Accumulated values in analysis services

Hello,

I'm making a report where I have a matrix showing the weeks of a month in the columns and certain data. The user selects a month and I use it as a parameter to show all the calculations from a cube. I already have all the data needed for the matrix, the problem is that I need an accumulated value in the last row that sums the first week's accumulated value and the second week's value and so on (the month can have 4 or 5 weeks). The first week's accumulated value is the data itself since there is no previous value.

week1

week2

week3

....

data

x1

x2

x3

...

accumulated value

x1

x1+x2

x1+x2+x3

...

I don't know if I can do this from the cube using a mesure or something(i think it would be easier to have all the calculations in the cube),or if it has to be done from the matrix in the report.

I would appreciate all help you can give me. Thanks in advance.

Assuming that you have a hierarchy with something like Year - Month - Week, you should be able to create a calculated "MonthToDate" figure using the PeriodsToDate function.

eg.

CREATE MEMBER Measures.MonthToDate AS SUM(PeriodsToDate([Time].[Year-Month-Week].[Month],[Time].[Year-Month-Week].CurrentMember),Measures.[data])

|||

Hello Darren!,

Thanks for your reply!, that is what I wanted to do :)

Accessing sub totals in a matrix

Hi,

How can I access the subtotal cells/values from each of the columns in Matrix and use them for calculations on other places in the report?

Thanks.

Any body?

How can I access the sub-totals of a Matrix as I need to use them in calculations in another place on the report? How can I programatically access the matrix report item's cell or text box values, particularly the sub totals?

Thanks,

|||Does any one know how the sub total field can be accessed in the matrix?|||

Right click on your Column Group Header to acces the Column Subtotal. Same with the Row Group header for the Row Subtotal. I don't think you can access the Subtotal value in other places on the report though.

-Aayush

|||Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column.

Accessing sub totals in a matrix

Hi,

How can I access the subtotal cells/values from each of the columns in Matrix and use them for calculations on other places in the report?

Thanks.

Any body?

How can I access the sub-totals of a Matrix as I need to use them in calculations in another place on the report? How can I programatically access the matrix report item's cell or text box values, particularly the sub totals?

Thanks,

|||Does any one know how the sub total field can be accessed in the matrix?|||

Right click on your Column Group Header to acces the Column Subtotal. Same with the Row Group header for the Row Subtotal. I don't think you can access the Subtotal value in other places on the report though.

-Aayush

|||Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column.