SSRS - How to count values in a column that is generated by an expression?

Question

In my SSRS report, I have a column named permit_status. This column has a value generated by an expression (i.e. it comes from custom code). The permit_status value is calculated row-by-row.

The requirement is to show a sum of the different values displayed in the permit_status column, and to have this export to Excel. The values are "Approved" "Pending" etc. I just need one cell showing the total for each value.

I am able to create a footer for this report that uses Sum(...), for example:

=Sum(IIF(ReportItems!permit_status.Value = "Approved", 1, 0))

However, this only shows the sum of "Approved" values for that page of the report - not for the entire report. (And I'm missing something as it doesn't export to Excel.)

What's the best way to sum ReportItems!column_name.Value into a grand total?

Answer 1

In your matrix add column group based upon permit_status ( so that each column contains one available unique value) . right mouse click on left most field on your row and then select add total after, amend this field expression to count(Fields!permit_status.Value)

Answer 2

As I understand your question you want to count the amount of "Approved", "Pending", etc... values. There are different ways to achive this. The easyest way without changeing the structure of your tablix is a expression:

Either for one Cell:

="Approved: " & Sum(IIF(ReportItems!permit_status.Value = "Approved", 1, 0)) & " / " &
 "Pending: "  & Sum(IIF(ReportItems!permit_status.Value = "Pending", 1, 0))
 'And so on...

Or more cells:

'Cell 1
="Approved: " & Sum(IIF(ReportItems!permit_status.Value = "Approved", 1, 0))
'Cell 2
="Pending: "  & Sum(IIF(ReportItems!permit_status.Value = "Pending", 1, 0))

Answer 3

How about you add two Extra columns "Assigned" and "Pending" and then for those colum set Row rule as below respectively.

IIF(ReportItems!permit_status.Value = "Approved", 1, 0)
IIF(ReportItems!permit_status.Value = "Pending", 1, 0)

Then at end of data set just add Total as described here. IT will give you desired result