SSRS get previous total

0
votes

I am working on SSRS 2008. A simple tablix with one row group (product) and one column group (month), at the intersection of row and column is the data (number of sales). Now I add a total row, which equals to sum(number of sales) for each month. Also I add a total column, which equals to sum(number of sales) for each product. At the intersection of the total row/column, the value is the grand total sum of number of sales for all products and all months. Now I want to change this to total of all products but for all months except the last month. E.g. there are product 1, 2, 3 and month 1, 2, 3. I want total of sales for product 1,2, 3 and month 1,2 in that cell. How to do that? Something like this pic, in the lower right corner, there should be 6 (for Feb and Jan).

enter image description here

----------update, added script to create this report, so maybe someone can try it.

The dataset:

    select 'Jan'  as mon, 'p1' as prod, 1 as id
    union all
    select 'Feb'  as mon, 'p1' as prod, 1
    union all
    select 'Mar'  as mon, 'p1' as prod, 1

    union all
    select 'Jan'  as mon, 'p2' as prod, 1
    union all
    select 'Feb'  as mon, 'p2' as prod, 1
    union all
    select 'Mar'  as mon, 'p2' as prod, 1

    union all
    select 'Jan'  as mon, 'p3' as prod, 1
    union all
    select 'Feb'  as mon, 'p3' as prod, 1
    union all
    select 'Mar'  as mon, 'p3' as prod, 1

The report definition:

enter image description here

There is one row group (group on prod), one column group (group on mon). Actually, I can do that in T-SQL by calculating total without last month then join this back to the main dataset. But just wondering is it achievable by SSRS alone.

3
reporting-services
add the appropriate reportItem values together. – Tab Alleman
I can't think of how exactly. Can you give more detail? – thotwielder
"7: Avoid calculated fields at the SSRS report level If there are calculated fields in the report then it is better to create them at the query level and not at the report level, because the database can better handle the calculations. This approach will also promote less report processing load on the report server and ease of maintenance." mssqltips.com/sqlservertip/3659/… – HEDMON

3 Answers

0
votes

Use an IIF in your expression to exclude the ones with the last (MAX) month from your dataset.

=SUM(IIF(Month(Fields!DateField.Value) & "|" & YEAR(Fields!DateField.Value) = 
Month(MAX(Fields!DateField.Value, "Yourdataset")) & "|" & YEAR(MAX(Fields!DateField.Value, "Yourdataset")), 0, Fields!ValueField.Value))

If the month equals the max month, it will be 0 otherwise it will use your ValueField.

0
votes

Reference the textboxes you want to sum by name, as members of the reportItems collection.

In your example, for instance, if the bottom row of your tablix (the totals row) had textboxes named "tbTotal1", "tbTotal2", "tbTotal3" for the month columns, then you would set an expression like this in the last textbox to sum just the first two month-totals:

=ReportItems!tbTotal1.Value + ReportItems!tbTotal2.Value
0
votes

Try with this:

RunningValue(Fields!totalField.Value,Sum,"DataSourceName")

You can modify the aggregator (by using for example Count instead of Sum) and, if you wanted to reset the value within each iteration, you can change the DataSourceName by the word Nothing