SSRS semi additive function for tablix cells

Question

I have to produce a report where in my row values should sum up on collapse (summary data) but column values should not sum up on collapse, rather show the last value. Here is a sample report

Fully expanded report

                       -2012  
                       Jan     Feb     Mar  
-India    Chennai      10       8       9  
          Bangalore    15       15      16

Rows collapsed

                       -2012  
                       Jan     Feb     Mar  
+India                 25       23      25

Columns collapsed:

                      +2012
+India                73

However, I don't need summarized data as sum for column collapse. The right data would be the last one available in the month data, which will be

Columns collapsed:

                     +2012
+India                25

I don't find any semi additive function available to be used in the report's tablix cells. Either i can use sum or last which applies to both rows and columns summary, which i don't want.

We don't have a problem with this in pivot table as we have defined the measure as a semi-additive measure on time dimension. The only problem is with the report designed using SSRS. Any idea on how this can be achieved?

Stacia Stacia · Accepted Answer · 2012-06-18T14:48:30

It sounds like you are using a cube as your data source? If that's the case, don't use the SUM function in SSRS for the expression in the textbox. Instead, use the AGGREGATE function. That tells SSRS to get the value from the cube. If it's defined correctly in the cube (as shown by your pivot table) then you should get that same value in your report.

SSRS semi additive function for tablix cells

1 Answers