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 :)

Accumulated value in table detailrow

I have a dataset with a GROUP BY connected to my table.
In the detailrow I want to add a accumulated value calculated from another
field.
Like this:
MonthX ValueY Acc. value
--
Month 1 10 10
Month 2 20 30
Month 3 30 60
Month 4 40 100
Can I do it by referencing the textboxes in the table, or can i change my
transact-SQL in any way?
(SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable GROUP
BY MonthX ORDER BY MonthX)
Please help!Take a look at the reporting services RunningValue function.
"Olof" <Olof@.discussions.microsoft.com> wrote in message
news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
>I have a dataset with a GROUP BY connected to my table.
> In the detailrow I want to add a accumulated value calculated from another
> field.
> Like this:
> MonthX ValueY Acc. value
> --
> Month 1 10 10
> Month 2 20 30
> Month 3 30 60
> Month 4 40 100
> Can I do it by referencing the textboxes in the table, or can i change my
> transact-SQL in any way?
> (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> GROUP
> BY MonthX ORDER BY MonthX)
> Please help!|||Thank you very much Stephen, that function was exactly what I needed!!
Greetings
Olof
"Stephen Farmer" wrote:
> Take a look at the reporting services RunningValue function.
>
> "Olof" <Olof@.discussions.microsoft.com> wrote in message
> news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
> >I have a dataset with a GROUP BY connected to my table.
> > In the detailrow I want to add a accumulated value calculated from another
> > field.
> > Like this:
> > MonthX ValueY Acc. value
> > --
> > Month 1 10 10
> > Month 2 20 30
> > Month 3 30 60
> > Month 4 40 100
> >
> > Can I do it by referencing the textboxes in the table, or can i change my
> > transact-SQL in any way?
> > (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> > GROUP
> > BY MonthX ORDER BY MonthX)
> >
> > Please help!
>
>