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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not really following your example, but the RunningValue function can be used in an expression to generate a running total of another column.


Open in new window

ValentinoVBI ConsultantCommented:
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.
SURESH0518Author Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ValentinoVBI ConsultantCommented:
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.
SURESH0518Author Commented:
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.
ValentinoVBI ConsultantCommented:
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)
ValentinoVBI ConsultantCommented:
I did some testing and now I think I understand what you mean.  With RunningValue you can specify a scope but in your case you need the aggregation go over the Currency child group.  I don't think you'll be able to achieve your goal using this method...

Would it be an option to calculate the aggregations in the query itself?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.