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.

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] :
[Measures].[TotalDays] :
Count(Descendants([FiscalDate].CurrentMember,
[FiscalDate].[Day])),
Count(Descendants([CalendarDate].CurrentMember,
[CalendarDate].[Day])))

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

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 ***