SSRS CountRows of a specific Field containing a specific Value

Question

I am building an SSRS report. I have a DataSet that contains several Fields, one of which is Doc_Type. Doc_Type can contain several values, one of which is 'Shipment'.

In my report, I want to count the number of Doc_Types that are equal to 'Shipment'. This is what I am using, and it is not working:

=CountRows(Fields!Doc_Type.Value = "Shipments")

The error I get is: "The Value expression for the textrun 'Doc_Type.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

Answer 1

You need to use an IIF to evaluate your fields and SUM the results. If your expression is in a table, you could use this:

=SUM(IIF(Fields!Doc_Type.Value = "Shipments", 1, 0))

Answer 2

There are many ways to achieve this.

Method 1

You can set up your expression something like this

=SUM(IIf(Fields!Doc_Type.Value = "Shipments", 1, 0), "YourDataSetName")

Remember SSRS is case sensitive so put your dataset name and Field names correctly.

Method 2

I prefer handling it in SQL as I want to keep the business logic out of RDLs.

You can use window functions to get the shipment count.

Also notice in the case of count I haven't added ELSE 0 condition. Because that will give wrong results. Count doesn't care what is the value inside it. It just counts all Non Null values. ELSE NULL will work.

SELECT Column1, Column2, Column3,
       SUM(CASE WHEN Doc_Type = 'Shipments' THEN 1 ELSE 0 END) OVER() ShipmentCount_UsingSum
       COUNT(CASE WHEN Doc_Type = 'Shipments' THEN 1 END) OVER() ShipmentCount_UsingCount
FROM myTable
JOIN....
WHERE .....

Now you can use this field in the report.