Search code examples
reporting-servicesssrs-2008ssrs-2012ssrs-2016

SSRS Multi Value Parameter Count Expression

I am trying to count the number of records through a count expression in a text box based on the multi value parameters that are chosen.

My table:

WSVLWOs

WO_NUMBER    WO_ACTN_TY
18-003759   Adjust to Grade
18-005909   Repair / Replace Box
18-002559   Repair / Replace Box
18-003229   Inspection
18-003224   Repair / Replace Box

My count expression:

=Count(Fields!WO_NUMBER.Value, "WSVLWOs")

My Available Parameters

Adjust to Grade
Repair / Replace Box
Inspection

The filter on the table:

enter image description here

Now when I select multiple parameters, it does not seem to give me the sum of the count of the parameter values, only one of the parameter value. Is the issue in my count expression?

Solution

  • Your filter expression is

    =Parameters!Tasks.Value(0) which is only the first selected parameter value.

    There are a few ways to fix this issue.

    You should be able to change your expression to =JOIN(Parameters!Tasks.Value, ",") although this might not work depending on datatypes etc. All this does is pass a comma separated list to the filter.

    However I would question if this is the best approach. If you can, I would filter in the dataset query as this means only the required data is returned and it makes you count much simpler.

    If you need the individual records but also need the count then return all required rows with a dataset query that looks something like.

    SELECT * FROM WSVLWOs WHERE WO_ACTN_TY IN(@Tasks)

    @Tasks will be passed into the query, parsed correctly and you will only get the records you need. Your COUNT() expression with then work fine.

    If you only needed the count then then you could just do this in your dataset query and return just the count

    SELECT COUNT(*) as myCount FROM WSVLWOs WHERE WO_ACTN_TY IN(@Tasks)

    Then you could just reference the myCount fields from the dataset to get your answer.