nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Change where caluse based on Report Parameters

shar1975

Hi,

I am having a difficulty to get this done. I have a requirement to display user information based on organization,state,city. If any one or two or all the parameters are selected report should display the user list. Basically what i need is to change the condition in where clause in query based on the parameter selection. How to accomplish this.

Find more posts tagged with

Comments

kclark

In your SQL statement what you would need to do the following. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT *
FROM CLASSICMODELS.EMPLOYEES
WHERE where CLASSICMODELS.EMPLOYEES.OFFICECODE = ? and CLASSICMODELS.EMPLOYEES.REPORTSTO = ?
</pre>
 
After you have created the parameters open edit data set > parameters. You'll see param_1 and param_2 - edit each of them so you can point the correct paramater to each field. <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/73-birt-cascading-parameters/'>Here's</a> a devshare on how to do this, you can also find an example report in Eclipse.

bgbaird

I think what your asking for can also be done in the "before open" event of the dataset.

To do this I ALWAYS have a where clause in the SQL. This sets it up so that I don't have to remember if the SQL has a where clause or not.

SELECT stuff
FROM table
WHERE 1=1

Something like this in the before open script:

if(params["salesRep"].value>0)
{this.queryText=this.queryText+" AND (cust.sales_rep_cd = "+params["salesRep"].value
+" OR cust.inside_sales_rep_cd = "+params["salesRep"].value+") " ;}

if(params["corpCust"].value>0)
{this.queryText=this.queryText+" AND cust.corporate_cust_id = "+params["corpCust"].value;}

if(params["customerID"].value>0)
{this.queryText=this.queryText+" AND cust.customer_id = "+params["customerID"].value;}

if(params["customerID"].value+params["corpCust"].value+params["salesRep"].value==0)
{this.queryText=this.queryText+" AND coli.ship_week between dateadd(dd,-7,getdate()) AND dateadd(dd,7,getdate())"}

Brian

shar1975

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="110567" data-time="1350396663" data-date="16 October 2012 - 07:11 AM">
In your SQL statement what you would need to do the following. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT *
FROM CLASSICMODELS.EMPLOYEES
WHERE where CLASSICMODELS.EMPLOYEES.OFFICECODE = ? and CLASSICMODELS.EMPLOYEES.REPORTSTO = ?
</pre>
 
After you have created the parameters open edit data set > parameters. You'll see param_1 and param_2 - edit each of them so you can point the correct paramater to each field. <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/73-birt-cascading-parameters/'>Here's</a> a devshare on how to do this, you can also find an example report in Eclipse. </blockquote>
 
Hi kclark, 
 
thanks for reply. i have already done this but it did not worked. what i need is to change the condition itself at run time.

shar1975

<blockquote class='ipsBlockquote' data-author="'bgbaird'" data-cid="110591" data-time="1350411090" data-date="16 October 2012 - 11:11 AM">
I think what your asking for can also be done in the "before open" event of the dataset. 
 
To do this I ALWAYS have a where clause in the SQL. This sets it up so that I don't have to remember if the SQL has a where clause or not. 
 
SELECT stuff 
FROM table 
WHERE 1=1 
 
Something like this in the before open script: 
 
if(params["salesRep"].value>0) 
{this.queryText=this.queryText+" AND (cust.sales_rep_cd = "+params["salesRep"].value 
+" OR cust.inside_sales_rep_cd = "+params["salesRep"].value+") " ;} 
 
if(params["corpCust"].value>0) 
{this.queryText=this.queryText+" AND cust.corporate_cust_id = "+params["corpCust"].value;} 
 
if(params["customerID"].value>0) 
{this.queryText=this.queryText+" AND cust.customer_id = "+params["customerID"].value;} 
 
if(params["customerID"].value+params["corpCust"].value+params["salesRep"].value==0) 
{this.queryText=this.queryText+" AND coli.ship_week between dateadd(dd,-7,getdate()) AND dateadd(dd,7,getdate())"} 
 
Brian </blockquote>
 
Hi Brian, 
 
Thank you for the solution. I will try it and will let you know if it worked or not for me.