August 5, 2010 at 10:14 am
Good morning.
I've just discovered how cool SSRS is and am having a great time building reports.
Question I have is can I put an IF in my expression where it replaces select all with "All Stages" for multi value parameters instead of listing a huge line of labels?
I'd like it to list the individual labels like it does when people choose them but if they choose select all, just put "All Stages".
Something like:
IF Parameters!Stage.Label= all labels then "All Stages" else =Join(Parameters!Stage.Label,", ")
Is this possible? Thanks for reading.
Howard
August 5, 2010 at 11:44 am
I take it by the question you are displaying the parameters that were chosen on the report someplace? the header perhaps? At least in SSRS 2005 you can't access the fields of your datasets to do a count or what have you to compare against your data. To get around this, I create a dataset that returns the count for me. I set this to the default value of a hidden parameter and then compare the selected number of values from my parameter to that hidden parameter.
So for example I have a office drop down list for my report. To populate it I have the following dataset (named GetOffices) set to the Office Parameter
Select Name FROM Office;
then I have a second dataset named GetTotalOfficeCount as
Select Count(distinct Name) FROM Office;
I use that as the default value for the TotalOfficeCount Parameter
In my display expression I have the following (you might need to check the syntax as I had to change some things for the example)
="Selected Offices: " & IIF(Parameters!TotalOfficeCount.Value = Parameters!Office.Count, "All offices", Join(Parameters!Office.Label,", "))
It seems somewhat the long way round, but it works for me. If you find a different solution please share.
Thanks,
-Luke.
August 5, 2010 at 12:33 pm
Thanks Luke. I see where you are going with this. The report is in SSRS 2008 but I'll give it a try.
Yes, I have a header that lists all of the search criteria so that when the report is exported or printed, the viewer will know how the parameters were configured. When I was asked to include multiple value parameters, the list became very long so replacing the long list of values with "All values" when applicable will help.
Howard
August 5, 2010 at 12:54 pm
Perhaps in SSRS 2008 you may have access to be able to find a count of the first dataset so that you don't need the second one. I'm just not sure as we've not upgraded yet. In 2005 it throws an error so if no error you might be ok.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply