Calculate the count of rows in a table (SSRS grouping a table)

Question

I have a table in ssrs as follows. It is grouped by System and souce description as shown below. I need to calcluate the difference after each grouping as follows Calucated Diiference = Initial - filtered - Final = 0. How do you get the values of these from the table and display it after each grouping as shown below

System Source Description Total Rows

System1

                      Source                100     
                      Initial               100     
                      Filtered              10      
                       Final                90

Calucated Diiference = Initial - filtered - Final = 0

System2

                      Source                200     
                      Initial               200     
                      Filtered              30      
                       Final                170

Calucated Difference = Initial - filtered - Final = 0

score 0 · Accepted Answer · answered Nov 26 '18 at 21:37

Assuming the dataset columns are called myCaption (for the description part) and myValue for the numeric part then you can write an expression as follows.

=SUM( Fields!myValue.Value * 
    SWITCH(Fields!myCaption.Value = "Initial", 1,
            Fields!myCaption.Value = "Filtered" OR Fields!myCaption.Value = "Final", -1,
            True,0
            )
    )

This just takes the detail row myValue and multiplies it by either: 1 if the caption is "Initial"; -1 if the caption is either "Filtered" or "Final"; or 0 if it's something else. It then simply sums the results.

score 0 · Answer 2 · answered Nov 27 '18 at 05:55

Just add another row in your table (after final) and add the following expression into the textbox. Important here is that you add the scope to your calculations, so the system knows that you want to access the values at the grouping level:

=(Fields!Initial.Value, "SystemGroupingName") - (Fields!Filtered.Value, "SystemGroupingName") - (Fields!Final.Value, "SystemGroupingName")
=(100                                       ) - (10                                         ) - (90                                      )

If the field values are not numeric you need to put a CDbl() around each field.

Calculate the count of rows in a table (SSRS grouping a table)

System Source Description Total Rows

System1

System2

2 Answers2