Get the query of a report by code? Need some help

[Pentacle]

Hi,

What i would like to achieve is to have a button on my report (any report for that matter) which OnClick performs some action and returns to me the query of that report.
Lets say "Select * from houses where country = 'Netherlands'"

Using and modifiying the Samples in the PentahoReportingEngineSDK i have been able to get the QueryNames used in a report.

MasterReport report = new MasterReport();
Sample5 sample5 = new Sample5();
report = sample5.getReportDefinition();
final DataFactory dataFactory = report.getDataFactory();
for (String query : dataFactory.getQueryNames())
{
System.out.println("Query: " + query);
}

Which returns:

- SampleQuery
- Customer Names



So I actually have 2 questions:

1. How can i get the content of the query instead of just the queryname?
2. This example runs from a standalone java app, it would like to do this from a function / button on the report itself. How can i do this?


Thanks!

EDIT: I searched the forum before posting these questions, but couldn't find something that could help me with this problem.

[Pentacle]

A short update:
I examined that i can use the SQLReportDataFactory Class to get the query contents, by getQuery() and getQueryNames()

The problem is that the constructer needs a Connection or ConnectionProvider in order to be initialised.

Of course i can hardcode the connection from my report into the app, but that's a rather unelegant way to approach the problem.

Especially when taken into account that a the connection is already defined in the datasources/sql-ds.xml in the .prpt file itself.

The MasterReport Class and derived DataFactory class don't have methods to get the used Connection object used in the MasterReport that i know of.

[Pentacle]

I also found that the DataConnectionProvider and QueryMapping containing the querys are Embedded in the MasterReport.getDataFactory() object.

For now i have no way of accessing them other then using the Java Eclipse debugger :P

For this moment, only getting the querynames themselves is enough.
What i like to do, is have a simple textfield which on runtime has the value SampleQuery, as is returned by MasterReport.getQuery();

I looked at the Bean Scripting Host and framework, and did found something usefull like on this forum that seems almost usefull:

import org.pentaho.reporting.engine.classic.core.*;
ReportEnvironment env = runtime.getProcessingContext().getEnvironment();
String root = env.getEnvironmentProperty("solutionRoot");

But this is not applicable to the current (Master)report
Does someone have a suggestion how to go from here?

Thanks in advance

[Taqua]

There is no way to get the real query (unless you want to cast your way around). Heck, it is not even guaranteed that there is a query in the end after all. A TableDataFactory has no query, it has the data hardcoded in. A scriptable datasource has no query, but a script, and you cant even tell what is executed unless you also see the parameters and/or run it.

So it basically comes to:
- you have to know what data-source you are using
- you have to have code that knows the internals of each data-source type you wan to handle

Oh, and there can be more than one datasource. Each subreport can have its own datasources in addition to the ones inherited from the master.

However, from *within* a report-function you are not able to get to the datasources declared on the report. We do not want anyone to mess with the datasources while we are working with them. So there are a lot of barriers in place to prevent that to happen. And if you dig your way through it and we hear about it, we will add another layer of barriers - that's promised

[Pentacle]

There is no way to get the real query (unless you want to cast your way around). Heck, it is not even guaranteed that there is a query in the end after all. A TableDataFactory has no query, it has the data hardcoded in. A scriptable datasource has no query, but a script, and you cant even tell what is executed unless you also see the parameters and/or run it.

So it basically comes to:
- you have to know what data-source you are using
- you have to have code that knows the internals of each data-source type you wan to handle

Oh, and there can be more than one datasource. Each subreport can have its own datasources in addition to the ones inherited from the master.

However, from *within* a report-function you are not able to get to the datasources declared on the report. We do not want anyone to mess with the datasources while we are working with them. So there are a lot of barriers in place to prevent that to happen. And if you dig your way through it and we hear about it, we will add another layer of barriers - that's promised

Taqua, thanks for your reaction.

I know there are more possible datasources for a MasterReport, for the sake of Proof of Concept we want to keep it simple, and use only one (known) datasource and query.

In order to make sure we have to same point of reference, let me clarify what we're trying to achieve here:
We have a (in house developed) webbased GIS App, and we like to show Pentaho data on the map. In order to do so we want to send some parameters to the GIS server by querystring (queryname, objectColumn, dataColumn). In the database we have a tbl_query from which i access querys by name.

We abandoned the idea of putting a button on a report to invoke the gis app. Instead of that we would like to make a button next to the print button in Pentaho, which get the fullpath of a report, with a standalone small java app i can get the reference to a report by filename, and return queryname.

I'm not planning to do any evil datasource manipulation at runtime

Now the point of action is getting the filepath of the currently opened report in pentaho, just like "mainToolbarHandler.printClicked()" in main_toolbar.xul probably does.

It seems I first have to dive in pentaho source code to figure how the printing function in pentaho is working.

[Pentacle]

Opened a new topic in Bi-platform > http://forums.pentaho.org/showthread.php?t=73840

Since the question now more applies to the bi-server platform itself.

Thanks for the answers!