dynamic subset with Start and End date

[kangkc]

Hi,
We have a dimension that's comprise of dates console to month, year.
Eg:

2009
2009-Q1
2009-Jan
1-Jan-2009
2-Jan-2009
..
31-Jan-2009
2009-Feb
...

For analysis, user requested to have selection of Start and End Date in Excel reports, in OLAP analysis, this is typically not possible unless user pre-select all the elements covering the start/end dates. We are exploring using dynamic subset that's generated via TI through action Button before the Excel report is refreshed. Seems like MDX may be the solution but we are not sure how to go about doing that. Anyone MDX guru out there can assist?

Is there any better suggestion to handle the date range selection?

[Wim Gielis]

Hi

Use a colon to indicate the Begin and End date:

In the Prolog tab of a TI process:

Code: Select all

SUBSETDESTROY('MyDimension','MyDynamicSubset');
SUBSETCREATEBYMDX('MyDynamicSubset','{[MyDimension].[1-Jan-2009]:[MyDimension].[31-Dec-2009]}');

Wim

[kangkc]

Well, managed to get the whole action button - TI - dynamic subset working,
but except for my original thought on using subset in DBRW. Once the subset is destroyed and rebuilt, the DBRW referring to the subset will fail with key error.
Anyway, using subset in DBRW is never officially supported hence can't blame anyone.

The last thing I want to do is to create dynamic (driven by TI) consolidated element for selected date range in dimension hierarchy which I know it will work.

Any other bright idea(s) on how typically you handle calendar date range selection.

[dfrench77]

Use a colon to indicate the Begin and End date:

In the Prolog tab of a TI process:

CODE: SELECT ALL
SUBSETDESTROY('MyDimension','MyDynamicSubset');
SUBSETCREATEBYMDX('MyDynamicSubset','{[MyDimension].[1-Jan-2009]:[MyDimension].[31-Dec-2009]}');

I would like to use this code to create a dynamic that is based on the users input in an TM1 Active form. For example, the user would enter or select the begin date and end date. How would I get the user selection of begin date and end date populated where 1-Jan-2009 and 31-Dec-2009 are hard coded in the code?

I was trying to use the TI Advanced - Parameters tab to populate the begin and end date. Do you have any suggestions on how to do this?

[lotsaram]

I would like to use this code to create a dynamic that is based on the users input in an TM1 Active form. For example, the user would enter or select the begin date and end date. How would I get the user selection of begin date and end date populated where 1-Jan-2009 and 31-Dec-2009 are hard coded in the code?

If you are using an active form then you shouldn't be referencing a dynamic subset in the first place. It is much better to use the MDX argument of the TM1RptRow function to generate the list of elements dynamically. This has several advantages
- more flexibility and ease of use for the end user
- no need to create lots of subsets in advance
- no need to run any code to maintain subsets (either dynamic or static) on the server
- no locking or performance issues from reevaluating dynamic subsets

To do this is easy as you can "parse" together your MDX string in an Excel formula, that's all there is to it (really!). All you need is for your report row formula to look something like
(note your regional Excel settings will more than likelly have a comma not semi-colon)
=TM1RPTROW($B$9;"server:DimDate";;;;;"'{[DimDate].["&StartDate&"]:[DimDate].["&EndDate&"]}")
Where you have 2 named ranges in your sheet called "StartDate" and "EndDate". These could be entered with a SubNm or a validation drop-down or a pick-list or whatever.

Enjoy.

[dfrench77]

That's great I love easy! In TM1 it is easy to over complicate solutions. Thank you, I will use your suggestion.