Hi @db042190 ,
Could you describe the issue in more detail? Please illustrate with some screenshots if it is possible.
Best Regards,
Joy
trying to pass a count based expression to a dataset parameter
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)
9 answers
Sort by: Most helpful
-
Joyzhao-MSFT 15,581 Reputation points
2022-11-03T06:31:19.677+00:00 -
db042190 1,516 Reputation points
2022-11-03T11:06:36.247+00:00 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)
-
AniyaTang-MSFT 12,446 Reputation points Microsoft Vendor
2022-11-07T05:55:22.167+00:00 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 -
db042190 1,516 Reputation points
2022-11-07T19:03:53.187+00:00 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 .
-
db042190 1,516 Reputation points
2022-11-08T21:56:26.447+00:00 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?