trying to pass a count based expression to a dataset parameter

Question

Hi we run 2014 enterprise. We'll be upgrading later.

i want to pass the string "<ALL>" to a dataset's proc parameter if the count of cities selected = count presented in the city dataset.

after having trouble i started building from simpler expressions to my desired expression. My trouble starts on approach 4. the error which i'll add to this post momemtarily says something about an aggregate over a datset being invalid.

=IIf(1=1,Parameters!pCity.Value,Parameters!pCity.Value)
=IIf(1=1,"<ALL>",Parameters!pCity.Value)
=IIf(1=Parameters!pCity.Count,"<ALL>",Parameters!pCity.Value)
=IIf(CountRows("Cities")=1,"<ALL>",Parameters!pCity.Value)
=IIf(CountRows("Cities")=Parameters!pCity.Count,"<ALL>",Parameters!pCity.Value)

Answer 1

Hi @db042190 ,
Could you describe the issue in more detail? Please illustrate with some screenshots if it is possible.
Best Regards,
Joy

Answer 2

sure, when i attempt to pass this to my dataset's proc (main proc for report generation) city param, i get the error shown below...i'm trying to condense the value of the city parameter being sent to my proc as "<ALL>" when user selects all from the report's city multi value parameter. the source of that parameter is a dataset called "Cities". I've anonymized the names to preserve our privacy.

=IIf(CountRows("Cities")=Parameters!pCity.Count,"<ALL>",Parameters!pCity.Value)

Answer 3

Hi @db042190
Do you mean that when the customer chooses "Select All" in the report, it displays "<All>", otherwise it displays the city name?

This seems to be a problem with your CountRows function. The CountRows function must specify the scope, the name of the dataset, data range, or group that contains the report items to be counted. "Cities" seems to be your field name. For more details about CountRows function, you can refer to this link: report-builder-functions-countrows-function.

I did a simple test where "DataSet2" is the query dataset for the parameter.

=IIf(CountRows("DataSet2")=Parameters!pCity.Count,"<ALL>",JOIN(Parameters!pCity.Value, “,”))  

Preview:


Best regards,
Aniya

Answer 4

thx aniya, i'm not sure you understand. I'm trying to pass that to a proc in a dataset. I've never had a problem with countrows in filling a textbox conditionally.

try passing something similar to what i posted to a param in your your proc for dataset 1 .

Answer 5

i can probably do that but first can you try passing the results of something like my expression to one of your proc's parameters for dataset1? And even see if you get the same error?