How exaclty the $P!{WhereClause} works.

[pentaho]

Hi,


I have designed one simple report with dynamic PARAMETERS in query.......

select  monitor_name, customer_name, server_name, monitor_type
from Monitor
where $P!{WhereClause}
order by customer_name, server_name


If I want to publish this report in Jasper server, I have Two Optional Parameters.............

HOW TO ADD THESE  TWO PARAMETERS(optional) IN JASPERSERVER.......

1. Customer Name

2. Server Name


How exaclty the $P!{WhereClause} works.................

The above two parametrs are optional and these two parameters are not added in iReport JRXML file....................

I have attached my jrxml file...........

I would appreciate any help......

 

[pentaho]

Hi,

 

select  monitor_name, customer_name, server_name, monitor_type
from Monitor
where $P!{WhereClause}
order by customer_name, server_name

parameter  $P!{WhereClause} Default Value Expression is "1=1"

The above query at run time is given below

select  monitor_name, customer_name, server_name, monitor_type
from Monitor
where 1=1
order by customer_name, server_name

The above query will display all the records........

 

I want to filter the data based on customer_name and server_name but these two parameters are optional values.

How can i add these two parameters in jasper server to filter the data..................

The final query if i select the customer_name  and  server_name 

select  monitor_name, customer_name, server_name, monitor_type
from Monitor
where customer_name='xyz'  and  server_name='server1'
order by customer_name, server_name

 

How to implement the above things in jasper server..............

The parameters are optional, they are passed dynamically to the sql $P!{WhereClause} parameter........

[Gaby38]

If I well understand your problem, you want to have different filtering  "levels" in your query . If I were you, I should do the following :

- have a general query like :

"select  monitor_name, customer_name, server_name, monitor_type
from Monitor $P!{WHERE}"

- define the three parameters in iReport (or jrxml) : WHERE, CUSTOMERNAME,USERNAME and define an input control for the two last parameters.

- in the "default expression" of WHERE, calculate the expression according to the values of the two other parameters :( I write in a "logic" syntax because I am null in Java language : you will have to translate):

- CUSTOMERNAME and USERNAME are empty : all records so WHERE=""

- CUSTOMERNAME is empty but not USERNAME : WHERE="where username='XXXX'"

- USERNAME is empty but not CUSTOMERNAME : WHERE="where customername='XXXX'"

- CUSTOMERNAME and USERNAME are not empty : WHERE="where username='XXXX' and servername='YYYY'"

Hope this will help you

[pentaho]

Hi,


Got the solution...............

Single JRXML query can be filter based on WhereClause Values........

For the below query...........

select  monitor_name, customer_name, server_name, monitor_type
from Monitor
where $P!{WhereClause}
order by customer_name, server_name

URL is
http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Test/i

ncd&standAlone=true&ParentFolderUri=/reports/Test&WhereClause=customer_name='abc' and

server_name='Ser1'

The JRXML file i have added only one parameter WhereClause and the default value is 1=1........

If you are publishing the jrxml file, add input control(parameter name) as WhereClause, Type as

single value  and locally defined & data type name as WhereClause, Type as

Text/number/date/datetime.

Finally in URL you can pass any parameters.............

Filter the data  by passing dynamic fields to WherClause

Type 1:

customer_name, & server_name

WhereClause=customer_name='abc' and server_name='Ser1'

http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Test/i

ncd&standAlone=true&ParentFolderUri=/reports/Test&WhereClause=customer_name='abc' and

server_name='Ser1'


Type 2:

monitor_name and monitor_type

WhereClause=monitor_name='mn1' and monitor_type='mt'

http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Test/i

ncd&standAlone=true&ParentFolderUri=/reports/Test&WhereClause=monitor_name='mn1' and

monitor_type='mt'


Type 3:


monitor_id and monitor_place

WhereClause=monitor_id='mid1' and monitor_place='mp'

http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Test/i

ncd&standAlone=true&ParentFolderUri=/reports/Test&WhereClause=monitor_id='mid1' and

monitor_place='mp'




Filter the Records
Just add the

& WhereClause=monitor_id='mid1' and monitor_place='mp' at the end of your URL..........

 

Donot Filter(All Records)

If you want to display all values without filtering the data, default vaue expression for WhereClause is 1=1


Your URL will be without WhereClause

http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Test/i

ncd&standAlone=true&ParentFolderUri=/reports/Test