Replace parameter values with "All" in title heading 1

[dianemarie]

Hello, we have a report heading that shows the selections a user makes in a multi-value parameter separated by commas (in this case for profit centers). Example of formula for doing this:

=join(Parameters!ProfitCenter.Label,", ")

When the user selects "Select All", I would like it to say "All" instead of "Profit Center A, Profit Center B, Profit Center C" etc. My parameter is a String, Multi-Value, pulling available values from a dataset query, and pulling default values from the same dataset query. The main query says:


Where lo.profit_center_name in (@ProfitCenter)

Typically the user tends to select 1,2, maybe 3 profit centers, or all, and we have over 10 so when they pick all the report label is needless to say overly long. Thank you for any help.

 

[RiverGuy]

=iif(Parameters!ProfitCenter.Label.Length = Count(Fields!SomeField.Value, "The Name of Your DataSet containing the Lookup Values"), "ALL", JOIN(Parameters!ProfitCenter.Label))

 

[dianemarie]

Hi RG, I'm having trouble getting this to work. My dataset is called "ProfitCenter" and pulls from an order history table (there's no lookup table for Profit Center so I select distinct):

select distinct LO.nm_Profit_Center_Name
from nm_ordhistall LO
where LO.nm_profit_center_division in ('in-house','management')

My expression looks like below. It appears to be good when I save it but I keep getting an error when I click on Preview. The error is the generic "occurred during local processing, definition of the report is invalid". I tried both count and countdistinct in the expression:

=iif(Parameters!ProfitCenter.Label.Length = Countdistinct(fields!nm_profit_center_name.value,"ProfitCenter"),"All",
join(Parameters!ProfitCenter.Label,", "))

I create the expression called xProfitCenterLabel, drag it onto the group header where all my other report titles live as =Fields!ProfitCenterLabel.Value. Do you see anything obvious?

 

[RiverGuy]

Not really sure right off hand, but you can debug it in steps to figure out where the error is happening. For example, try the following:

=iif(Parameters!ProfitCenter.Label.Length = 3, "All",
join(Parameters!ProfitCenter.Label,", "))

Instead of trying to count the rows in your parameter's dataset, try the literal "3." If it works fine afterwards, that means there's an issue getting that row count, which will help to narrow down the issue.

 

[dianemarie]

Good plan. I'll let you know... Thanks RG!

 

[dianemarie]

Hi RG, both your solutions worked, but the first is preferable because the number of our profit centers might change, so we needed it to dynamically compare the current number at report runtime. However, for some reason we can't figure out, it wasn't working because I was creating an expression (a calculated field) then dragging the field onto a table header. Once I deleted the field and put it directly into the table header cell, it worked. My senior report developer said she notices this quite often in SSRS (2005). We agree there must be some logic to it but we don't know what it is. On this same report, I have created other calculated fields(pulling parameter information) that work fine when dragged into the same table header. In any case, thanks for your help with getting the dynamic count by comparing to the dataset. That was the piece we were looking for.