SSRS tablix group by

Question

I have a SSRS report to display as below:

Agency          Filing Type        Status - Report headings

Agriculture and Markets      

             Internal (5)    
                                   Pending (4)
                                   Closed (1)
                                   Substantiated (1)
          Inspector General (1)  
                                   Pending (1)
                                   Closed (0)
                                   Substantiated (0)
BPCA         

              Internal (3)   
                                   Pending (3)
                                   Closed (0)
                                   Substantiated (0)
                DHR (1)  
                                   Pending (0)
                                   Closed (1)
                                   Substantiated (0)

I was able to do the group by agency, filing type and status. Only problem is I am not able to repeat this status column. I mean status with pending,closed,substantiated need to be displayed to all the filing types.

my dataset has agency_name, filing_type_name, event_type and count — pran
Select a.agency_name,f.filing_type_name,ag.event_type, Count(case when c.origination_date >= '2015-01-01 00:00:00.000' then ca.event_id end) as FilingCount FROM wdu_case c inner join wdu_event ca ON c.case_id = ca.case_id and ca.event_type_id in (14,19) INNER JOIN event_type ag on ca.event_type_id = ag.event_type_id INNER JOIN Agency a on c.agency_id = a.agency_id inner join filing_type f on c.filing_type_id = f.filing_type_id Group By a.agency_name,f.filing_type_name, ag.event_type; — pran
Does it return 0 for FillingCount where there isn't rows with an event_type?. In example for BPCA/Internal/Closed FilingCount will be 0? — alejandro zuleta
That is where i need help. i am not able to get a 0 where there isn't rows for an event type because that event type is not in the list. i need help at this point. — pran

alejandro zuleta alejandro zuleta · Accepted Answer · 2015-11-24T20:16:09

I've recreated your issue using the following dataset:

I added a tablix and set the Row Groups you can see in the screenshot.

Note I've added three rows inside the FilingType group and hardcoded the Pending, Closed and Substantiated words.

Add Placeholders beside each hardcoded to calculate the count.

Use these expressions:

(1) For Pending

=Sum(
IIF(Fields!Status.Value="Pending",Fields!FilingCount.Value,0)
)

(2) For Closed

=Sum(
IIF(Fields!Status.Value="Closed",Fields!FilingCount.Value,0)
)

(3) For Substantiated

=Sum(
IIF(Fields!Status.Value="Substantiated",Fields!FilingCount.Value,0)
)

For the Filing Type total use this expression beside the [FilingType].

=Sum(Fields!FilingCount.Value,"FilingType")

It will preview the following tablix:

Let me know if this can help you.

SSRS tablix group by

1 Answers