Complex universe design problem:dynamic dimensions makes drilldown wrong

[former_member207342]

Hi,

We have a requirement where in we need to display data based in Time interval selected by user.

e.g.

If User selects u201CMonthu201D then report should display data for last 30 days.

Interval			Revenue

01-Apr-2008		2000
02-Apr-2008		3000
---
--
--
--
30-Apr-2008		8000

Similarly if he as below

Day u2013 Last 24 hours		Hourly
Week u2013 Last 7 days		Daily
Month u2013 Last 30 days 	Daily
Quarter u2013 Last 90 days	Daily
Year u2013 Last 12 months	Monthly

To implement this I created a objects with following hierarchy.

Year->Quarter->Month->Week->Day->Hour

And later I created an object u201CIntervalu201D as and included it in time hierarchy as

Interval->Year->Quarter->Month->Week->Day->Hour

Select

CASE @Prompt('Select Interval',A,{'Day','Week','Month','Quarter','Year'},mono,constrained,)
WHEN 'Day' THEN to_char(@Select(Time Period\Hour))
WHEN 'Month' THEN @Select(Time Period\Day)
WHEN 'Week' THEN @Select(Time Period\Day)
WHEN 'Quarter' THEN @Select(Time Period\Day)
WHEN 'Year' THEN @Select(Time Period\Month)
END

Now I created a report I use u201CIntervalu201D Object along with u201CRevenueu201D

Everything works fine. But when I drill down report it shows u201CYearu201D as next drill down even when I have selected u201CMonthu201D as a Interval. (Last 30 Day data)

Ideally It should display u201CWeeku201D as next drill-down. As our sampling points for Week is last seven days i.e. Day.

Can some one please help out to understand what is happening.

Or is there any way to fulfill above requirement without loosing Drill-down feature

[Former Member · Accepted solution]

Hi Kuldeep,

You are moving to Year and your Hierarchy goes to the Year object. The hierarchy is not aware of the content of the Interval, only aware of its position in the hierarcy.

In order to implement this I believe you would need a series of interval objects so your hierarchy would look like

Interval->Interval Level 2->Interval Level 3....

The code for Interval would be

CASE @Prompt('Select Interval',A,{'Day','Week','Month','Quarter','Year'},mono,constrained,)
WHEN 'Day' THEN to_char(@Select(Time Period\Hour))
WHEN 'Month' THEN @Select(Time Period\Week)
WHEN 'Week' THEN @Select(Time Period\Day)
WHEN 'Quarter' THEN @Select(Time Period\Month)
WHEN 'Year' THEN @Select(Time Period\Quarter)
END

The code for Interval Level 2 would be

CASE @Prompt('Select Interval',A,{'Day','Week','Month','Quarter','Year'},mono,constrained,)
WHEN 'Month' THEN @Select(Time Period\Day)
WHEN 'Week' THEN @Select(Time Period\Hour)
WHEN 'Quarter' THEN @Select(Time Period\Week)
WHEN 'Year' THEN @Select(Time Period\Month)
END

Note that day has been left out of level 2 as it should return null.

The code for Interval Level 3 would be

CASE @Prompt('Select Interval',A,{'Day','Week','Month','Quarter','Year'},mono,constrained,)
WHEN 'Month' THEN @Select(Time Period\Hour)
WHEN 'Quarter' THEN @Select(Time Period\Day)
WHEN 'Year' THEN @Select(Time Period\Week)
END

Hope this helps

Alan