AnsweredAssumed Answered

How to create Analysis data source with time dimension from Oracle Date column allowing date range filter

Question asked by Oliver Doepner on Jan 23, 2019

Using Pentaho BI Server 6.1.0.9.307, I am trying to create an Analysis data source from a mondrian.xml file with a LABOR_DATE TimeDimension based on a single DATE column in Oracle database. The physical column is called ENTERED_ON.

 

I then create an Analysis Report for that source, where I also need a date range filter on the time dimension.

 

I tried various things and currently trying the below code, but when I add a filter on the column, the Pentaho Analysis Report UI only offers the typical String conditions (includes, contains, etc):

 

  <Dimension name="LABOR_DATE" type="TimeDimension">

    <Hierarchy hasAll="true">

    <Table name="IMP_MAN_HOURS$VIEW"/>

      <Level name="LABOR_DATE" uniqueMembers="false" column="LABOR_DATE" levelType="TimeDays" type="String">

        <KeyExpression>

            <SQL dialect="oracle">to_char(ENTERED_ON,'yyyy-mm-dd')</SQL>

        </KeyExpression>

        <Annotations>

          <Annotation name="AnalyzerDateFormat">&#x5b;yyyy-MM-dd&#x5d;</Annotation>

        </Annotations>

      </Level>

    </Hierarchy>

  </Dimension>

 

Please tell me how to do this properly. I do not want to split the DATE column into year, month, day components. The user wants to deal with just a single "between" filter on one column representing basically calendar dates.

Outcomes