Common relative date filters

Pentaho Schema Workbench

Version
10.0.x
Audience
anonymous
Part Number
MK-95PDIA009-05

In the Steel Wheels sample data cube provided by Pentaho for evaluation and testing, the Month level uses abbreviated three-letter month names. Furthermore, the Month level sits under the Quarter level. In Steel Wheels, the format string for an MDX member from the Month level would look like this:

[yyyy].['QTR'q].[MMM]
Some other common date formats:
  • [yyyy] (Year)
  • [yyyy].[q] (Quarter)
  • [yyyy].[q].[M] (Month)
  • [yyyy].[q].[M].[w] (Week)
  • [yyyy].[q].[M].[w].[yyyy-MM-dd] (Day)

The Day line, above, also specifies a format to represent the entire date. Without this format, a simple [d] parameter would be difficult to put into context. For more information on date format strings, refer to the SimpleDateFormat page on the ICU Project site.

To set up relative date filtering, for each level, you need to do the following:

  • In your Mondrian schema file, set the levelType XML attribute to TimeYears, TimeMonths, TimeQuarters, TimeWeeks or TimeDate.
  • Define the MDX date member format as an annotation with the name AnalyzerDateFormat.

Here is an example from the Pentaho sample data (Steel Wheels) Time dimension:

<Level name="Years" levelType="TimeYears" ... >
   <Annotations><Annotation name="AnalyzerDateFormat">[yyyy]</Annotation></Annotations>
</Level>
<Level name="Quarters" levelType="TimeQuarters" ... >
   <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].['QTR'q]</Annotation></Annotations>
</Level>
<Level name="Months" levelType="TimeMonths" ... >
   <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].['QTR'q].[MMM]</Annotation></Annotations>
</Level>