trying to pass a count based expression to a dataset parameter

db042190 1,516 Reputation points
2022-11-02T20:08:57.327+00:00

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)

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,854 questions
{count} votes

9 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,571 Reputation points
    2022-11-03T06:31:19.677+00:00

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

    0 comments No comments

  2. 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)

    256710-erroronpassingalltoproc.png

    0 comments No comments

  3. AniyaTang-MSFT 12,341 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, “,”))  
    

    257714-1.png
    Preview:
    257715-2.png
    257771-3.png
    Best regards,
    Aniya

    0 comments No comments

  4. 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 .


  5. 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?