CanShrink Question

Hello all,

I have a report with a dataset similar to this:

description0 35
description1 0
description2 0
description3 0
description4 0
description5 45
description6 0
description7 0
description8 50
description9 10
.....

I don't care to see the records with a zero numeric value so I've configured the two text boxes the list box to set their values to "nothing" if the numeric value is 0. I've also configured both text boxes canshrink property to true.

When the report renders, I get this:

desc. header numeric header
--
description0 35

description5 45

description8 50
description9 10
....

I get one blank line where the grouping of 0 valued numeric records are. That tells me the canshrink is working, somewhat. What I'm confused about is why the blank line and what I need to stuff into the text box value.

In this particular case, I can go back to the db stored proc and just omit the 0 valued records. But, that doesn't help me if I run into a situation where I don't have that option.

Any help would be appreciated,

Thanks in advance,

Greg Wilkerson

Well, I need to add something. I deployed this report and the can shrink does not work in the deployed version. Through BIDS, it works as described above.

Greg

|||Hi Greg.

I didn't fully understand the question but from what I understod - may be instead of changing the "canshrink" property you should try and change the "visibility" property if the value is zero (Also, you can try and change the width or height of the cells).

Good luck,
Roy.|||

Roy,

You understood things fine. The visibility settings did work out fine, and is a pretty clean implementation. I thought I had tried that in an earlier attempt but I guess didn't get something right. I'm going to have to figure out the intracacies of this canshrink property. And, this rendering differently in BIDS vs. the deployed report is troublesome.

But, I'll figure it out.

Thanks.

Cannot use global variables in Table Footer

Hi,
I tried to use the global variables "totalpages" in my table footer to decide whether to hide or show if there are no records from the dataset.
But then I found out that global variables can only be used in page header/footer.
Is there any way i could check whether there's records returned, and hence control the visibility.
Thanks.Good Evening!
Right or wrong - but never mind me using stored procs for everything!

For ALL MS RS reports we use stored procedures - for everything...
So in every stored proc we count records in a lot of various ways - but primarily we use in the first line
Select
Count(1) as Expr1 - Expr1 is our indicator that data has been populated within the SQL Select for all records processed (may have some records or not)
You should never use Count(*) anyway because it does another "scan" of the DB to get the result...
So within MS RS the first thing we do is inspect EXPR1 for a value
No Value
Display "Selection Parameters Found No Information For Your Request" in a text box that was added to the Header of the Report
Else
Display the data within MS RS.
The header text box has an IIF condition and we BOLD in RED so it is visible for the users who do not like to read what they are getting
I don't know how to do if the SQL Select was a "TEXT" string versus a stored procedure! Probaly just as easy - I guess....
We have also in a lot of instances where we use a SQL Stored Proc driver which means and it works really nice if you have SELECTS <= 8000 characters...
We already know the fields to populate the MS RS REport so we just take the parameters passed - look up in a table the SQL for the particulare MS RS report and insert the parameters passed by MS RS so we always get a return value(s) from the SQL Stored Proc driver that performs and EXEC for the SQL select we pull from the table and then declare and insert in the SQL script the values the user passed from MS RS.
So the return value from the EXEC is passed back to use and we cast as EXPR1.
I got to "woordy" here - but nevertheless - I would hope that not only for no data from a Select is captured but also other error situations that might occur as well - so you can pass this information back as well especially if your environment is OLTP versus a warehouse...
Best regards



|||

eeyore21,
CountRows(Scope) where Scope is your dataset. ex: Place=IIF(CountRows("MyDataset") = 0, True, False) as Visibility->Hidden->Expession for your object that you want to hide/show based on record count.

Hope this helps,
Mike

|||

Thanks,
It sure helps alot.

cannot see the 2nd table(2 tables returned from stored procedure) in dataset in report designer

Hi,

I am using stored procedure(SP) to create the dataset in report designer. My SP is returning 2 tables/recordset and i am only able to see first table/recordset in the dataset in report designer. I have to place fields from second table onto my report. Is there anyway i could get the 2nd table in my dataset so that i could place the fields in my report.

Please help

This is by design. We'll only look at the first resultset.

You create a second sproc to return the second resultset and build a second dataset to consume it.

|||

Hi

I am also facing the same problem. Could you help how to write a stored procedure to retrieve the second table from stored procedure. So that I will create second dataset as you said.

Thanks & Regards

Kumar

cannot see the 2nd table(2 tables returned from stored procedure) in dataset in report designer

Hi,

I am using stored procedure(SP) to create the dataset in report designer. My SP is returning 2 tables/recordset and i am only able to see first table/recordset in the dataset in report designer. I have to place fields from second table onto my report. Is there anyway i could get the 2nd table in my dataset so that i could place the fields in my report.

Please help

This is by design. We'll only look at the first resultset.

You create a second sproc to return the second resultset and build a second dataset to consume it.

|||

Hi

I am also facing the same problem. Could you help how to write a stored procedure to retrieve the second table from stored procedure. So that I will create second dataset as you said.

Thanks & Regards

Kumar