CODEWITHSUNDEEP
reporting-services
pran
pran

Reputation: 15

SSRS tablix group by

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.

Upvotes: 1

Views: 82

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I've recreated your issue using the following dataset:

enter image description here

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

enter image description here

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:

enter image description here

Let me know if this can help you.

Upvotes: 2

Related Questions