Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Include report variable in Data set SQL query in detail table(Using Report variables in a Data Set SQL query for detail table)
Include report variable in Data set SQL query in detail table [message #1020310] Sun, 17 March 2013 22:18 Go to next message
Jeff Stouse is currently offline Jeff StouseFriend
Messages: 13
Registered: May 2012
Junior Member
All,

I have a report which uses a master table and a detail table. In the detail table, the records need to be selected by date range. (Another issue, but not one I will address in this post, is how to convert the value the use would normally input as date into a Clarion date format - i.e., the number of days, not an actual date.)

What I want to do is have the user running the report input the report begin and end dates as report parameters (i.e., numbers like 77405 and 77495) and have those passed to the SQL query in the detail table data set. (Clarion stores the dates as numbers similar to those examples above.) However, if I try to use the "?" approach, I get errors because BIRT is confused with the "?" trying to link the master and detail tables.

What I think I can do, but haven't figured out how, is to use the report variables in the SQL query for the data set. (In other words, I would create a query parameter and link it to the report parameter, and use the report parameter in the SQL query that selects the detail records.)

I tried creating a report variable (RPT_Begin_Date) and assigning it the default value ('77405'). I then created a data set parameter (string type) for the detail table (PRM_Report_Begin_Date) and assigned its default value to be the RPT_Begin_Date variable. When I try to run the report, I get an error stating that the "table Cannot set the string value (77405) to parameter 2" ... "Cannot set prepared Statement parameter string value. SQL error #1: The index 2 is out of range."...

I get that something is not right between the report variable and the report parameter. But I went back and changed both to integer values and got the same result. I'm missing something easy - any help on what it is would be greatly appreciated.

TIA,
Re: Include report variable in Data set SQL query in detail table [message #1020375 is a reply to message #1020310] Mon, 18 March 2013 03:06 Go to previous messageGo to next message
Sean Lewis is currently offline Sean LewisFriend
Messages: 8
Registered: February 2013
Junior Member
You might have luck using the beforeOpen script for the dataset and parsing it with:

this.queryText = this.queryText.replace("mydate", params["mydate"].value);


is for a report parameter named mydate. change it to a java variable or whatever you're using. in the CALL query for the dataset just use the 'mydate' and it'll replace the mydate text with whatever you defined.
Re: Include report variable in Data set SQL query in detail table [message #1022389 is a reply to message #1020375] Thu, 21 March 2013 19:27 Go to previous message
Jeff Stouse is currently offline Jeff StouseFriend
Messages: 13
Registered: May 2012
Junior Member
Thanks for the reply, however, I am not familiar with those concepts - do I have to use scripts to accomplish this?

Where would I go for an example that is similar to the situation I am facing? Is there any one that I can download and look at?

TIA
Previous Topic:Error in Accessing Reports Using Servlet
Next Topic:Layered PDF output with BIRT
Goto Forum:
  


Current Time: Sat Dec 20 01:08:26 GMT 2014

Powered by FUDForum. Page generated in 0.01910 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software