0

I have a problem using mondrian and JPivot

The problem is that when I connect my application with an Oracle database, and access to the drill through table, all the values are shown with the format 2.010,00 that refers to the year 2010. This problem only happen with Oracle DB if I connect my application with a Postgres or SqlServer database the values are shown like 2010.

More information:

My cube has two dimensions: CREATION_DATE and STATUS, and one measure:

(sorry, i have to change the tags for display in here, asume the next xml is in correct format)

 -SCHEMA name="AAAA" envId="0"-
  -Cube name="AAAA" envId="0" cache="true" enabled="true"-
   -Table name="MY_FACT_TABLE" alias="MY_FACT_TABLE"-
   -Dimension name="FECHA_CREACION" envId="0"-
    -Hierarchy name="FECHA_CREACION" hasAll="true" allMemberName="All FECHA_CREACION"-
     -Level name="PRO_CREATE_YEAR" column="PRO_CREATE_YEAR" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"-
     -Level name="PRO_CREATE_SEM" column="PRO_CREATE_SEM" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"-
    -Hierarchy-
   -Dimension-
   -Dimension name="ESTADO" envId="0"-
    -Hierarchy name="ESTADO" hasAll="true" allMemberName="All ESTADO"-
     -Level name="PRO_STATUS" column="PRO_STATUS" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"-
    -Hierarchy-
   -Dimension-
   -Measure name="MEASURE1" column="DW_PRO_ID" aggregator="count" visible="true" formatString="#,###.0" datatype="Numeric" caption="MEASURE1"-
  -Cube-
 -SCHEMA-

In the database, the table MY_FACT_TABLE has the column PRO_CREATE_YEAR with type 'INT' and all the values inserted in that column where 2010 and 2011.

Drill Through Table for MEASURE1:

PRO_CREATE_YEAR | PRO_CREATE_SEM | PRO_STATUS | MEASURE1
2.010,00 | SEM1 | A | 1,00
2.010,00 | SEM1 | A | 3,00
2.010,00 | SEM1 | A | 2,00

I already tried to:
- change the levelType on the level PRO_CREATE_YEAR to "TimeYears".
- change the type of the dimension to "TimeDimension".
- change the type of the column PRO_CREATE_YEAR to number, number(9).
and nothing change.

mzy
  • 1,754
  • 2
  • 20
  • 36
sebasto
  • 1
  • 1

1 Answers1

1

You should start by defining your dimension as a "time" type. There are plenty of examples out there. This will allow you to use the full power of MDX's time functions and calculations.

You should also consider using the attributes nameColumn and captionColumn of the level element. You can get more details on these in Mondrian's schema documentation.

If you are willing to go the extra mile and contribute your findings with the members of the Mondrian community, feel free to reach out to the mailing list or file a bug report in the tracking system.

Luc
  • 672
  • 3
  • 8
  • Luc, thanks a lot for the answer. I already tried defining my dimension as a "time" type and nothing changes. I'm going to try to include the class MondrianDrillThrough.java and MondrianDrillThroughTableModel.java in my project and edit the method executeQuery() to control the format. If i sucess i'll published de solution. Thks a lot! – sebasto Feb 08 '13 at 18:03
  • Besides the error is only with the Oracle DB, in others DB is just fine. None using JPivot/Mondrian with Oracle has this problem?? – sebasto Feb 08 '13 at 18:05
  • I recall that I've seen this before, and I dont think I fixed it at the time. but unfortunately I no longer have an oracle env to test against. – Codek Feb 11 '13 at 09:54