How to construct dynamic query for reports

[rajeevpv]

hi,

 

need help on constructing an SQL query.

 

my requirement is something like this.

generate a report on task ( with statuses RUNNING, COMPLETED, FAILED). The status is passed on from the screen (A combo box) and jasper will generate the report. The query i need to attach to the .jasper report is something like this.

 

SELECT X,Y,Z

FROM task_table

WHERE status IN (CASE WHEN $P{STATUS}='ALL' THEN (SELECT different_tasks_statuses FROM FEPS.TASK_STATUS)ELSE $P{STATUS} END)

 

where $P{STATUS} is the report parameter whose value is passed from the screen based on the combo box (drop down text) selection.

 

i can create report if we query it for a specific status. but there is a special case, where when we select 'ALL' from the screen, report should fetch tasks belonging to all statuses (see the query condition in the above query).

 

1. i can do this by designing a separate report (separate query then) for this special case (passing ALL). but i dont want to do that since there are amny filter combos with 'ALL'.

2. neither can i pass a dynamic where clause to the query generator

 

if somehow we can construct a string literal i think we can do this with a decode or case statement.

 

if you have any clue, pls let me know fast.

 

thanks,

Rajeev

[lucianc]

You can create a new parameter and use its default value expression to construct the where clause:

Code:

<parameter name="STATUS"/>
<parameter name="whereClause" isForPrompting="false">
  <defaultValueExpression>
    $P{STATUS}.equals("ALL"«») ? "" : (" WHERE status = '" + $P{STATUS} + "'"«»)
  </defaultValueExpression>
</parameter>
<queryString>
  SELECT .. FROM .. $P!{whereClause}
</queryString>

 

This solution has the disadvantage that the status is no longer sent as prepared statement parameter. If you think this is a problem, you can easily find a trick around this (e.g. create parameter having $P{STATUS}.equals("ALL") ? "true" : "false" as default value and use WHERE ($P!{yourParam} OR status = $P{STATUS}) in the query).

 

HTH,

Lucian

[rajeevpv]

thanks a lot Lucian,

it worked fine with the whereClause parameter as u suggested.

but i didnt understand your tip on passing preparedstatment parameter. i tried changing the query to

SELECT X,Y,Z

FROM task

WHERE $P!{IS_TRUE} OR STATUS = $P{STATUS}

 

which will thro ORA-00920:ÂinvalidÂrelationalÂoperator exception.

where IS_TRUE is defined as $P{STATUS}.equals("ALL") ? "true" : "false".

 

i also have one more doubt, whats the difference between specifying parameter as $P{STATUS} and $P!{STATUS}.

 

thanks,

Rajeev

[lucianc]

Read this page to learn about query parameters.

 

Regarding the query error, obviously true and false are not Oracle literals, so you'll have to use something like $P{STATUS}.equals("ALL") ? "0 = 0" : "0 = 1".

 

HTH,

Lucian

Post edited by: lucianc, at: 2006/11/21 15:26

[rajeevpv]

excellent Lucian,

thanks for your help..

[pbj1214]

Hi Rajeev!
I have the same problem as yours, i need to do something like this.
Generate a report on Vehicle ( with statuses RUNNING, Stopped). The status is passed on from the screen (A combo box) and jasper will generate
the report. The query i need to attach to the .jasper report is something like this.

SELECT X,Y,Z
FROM vehicle
where vehicleId=1234
and status  ?? (should can be either Running , stopped or ALL as selected by the user from the combo box on the screen.
If the user selects ALL in that case the records for both the status should be displayed)

Regards
Puneet
 

[pedror]

Hi!

Is it possible to add a parameter to FROM clause? Or this question doesn´t make any sense? In that way users will have much more freedom to choose, and it saves me some work, I think

Thanks in advance!

[vinay.kulkarni]

We are looking answers for following Questions

1. How to embed custom DynamicSchemaProcessor in Jasper Report Server (We would like to use that to dynamically modify the schema based on user credentials in the query.

2. Is there any way to edit the query ( replacing Query Parameter in the sql query) programmatically before it is executing. 

basically we need to change the 'where' clause based on user credentails.

 

Many Thanks