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; } } }

Passing parameter (date) to sql query

jegan

Hi there, 
 
I'm a newbie to Birt. I'm designing a report which pick parameter from users. below is my sample sql script. Postgresql is the db. 
 
select 
account.account_id, 
(select count(transaction_id) FROM transaction WHERE transaction.account_id = account.account_id AND transaction.amount < 0::numeric and reversed = 'f') as no_of_payment, 
(SELECT sum(transaction.amount * -1::numeric) FROM transaction WHERE transaction.account_id = account.account_id AND transaction_type_id not in (1,10) and reversed = 'f' and date_trunc('month', tran_date) = date_trunc('month', date('20120901'))) AS paid, 
(select sum(amount) from promise where promise.account_id = account.account_id and date_trunc('month', promise_date) = date_trunc('month', date('20120901')) and broken <> 't') as ptp_amt, 
from account 
where client_id = ? 
 
now i manage to set parameter for client_id but how do set parameter for paid and ptp_amt so that user can choose the month instead coding it in script? And the month show display as list box on report viewer. 
 
How someone can assist me on this. Thanks.

Find more posts tagged with

Comments

mwilliams

You just want the values of 1-12 to show for the month, in the parameter screen? Then, use this value in the query?

jegan

Hi william, 
 
Exactly. I want paramater in screen shows as MONTH YEAR then based on the month selected, the value need to be passed to the query. Thanks in advance. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110147" data-time="1349367590" data-date="04 October 2012 - 09:19 AM">
You just want the values of 1-12 to show for the month, in the parameter screen? Then, use this value in the query? </blockquote>

mwilliams

You could easily create a dataSet that returns all the values you need, that you can use as a list box parameter for the user to select from, then you could just link that selected value with your dataSet parameter. You say you don't want to use script, but another simple way would be to take the input date, from the user, format it how you need in your beforeOpen script of your dataSet, and edit your queryText with the correct value.

jegan

Hi William, 
 
Once again thanks for your reply. As I'm just learning to use BIRT, would be possible if you could help me with the beforeOpen script and how do I edit in my queryText? Feel like I'm so alien here :unsure: 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110182" data-time="1349458571" data-date="05 October 2012 - 10:36 AM">
You could easily create a dataSet that returns all the values you need, that you can use as a list box parameter for the user to select from, then you could just link that selected value with your dataSet parameter. You say you don't want to use script, but another simple way would be to take the input date, from the user, format it how you need in your beforeOpen script of your dataSet, and edit your queryText with the correct value. </blockquote>

mwilliams

Ok. Say you take in a report parameter, "orderDate". The user enters a value of 2012-10-08. In the beforeOpen script of your dataSet, you could format the necessary date format to pass to your dataSet with something like:

df = new java.text.SimpleDateFormat("MM yyyy"); //whatever format your truncated data would be in
queryDate = df.format(params["orderDate"]);
queryDate = queryDate.toString();

In the query builder, instead of putting ? for the parameter, put something like 'myDateParameter' or any other marker that you know won't appear anywhere but here. Then, you can have this script to replace it, in your beforeOpen:

this.queryText = this.queryText.replace("myDateParameter",queryDate);

This is just approximate code. I wasn't able to test date_trunc.

jegan

Hi william, 
 
Thanks a a lot for the sample coding. I got it work this time with your assistance. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="110251" data-time="1349740731" data-date="08 October 2012 - 04:58 PM">
Ok. Say you take in a report parameter, "orderDate". The user enters a value of 2012-10-08. In the beforeOpen script of your dataSet, you could format the necessary date format to pass to your dataSet with something like: 
 
df = new java.text.SimpleDateFormat("MM yyyy"); //whatever format your truncated data would be in 
queryDate = df.format(params["orderDate"]); 
queryDate = queryDate.toString(); 
 
In the query builder, instead of putting ? for the parameter, put something like 'myDateParameter' or any other marker that you know won't appear anywhere but here. Then, you can have this script to replace it, in your beforeOpen: 
 
this.queryText = this.queryText.replace("myDateParameter",queryDate); 
 
This is just approximate code. I wasn't able to test date_trunc. </blockquote>

mwilliams

Great to hear! Let us know whenever you have questions!