Dynamic Time set up in InMemory

The Time dimension, as it is now, can be used to filter data by specific years, specific months, specific dates etc.

However, by adding Dynamic Criteria options to the Time dimension, you can dramatically improve its usefulness. With Dynamic Criteria you can start making analyses with dynamic filters such as Previous month, Year to date, Running 12 months etc.

You can add the Dynamic Criteria options to your Time hierarchies. The hierarchy and its levels must be of the correct Time types. The hierarchy itself must be of the type Time, and its levels must of types TimeYears, TimeQuarters, TimeMonths, TimeDays etc.

Furthermore, in the hierarchy property Time definition you must enter a special syntax that enables the TARGIT server to interpret and work with the hierarchy in a dynamic way.

Generally, the syntax includes a set of square brackets and ‘less than’ and ‘greater than’ signs for each level of the hierarchy. So, for a three-level hierarchy, the basic syntax is this:

• [<>].[<>].[<>]

In each set of brackets you enter the syntax that describes the time information on the corresponding level.

Examples on time information syntaxes:

• [<YYYY>] – Four digit year, e.g. ‘2012’.
• [<Q>] – Quarter number, e.g. ‘1’, ‘2’, ‘3’ and ‘4’.
• [Quarter <Q>] – Quarter number prefixed by string, e.g. ‘Quarter 1’.
• [<M>] – One or two digit month number, e.g. ‘1’, ‘2’, ‘3’ … ‘10’, ‘11’, ‘12’.
• [<MM>] – Two digit month number, e.g. ‘01’, ‘02’, ‘03’ … ‘10’, ‘11’, ‘12’.
• [<W>] – One or two digit week number, e.g. ‘1’, ‘2’, ‘3’ … ‘51’, ‘52’, ‘53’.
• [<DD>] – Two digit day of month number, e.g. ‘01’, ‘02’, ‘03’ … ‘30’, ‘31’.
• [<YYYY-MM-DD>] – Full date with dashes, e.g. ‘2012-05-26’.
• [<Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec>] – Looking up short month names in pre-defined list.

In all of the above examples, it will expect to find the values in the Namecolumn property of the hierarchy levels.

However, as we often know from the Month level, it works with month numbers in the Keycolumn property, while it displays month names via the Namecolumn property. In that case, it would make sense to direct the syntax to the Keycolumn instead:

• &[<M>] – Looking up the month number from the Keycolumn

Consider a hierarchy with levels, Namecolumns and Keycolumns like this for May 26^th 2012:

The Time definition syntax for such a hierarchy could be this:

• [<YYYY>].&[<M>].&[<YYYYMMDD>]

The Time definition syntax for the YMD hierarchy in the Data Model Editor:

Once the Data Model has been saved with the dynamic time definitions, you can start utilizing the new options in the end-user client.

E.g., with the YMD hierarchy in the Criteria bar, you can right click it to enable the dynamic options:

The drop down list should then display a number of pre-defined dynamic options according to the levels and types of the hierarchy:

Notice that, in these demo data, you may need to work with the ‘Dynamic date origin’ selector (the calendar in screenshot above) to simulate a different ‘today’.