|
Home > Archive > MS SQL Server OLAP > April 2005 > Multiple Time Hierarchies Problem
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 |
Multiple Time Hierarchies Problem
|
|
| Akber 2005-04-26, 11:25 am |
| Hi,
I have developed two hierarchies for the time dimension. One for Calender
Year and the other for the fiscal year.
The problem is that in my cube I have created some calculated measures that
computes the projected sales in the selected time member level.
e.g. If the user is at month level then it calculates the
-days passed in that month
- sales in that month
- totaldays in that month
and then it displays the projected sales of that month.
Same is the case for Quarter and Year level.
The Question is that do I have to create sepratate set of calculated members
for each hierarchy.
--
Thanks
Akber.
| |
| Deepak Puri 2005-04-26, 8:24 pm |
| If your users will only select a specific member from one of the 2 time
hierarchies, leaving the other hierarchy selection at the "All" level,
then you can check the level of the current member in each hierarchy.
Something like:
[Measures].[FiscalSelected] :[color=darkred]
([CalendarDate].CurrentMember.Level is [CalendarDate]. [All])[color=darkred
]
[Measures].[TotalDays] :[color=darkred]
iif([Measures].[FiscalSelected],
Count(Descendants([F
iscalDate].CurrentMember,
[FiscalDate].[Day])),
Count(Descendants([C
alendarDate].CurrentMember,
[CalendarDate]. [Day])))[color=darkr
ed]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***
| |
|
| Thanks Deepak for the reply.
- My users can select any level of any of the two time dimension hierarchies
at a time. In that case what would be the solution because ur solution
assumes that the user is analyzing sales using only one hierarchy at a time.
--
Thanks
Akber.
"Deepak Puri" wrote:
> If your users will only select a specific member from one of the 2 time
> hierarchies, leaving the other hierarchy selection at the "All" level,
> then you can check the level of the current member in each hierarchy.
> Something like:
>
> [Measures].[FiscalSelected] :
> ([CalendarDate].CurrentMember.Level is [CalendarDate].[All])
>
> [Measures].[TotalDays] :
> iif([Measures].[FiscalSelected],
> Count(Descendants([F
iscalDate].CurrentMember,
> [FiscalDate].[Day])),
> Count(Descendants([C
alendarDate].CurrentMember,
> [CalendarDate].[Day])))
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
| |
| Deepak Puri 2005-04-27, 11:23 am |
| Hi Akber,
Since the 2 hierarchies aren't orthogonal, you have to define what
results should be returned when there are simultaneous selections on the
2 hierarchies. For example,
if Calendar Month and Fiscal Quarter are selected, then are projected
sales based on the month or the quarter? The 2 selections may even be
mutually exclusive. Once these kind of issues are resolved, then the MDX
can be considered.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***
|
|
|
|
|