Problem with mondrian Parent-Child Hierarchy

[jens_noering]

Hello,

 

in my company we`re trying to build our reporting system on jasperintelligence/ireport.

 

For our account-structure i`ve built a Parent-Child hierarchy like the employee hierarchy in the mondrian-foodmart sample.

With Jasperintelligence/jpivot this hierarchy-structure is working very well. With ireport there is a problem where I need your help:

 

The ireport/jasperreports program sends the right calculated cells back, but the describtion of the fields only prints the highest entity of the structure.

 

For example: There is a account called Contribution 3 this is a parent of contribution 2, the measure is called "Salden". I want to select this 2 values. My Query looks like this:

 

Code:

select {[Measures].[salden]} ON COLUMNS,
  {[Return on Sales].[Alle Konten].[Contribution 3], [Return on Sales].[Alle Konten].[Contribution 3].[Contribution 2]} ON ROWS
from [ros]

 

My output file looks like this:

 

Contribution 3 | 1.750.000 € Contribution 3 | 2.500.000 €

The second row is the contribution 2 row and the value is the right one. but the describtion does not match.

 

Can someone help me with this problem.

[lucianc]

Indeed, JR's OLAP field mapping mechanism doesn't seem to handle hierarchical dimensions very well.

 

As a workaroung, you can map a field to the last/lowest member on an axis and use this field to get the data you need. I've atached a report against the Foodmart Mondrian schema that shows how to do this.

 

Not sure right now how the field mapping could be improved to handle this better. Let us know whether you have some inputs/suggestions/etc on this.

 

Regards,

Lucian [file name=olap_closure.jrxml size=1296]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/olap_closure.jrxml[/file]

[jens_noering]

thanks for your fast answer, but your suggestion seems to go the same way my own query does. I`ll found a way to get the right description to the values. My Query looks like this:

 

Code:

select [measures].[salden] on columns, 
{[Return on Sales].[Konto].[Contribution 2],[Return on Sales].[Konto].[Contribution 1]
from [ros]

 

"[Konto]" is the level of the dimension and i`m selecting the direct elemnt.

 

Now is the problem, that it seems that I can`t work with crossjoin on that axis. Is there a way to use more than 2 axis. because i`ll have to implement an other dimension witch has the same parent-child hierarchy.

[lucianc]

What JR version are you using? The sample I posted works fine for me..

 

You can use as many axes as you want (actually, as many as Mondrian supports). You can refer to the first 5 axes by their names (Columns, Rows, Pages, Chapters, Sections) and to any axis by its index (Axis(7), etc).

 

HTH,

Lucian

[jens_noering]

Thanxx lucianc,

 

I´m usinig IReport 1.2.7, so i think i'm using Jaspereports 1.2.7 too.

 

Now I´m trying to use multiple axis in my query:

 

Code:

select {[Measures].[salden]}  on AXIS(0),

{[Periode].[Jahr].[2006]} on AXIS(1),
{[Return on Sales].[Konto].[Contribution 3],[Return on Sales].[Konto].[Contribution 2]} on AXIS(2),
{[Kostenstellen].[Kostenstellen].[Verkauf Gesamt],[Kostenstellen].[Kostenstellen].[Verkauf PKW],[Kostenstellen].[Kostenstellen].[Verkauf LKW], [Kostenstellen].[Kostenstellen].[Verkauf TRAPO]} on AXIS(4)
from [ros]

 

but how the fieldmapping has to be done?

 

I`ve tried it this way by mapping all the axis to another and so on like this Expression:

Data(Rows[Periode][Jahr])(Pages[Return on Sales][Konto])(Chapters[Kostenstellen][Kostenstellen])([Measures].[salden],?)

 

Bute then ireport returns the following error:

Incorrect data mapping: the number of positions doesn't match the number of axes.

 

Maybe you can help me

[lucianc]

Not sure what you're trying to achieve with this mapping, but I think the correct syntax is

Code:

Data(Rows[Periode][Jahr],Pages[Return on Sales][Konto],Chapters[Kostenstellen][Kostenstellen])([Measures].[salden],?,?,?)

 

HTH,

Lucian

[jens_noering]

Ok,

 

solved this problem by myself.

 

Only had to add the ",?" for every axis in the datafield mapping.

 

That Problem is fixed now and my report is working well.