how to pass a query to Pentaho 3.5.0

[psapozhnikov]

Hi

In my BI Server 3.5.0 I have xactions and prpts. When I call an xaction from my j2ee web app I can set a query using postMethod.setRequestBody(query) and then Bi Server uses this new query to generate html and it works!

When I do the same method to prpt it doesn't seem to work. The html comes back using the query that's inside the report and not the new query.

So obviously my method is wrong for prpt. Does anybody know how to set a query from my web app to prpt?

Any help/comments will be greately appreciated as always.

Thanks
Pavel

[zulu]

Hi Pavel,

The xaction could have a "Get data from - Relational" process action. Set the database connection and query you want, and add a Result Set Name of "queryData".

Add the "queryData" as a Report Parameter in the "Pentaho Report" process action that I assume you already have.

Hope this helps,
Guy

[psapozhnikov]

Hi Zulu

Thank you for your input, although I think you might have misunderstood my question.

Let me elaborate!

There are I think 2 types of reports:

1. First type is basically xaction and xml combination in which xaction references xml and what you do from web app is call xaction and you can actually pass in query there right from web app using PostMethod http://hc.apache.org/httpclient-3.x/...ostMethod.html

2.Second type is prpt and what I figured is that I can still use HttpClient to call a prpt, now prpt is more or less a zip file as I understand and maybe that's why when I do postMethod.addParameter('queryData', 'select * from ...') that doesn't actually take any effect.

So basically I was wondering if its possible in my web app to add predefined query to prpt not to xaction (I know it works in xaction)

The more I think about it, the more I realize that probably will not work. Perhaps if I open an xaction and change from xml to prpt that might work who knows.

[mgie]

I do not completely understand what you're trying to do but I think your prpt report must have a datasource of the type advanced->external so that you can pass in the query from an xaction (with the simplereportingcomponent i guess). I didn't try it yet but I was asking myself if it would be possible to rebuild the guided-adhoc and dynamic crosstab examples in prpt that way.

[psapozhnikov]

I do not completely understand what you're trying to do but I think your prpt report must have a datasource of the type advanced->external so that you can pass in the query from an xaction (with the simplereportingcomponent i guess). I didn't try it yet but I was asking myself if it would be possible to rebuild the guided-adhoc and dynamic crosstab examples in prpt that way.

That's very interesting, can you elaborate on that.

What I tried was something else and that does actually work. Let me explain:

1. Put query in store proc with params of course to the proc
2. In the prpt itself Mast Report query call the proc itself with params like this ${param1}, ${param2} and so on
3. In the prpt create the params
4. In the code in postMethod do postMethod.addParameter(param1, param1Value), postMethod.addParameter(param2, param2Value)

I tried this and it actually does work which in a way does what I wanted to do but it would actually be nice to say postMethod.addParameter(reportQuery, 'select * from..')

[Taqua]

Within PRD, you can use the Advanced->SQL (Custom) datasource for a similar thing. Then you can pass the query in via formula on the report's query-name attribute.

In 3.6, there is a example report under the advanced samples called "Preprocessor" or so, that shows the use of that datasource.

[psapozhnikov]

Within PRD, you can use the Advanced->SQL (Custom) datasource for a similar thing. Then you can pass the query in via formula on the report's query-name attribute.

In 3.6, there is a example report under the advanced samples called "Preprocessor" or so, that shows the use of that datasource.

Taqua thank you can you link me to that?

So I went to Advanced -> SQL (Custom) and I picked JNDI because the connection is actually there already in jboss as jndi. So now you saying I can do postMethod.addParameter('query-name', 'select * from ... ' ) and that will actually work?

[Taqua]

You have to take care of a few things:

1) You have to declare a text-parameter called "query-name", of course
2) You have to set the formula "=[query-name]" on the query::name attribute of the master-report. (Hit the green "+" for the expression/formula-editor)
3) You have to add the "JDBC (Custom)" datasource and configure your connection
4) Your query has to return the same field names every time, or the report-field will not find the fields they were looking for.

If you want to absolutely dynamic, look at that sample (Help->Welcome; and then under the "Advanced" section on the right hand side), the master-report has a report-pre-processor defined.

http://wiki.pentaho.com/display/Repo...orIntroduction

BTW: Generally it is a very bad idea to get a query directly from the outside world. Users can do rather nasty things with that, like getting all your data out of your database, including the super secret stuff that no one (especially the SEC or the IRS) should ever see.

[psapozhnikov]

You have to take care of a few things:

1) You have to declare a text-parameter called "query-name", of course
2) You have to set the formula "=[query-name]" on the query::name attribute of the master-report. (Hit the green "+" for the expression/formula-editor)
3) You have to add the "JDBC (Custom)" datasource and configure your connection
4) Your query has to return the same field names every time, or the report-field will not find the fields they were looking for.

If you want to absolutely dynamic, look at that sample (Help->Welcome; and then under the "Advanced" section on the right hand side), the master-report has a report-pre-processor defined.

http://wiki.pentaho.com/display/Repo...orIntroduction

BTW: Generally it is a very bad idea to get a query directly from the outside world. Users can do rather nasty things with that, like getting all your data out of your database, including the super secret stuff that no one (especially the SEC or the IRS) should ever see.

Taqua thank you. You're a genius. This has solved it thank you. No need for any procs.

How can I mark this resolved?

[mgie]

I didn't get into the topic too much yet but i think i will need this quite soon... so:

Taqua, is it still possible with prpt to get something like described in
http://forums.pentaho.org/showthread.php?t=64279
to work? I mean a guided ad-hoc based on cdf where the user can select his columns dynamically?