using OLAP-Query (Mondrian) with Crosstab

[berndit]

Hi!

 

At least I got JasperReport working with Mondrian. And I got it working with Crosstabs.

 

But now I want to combine them! Okay, I tried just to add the MDX-Query as a subDataset (nested a queryString with language="mdx"), which seems to work somehow:

 

Code:

<subDataset name="Sales_CommodityGroup" >

	<queryString language="mdx"><![CDATA[select NON EMPTY {[Measures].[quantity], [Measures].[price], [Measures].[information_value],[Measures].[marigin]} ON COLUMNS,   
Crossjoin([Article].[all articles].Children, Descendants([Date].[all time].[2006], [Date].[month])) ON ROWS 
from [sales]]]></queryString>

	<field name="month" class="java.lang.String">
		<fieldDescription><![CDATA[Rows[Date][month]]]></fieldDescription>
	</field>
	<field name="commodity group" class="java.lang.String">
		<fieldDescription><![CDATA[Rows[Article][commodity group]]]></fieldDescription>
	</field>
	<field name="quantity" class="java.lang.Number">
		<fieldDescription><![CDATA[Data(Rows[Article][commodity group],Rows[Date][month])(0, ?)]]></fieldDescription>
	</field>
	<field name="price" class="java.lang.Number">
		<fieldDescription><![CDATA[Data(Rows[Article][commodity group],Rows[Date][month])(1,?)]]></fieldDescription>
	</field>

</subDataset>

 

Now I fill my crosstab.

 

If I run the report I get a warning:

 

Opening Mondrian connection

20545297 [Thread-69] WARN olap.JRMondrianQueryExecuter - The supplied mondrian.olap.Connection object is null.

Closing Mondrian connection

 

And the resulting crosstab in the report is empty. If I put the same mondrian query as report query I get results as expected. So the query is fine.

 

So my question: How to get that to work?

 

By the way, I set the Connection for the Crosstab to $P{REPORT_CONNECTION}

 

Code:

[code] <datasetRun subDataset="Sales_CommodityGroup"> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> </datasetRun>

 

I hope there is a solution for that?

 

Thanks in advance

Bernd

[berndit]

Okay, I encounter exactly the same problem when designing a subreport with a OLAP-Query. The problem seems to be the "Mondiran OLAP Connection" Datasource, it can not be passed correctly to the crosstab oder subreport.

 

I get the same warning as with the crosstab:

[TopArticle subreport filler] WARN olap.JRMondrianQueryExecuter - The supplied mondrian.olap.Connection object is null.

 

I tried to use $P{MONDRIAN_CONNECTION} as connection expression also, but that results in a

 

java.lang.ClassCastException: mondrian.rolap.RolapConnection cannot be cast to java.sql.Connection

 

looking in the source file of my reports shows the exception at the following line (maybe a bug?)

 

value = (java.sql.Connection)(((mondrian.olap.Connection)parameter_MONDRIAN_CONNECTION.getValue()));

 

But playing around further with my reports showed me at least a feasable solution:

 

set not the connection but the datasource to $P{REPORT_DATA_SOURCE}. That seems to work also with Mondrian Connections...

 

Sorry for disturbing everybody in the forum...

 

Thanks

Bernd

[berndit]

Celebrated too early. If I give the $P{REPORT_DATA_SOURCE} to the subreport or crosstab, the MDX-Query specified in the mainreport ist used in the sub report/crosstab, not the query specified there.

 

So again: What to do now, to get it working right?

Post edited by: berndit, at: 2006/12/10 13:24

[berndit]

Okay, finally I really got it:

 

configure crosstab or subreport to don't use conneciton or datasource. Instead pass a parameter a parameter to them:

 

parameter name: MONDRIAN_CONNECTION

expression: $P{MONDRIAN_CONNECTION}

 

Then finally it works lik expected... Very wired ;)

 

Maybe there should be a small example for it in the distribution? If I find some time for it I will try to enhance the given mondrian report example, but I can not promise....

 

Regards

Bernd

[lucianc]

connectionExpression is only used for JDBC connections (i.e. for reports having SQL queries). Additional query executers (introduced in JR 1.2.0) specify one or more "connection" parameters, which need to be passed to a subreport/subdataset as parameters (using subreportParameter/datasetParameter elements). We don't want to end up with JRXML elements specific to each query executer (hibernateConnection/entityManager/mondrianConnection/xmlaUrl/etc), keeping things flexible seems like a better solution.

 

If you have a proposal of how this could be improved, you can post it as a feature request (or a patch, if you also implement it). An idea might be supporing a "passConnectionParameters" subreport/datasetRun flag..

 

Regards,

Lucian

[rajan_patil]

Can you please let me know how to pass Mondrian_connection as parameter, I am still not getting the desired output.

[lucianc]

rajan_patil
Wrote:

Can you please let me know how to pass Mondrian_connection as parameter, I am still not getting the desired output.

See the demo/samples/mondrian sample report in the JR distribution (check src/MondrianApp.java).

Regards,

Lucian

PS:  Only post replies to a thread if your message is directly related to the original topic.  Otherwise start a new thread.

[anilguptakgp]

Hi,

I have created mondrian connection, and created fact table in MySQL. But I am unable to create OLAP View. I am trying in below two ways:

1) I tried to create OLAP view using MDX query, but when try to execute any MDX query, then I am getting Internal Error: Member Cache....

 

2) I also tried to create it using cross-tab, but when I select Mondrian Connection, cross-tab is getting disabled.

 

Thanks,

B Anil Kumar.