MDX query with crossjoin columns into ireport

[jay411]

Hello,

what i have is a MDX query that works fine as analysis view. now i want to create the same result within a jasper report by using the same olap data source.

mdx query:
select Crossjoin({[sUPPLIER].[All SUPPLIERs].children}, {[Measures].[VALUE1], [Measures].[VALUE2]}) ON COLUMNS,
  NON EMPTY {[PRODUCTS].Members} ON ROWS
from [sPM]

i used a crossjoin to create a result with 6 columns and 3 column groups, like:
supplier1#supplier2#supplier3
value1,value2#value1,value2#value1,value2

to make it even more complicated .....the number of columns is dynamic!

when inserting this mdx into ireport report query, it does not show me the measures. all i see is:
COLUMNS
#SUPPLIER
#-#(All)
#-#SUPPLIER
#Measures
#-#MeasuresLevel
ROWS
#.....

how can i use grouped column data from olap sources in jasper reports?

i guess, another way would be to simply access the relational data and create a crosstab. but the olap should have better performance and is made for such calculations.

Thanks for all answers and thoughts,

Jay

Post Edited by Jan Eberle at 01/14/09 16:04

[mdahlman]

Jay,

 

Have you looked at the sample MDX report that ships with JasperServer Pro? (/analysis/reports/FoodmartSalesMondrianReport) It groups like you describe, so it might be the best place to get started.

 

Regards,
Matt

[jay411]

Matt,

I think i found the report "FoodmartSalesMondrianReport" and attached it here.
there are grouped rows, but i am searching for grouped columns.

Do you have an example where this done?

Thanks,
Jay

[liancute20]

Can anyone give me a step by step instruction on how to create an olap mondrian report with mdx query???

Please help..i need it urgently.

tnx ;D

Lian

[jay411]

Hi Lian,

can you please create another topic for your question?
You will get more answers if you have your own thread.

Jay

 

So, back to my problem, is there anybody that made an MDX query with grouped columns work in iReport?

[jonnyfrastovner]

You can find an example <a href="http://dbh.nsd.uib.no/rapporter/nokkeltall/studenter_visning.action?inst=[institusjon.institusjoner%20flat%20liste].[alle%20institusjoner].[universitetet%20i%20Oslo]&Fagfelt=[Norsk%20standard%20for%20utdanningsgruppering%20(NUS)].[Totalt]&niva=1&nuskode=&nusniva=0&instkode=1110"> here </a> (sorry, norwegian only, drill using the column to the right). It was non-trivial to accomplish, i'd suggest you buy the ultimate guide, that helped us a lot. We used xmla rather than mondrian directly.

[jay411]

Thank you for that hint with XML/A.

Now i receive the required measure fields in iReport.
But it still doesn't work, as i get no data.


nsdanstatt,
how do you build your grouped columns in MDX?
with crossjoin()?
 

That's how I create my columns:
SELECT NON EMPTY Crossjoin({[DIM_ONE].Children}, {[Measures].[MEAS_ONE]}) ON COLUMNS etc.....

 

 

[jay411]

by now I think its impossible to create any kind of dynamic columns in iReport based on OLAP data.

a solution would be to create a report "by scratch" with java code without using iReport and generate the JRXML at runtime.
 

so, unless anyone can prove me wrong, I guess it's just like that.
 

 

[liancute20]

elow jay,

im so sorry for interrupting your topic..i dont know how to create a topic coz im just newly registered here... ;D

 

lian

[jens_noering]

For me the following is working:

First create a new subdataset in your report.

Then create the mdx in the subdataset in the following way :

 

select {[Measures].[VALUE1], [Measures].[VALUE2]} ON COLUMNS,

{[sUPPLIER].[All SUPPLIERs].children} on ROWS
  NON EMPTY {[PRODUCTS].Members} ON PAGES
from [sPM]

 

The field mapping is the following :

 

for value1 measures : Data([Measures].[VALUE1],?,?)

for value2 measures : Data([Measures].[VALUE2],?,?)

for supliers: ROWS[supplier]

for products: PAGES[Products]

 

then your dmx should work.

 

after that you create a crosstab which uses the subdataset.

Now you can put your Suppliers on Columns, your Products on Rows and the Measures are the Measures of the crosstab.

Important is that you have to pass the MONDRIAN_CONNECTION Parameter to the subdataset. Without this Parameter there will be no result.

In the main datasource of the report you have to create a dummy query. For example:

select {[Measures].[VALUE1]} on Columns from [sPM]

 

This should work and give you a table like result that has been created dynamicly.

 

hth,

Jens

 

Post Edited by Jens Noering at 01/21/09 08:02

[jay411]

Hi Jens,

thank you for that perfect explanation! ... i did't know that ON PAGES does exist.
Now, my report ist working great and shows dynamically generated columns.

I have to admit that dynamic columns from olap sources is working fine!

 

but I have another question...

I tried to add parameters to the report and get this error when my subdataset returns no data:
No Such Tuple ([Measures].[VALUE1] on Axis 0.

I have passed the parameters from the main report to the subdataset the same way as the MONDRIAN_CONNECTION.
My subdataset query has a Where condition that looks like:
....where ([Area].[$P{PARAM}])          

there are 2 cases:
1) i fill PARAM with a value that gives me result data -----> fine
2) i fill PARAM with a value that gives NO result data -----> error msg

I know there are possibilites to catch empty results, but with subdataset queries ...i dont know how.
anybody can help?

-Jay

 

 

[jens_noering]

I guess this is a member not found exception.

have you set the NON EMPTY clause on the columns axis ?

This could be the problem.

 

 

 

 

Post Edited by Jens Noering at 01/21/09 17:15

[jay411]

yep, that was the problem. thank you again!

i had to remove 'non empty' from columns ...

[rajan_patil]

jens_noering
Wrote:

Important is that you have to pass the MONDRIAN_CONNECTION Parameter to the subdataset. Without this Parameter there will be no result.

Can you please describe the steps for passing the MONDRIAN_CONNECTION from main report to subdataset. Right now I am not able to see any data in report.

[raylee]

How did you guys get passed the query dialog box?  Everytime I enter an MDX I get "No such tuple (________) on axis 0." This happens on iReport 4.1.3 and 4.5.0.  Java 6 Update 23 (32bit).  I posted on the forum under a different thread (link below) but haven't received a response yet.

http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=95384