|
Home > Archive > MS SQL Server OLAP > January 2006 > retrieve last month value of each year
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
retrieve last month value of each year
|
|
| Stanley 2006-01-26, 4:58 pm |
| Hi,
i have the following problem. I have an OLAP with3 dimensions. I measure
receives data every month. The values are ytd. So the value in march would be
the sum of jan,feb and march, etc... But the OLAP sums the values of all
months in 2005 when i select 2005 in the time slice while it should show the
value of december 205 because december would already the year value.
I tried to achieve this with a calculated cell, but got stuck on the time
selection. This is my configuration set with the calc cell wizard:
First screen: i selected the time slice. As member set i selected the year
level because it should only act when a year is selected.
Second screen: I added the crieteria that is should act for specific
Measures, like [Measure].currentmember is [Pi].[PiName]
Third screen:this the problem. i selected the month level, but the wizard
shows me [Time].[Year].&[2005].&[4].&[12]
4 = quarter 4
12 = december
2005 = year 2005
Now this is hard coded, but i want the year to be variable. it should be the
year depending on which year i have selected in the time slice. Is there a
way to make this year value variable?
Thnx,
Stanley
| |
| Marius Dumitru 2006-01-28, 8:23 pm |
| With Analysis Services 2005, this can be easily solved by changing the
aggregation function of the measure to "LastChild" instead of "Sum".
--
Hope this helps
Marius
"Stanley" <Stanley@discussions.microsoft.com> wrote in message
news:1C9EFBDE-6339-43F8-9577- DA96DF068042@microso
ft.com...
> Hi,
>
> i have the following problem. I have an OLAP with3 dimensions. I measure
> receives data every month. The values are ytd. So the value in march would
> be
> the sum of jan,feb and march, etc... But the OLAP sums the values of all
> months in 2005 when i select 2005 in the time slice while it should show
> the
> value of december 205 because december would already the year value.
> I tried to achieve this with a calculated cell, but got stuck on the time
> selection. This is my configuration set with the calc cell wizard:
> First screen: i selected the time slice. As member set i selected the year
> level because it should only act when a year is selected.
> Second screen: I added the crieteria that is should act for specific
> Measures, like [Measure].currentmember is [Pi].[PiName]
> Third screen:this the problem. i selected the month level, but the wizard
> shows me [Time].[Year].&[2005].&[4].&[12]
> 4 = quarter 4
> 12 = december
> 2005 = year 2005
>
> Now this is hard coded, but i want the year to be variable. it should be
> the
> year depending on which year i have selected in the time slice. Is there a
> way to make this year value variable?
> Thnx,
>
> Stanley
| |
| Stanley 2006-01-30, 7:24 am |
| Marius, thnx for your reply, but i am using AS 2000. Is it possible in this
version also?
"Marius Dumitru" wrote:
> With Analysis Services 2005, this can be easily solved by changing the
> aggregation function of the measure to "LastChild" instead of "Sum".
>
> --
> Hope this helps
>
> Marius
>
>
> "Stanley" <Stanley@discussions.microsoft.com> wrote in message
> news:1C9EFBDE-6339-43F8-9577- DA96DF068042@microso
ft.com...
>
>
>
|
|
|
|
|