Search code examples
dynamicservicereportingrdl

Add Totals Column at the end of table with dynamic number of columns

I am using Reporting Services 2008. I have an RDL as follows ![I am asking it to print for 12 months so I get the following]

jan feb mar apr may jun jul aug sep oct nov dec Totals
1---1----1---1-----1----1---1---1---1----1---1---1----12

Now when I ask it to print for 4 months I get the following ![For 4 months]
jan feb mar apr Totals
1---1----1---1-----12

I want the Totals column at the end to sum only the months that are printed. Currently It is Summing up all the months as i have put the following code behind the Totals Expression.

=Sum(Fields!retail1.Value + Fields!retail2.Value + Fields!retail3.Value + Fields!retail4.Value + Fields!retail5.Value + Fields!retail6.Value + Fields!retail7.Value + Fields!retail8.Value + Fields!retail9.Value + Fields!retail10.Value + Fields!retail11.Value + Fields!retail12.Value)

The expression behind each column to hide or show it is as follows:

=iif(Parameters!StartMonth.Value <= 5 and Parameters!EndMonth.Value >= 5,False,true)

How can display the total of only those months that are selected. And from the RDL itself, WITHOUT CHANGING THE QUERY.

Thanks in advance.

Solution

  • Your total expression should look like this:

    =Sum(IIF(Parameters!StartMonth <= 1 And Parameters!EndMonth >= 1, Fields!retail1.Value, 0)) 
+ Sum(IIF(Parameters!StartMonth <= 2 And Parameters!EndMonth >= 2, Fields!retail2.Value, 0))
+ Sum(IIF(Parameters!StartMonth <= 3 And Parameters!EndMonth >= 3, Fields!retail3.Value, 0)) 
+ ... and so on