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

Newbie BIRT Developer looking for date variables

JagThripp

Hi There esteemed BIRT developers.
 
I am new to developing in BIRT, however I have used a few other report development tools in the past.
 
I am starting off small here, so apologies for the stupid question. 
I have tried searching for a solution to my hurdle but alas nothing seems to full my needs.
 
I am trying to develop a listing report for Maximo users that they can run themselves and uses a date range as the input parameters for the report i.e. DateFrom and DateTo, I am just not sure how to integrate this into the report OPEN query.
 
sqlText = "select p.displayname " 
+ " ,LT.LABORCODE " 
+ " ,LT.CRAFT " 
+ " ,LT.REFWO " 
+ " ,LT.STARTDATE " 
+ " ,LT.REGULARHRS " 
+ " ,LT.PAYRATE " 
+ " ,WO.WORKTYPE " 
+ " ,wl.description " 
+ " from labtrans lt " 
+ " join workorder wo on lt.refwo = wo.wonum " 
+ " left join worklog wl on (wl.recordkey = wo.wonum and wl.class = 'WORKORDER') " 
+ " join person p on LT.LABORCODE = P.PERSONID " 
+ " where LT.STARTDATE between to_date('01/01/2014, 'dd/mm/yyyy') and to_date('15/12/2015', 'dd/mm/yyyy') " 
+ " order by LT.STARTDATE desc ";
 
Once again apologies for the elementary question.
 
Jag

Find more posts tagged with

Comments

jfranken

On the Data Explorer tab, you'll see "Report Parameters". Add a parameter for each data value you want the user to enter (they will be prompted to enter the values when the report is run). In your Data Set query, put a question mark (?) as a placeholder in the where clause for each parameter value. Select the "Parameters" option in the Edit Data Set window. A Data Set parameter should automatically be created for each question mark in the query. Link the Data Set parameters to the Report Parameters in the Data Set parameter edit window.
 
Alternatively, you can modify the query in code by setting this.querytext = "yourquery" in the beforeOpen event of the Data Set. The parameter values are referenced as: params["paramName"].value. This is only required for complex queries where the automated functionality has issues inserting the parameter values.

JagThripp

Hi Jeff thanks for the reply. 
I have modified the report to your sugesstion, bu tI have one question on the set up of the query in the Open Script. 
Can I use the "?" placeholder like this?
 
sqlText = "select p.displayname " 
+ " ,LT.LABORCODE " 
+ " ,LT.CRAFT " 
+ " ,LT.REFWO " 
+ " ,LT.STARTDATE " 
+ " ,LT.REGULARHRS " 
+ " ,LT.PAYRATE " 
+ " ,WO.WORKTYPE " 
+ " ,wl.description " 
+ " from labtrans lt " 
+ " join workorder wo on lt.refwo = wo.wonum " 
+ " left join worklog wl on (wl.recordkey = wo.wonum and wl.class = 'WORKORDER') " 
+ " join person p on LT.LABORCODE = P.PERSONID " 
+ " WHERE LT.STARTDATE BETWEEN TO_DATE('?', 'dd/mm/yyyy') and to_date('?', 'dd/mm/yyyy') " 
+ " order by LT.STARTDATE desc ";
 
I have added the Variables FromDate and ToDate to the DataSet Parameters.
 
I must say I am no longer getting the error I was, but now nothing is returned.
 
Also I am not being prompted for the dates in the range I am querying for using this mothod.
 
I am using Eclipse 3.7.1
 
Thanks again.

jfranken

Hi Jag,
 
I don't know what you are referring to when you say the "Open" script.
 
Assuming you're working in any recent version of the BIRT Designer, try the following:
 
1. Create a new report and add a new Data Source and Data Set.
 
2. Go to the Query tab in the Data Set editor and enter the following query:
 
select p.displayname, LT.LABORCODE, LT.CRAFT, LT.REFWO, LT.STARTDATE, LT.REGULARHRS,
LT.PAYRATE, WO.WORKTYPE, wl.description 
from labtrans lt 
join workorder wo on lt.refwo = wo.wonum 
left join worklog wl on (wl.recordkey = wo.wonum and wl.class = 'WORKORDER') 
join person p on LT.LABORCODE = P.PERSONID
 
3. Make sure it returns data and then save the Data Set.
 
4. Click the Data Set on the Data Explorer tab to make sure it is selected, and then click the Script tab in the Layout area
 
5. Go to the beforeOpen event
 
6. Type in the following code:
 
this.queryText = this.queryText + " WHERE LT.STARTDATE BETWEEN TO_DATE('" + params["fromDate"].value + "', 'dd/mm/yyyy') and to_date('" + params["toDate"].value + "', 'dd/mm/yyyy') order by LT.STARTDATE desc";
 
7. On the Data Explorer tab, go to Report Parameters and create two new parameters named "fromDate" and "toDate"
 
8. Drag the Data Set to the Layout area to create a table.
 
9. Run the report.
 
You should be prompted to enter the parameter values when the report runs. The values you enter should filter the data in the report.
 
The code in step 6 gets the parameter values entered by the user, creates the "where" clause filling in those values, and appends the clause to the query.
 
Important note: I cannot test the query or the code. Hopefully it will run, but you might have to debug it. Also, in the final version, it would be good to add some code to validate the parameter values prior to creating the "where" clause.
 
The question mark notation that I mentioned previously only works if you have a simple "where" clause and is only used in the Data Set editor on the screen where you enter the query. It does not require scripting, only linking the Data Set parameters to the report parameters. Give the code above a try first and see if that works as a way to create the desired "where" clause.