asked on

Running Total in SSRS Matrix Column Groups

Currently            I have Period 0 and 1 and each period has 2 currencies called GBP and USD and I need Running Total for each period within a particular currency as we progress each period

Currently what we have

NAME      0             1
---------------------------------------------------------
       GBP USD        GBP      USD
---------------------------------------------------------
cash       100        200        200      300

Results should be like this as below

NAME      0             1
----------------------------------------------------
       GBP       USD        GBP      USD
-----------------------------------------------------
Cash      100       200        300      500

For period 0 GBP=100 and for period=200 the cumulative will be 100+200 should be diplayed on period 1. Any help is appreciated

Jim Horn

Not really following your example, but the RunningValue function can be used in an expression to generate a running total of another column.

=RunningValue(Fields!GBP.Value,Sum,"ds_your_data_set")

Open in new window

ValentinoV

You should be able to use the RunningValue function as Jim mentioned but not as shown in the expression. The first parameter of that function should be the name of the numeric field, possibly Fields!Cash.Value. The aggregation should automatically be what you expect if you've set your groupings right. For your matrix to show as you've described you should probably have have two Column Groups: first one on Period, the next one on Currency. And no Row Groups.

SURESH0518

ASKER

I created two column groups called period and currency and I need running total for each curreny as period increases

Initially the report is like this

Period 0 Period 1 Period 2
USD GBP USD GBP USD GBP
100 300 500 600 1000 1200

Now I want to runningvalue for each currency for as period increases. I want to show result like this.

Period 0 Period 1 Period 2
USD GBP USD GBP USD GBP
100 300 600 900 1600 2100

Let me how this possible in SSRS

ValentinoV

Well, yes it is. Did you try the RunningValue as mentioned? You can just add an additional column inside your matrix groups (easier to check if the sum is correct because you'll have the original data next to it) and enter the following:

=RunningValue(Fields!Cash.Value, Sum, Nothing)

Open in new window

This assumes your numeric field is called Cash. If it's not you'll have to change the expression accordingly.

SURESH0518

ASKER

I used RunningValue(Fields!Cash.Value, Sum, Nothing) as you said but the problem some periods have 5 currencies and some periods have 7 currencies and how can add so many columns. If currencies are fixed then I can add another column as you said.

ValentinoV

The groupings should take care of that automatically. Can you upload your report so that I can have a closer look at the design? (Make sure not to include any sensitive data)

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial