Eclipse Community Forums: BIRT » Include report variable in Data set SQL query in detail table

Home » Archived » BIRT » Include report variable in Data set SQL query in detail table(Using Report variables in a Data Set SQL query for detail table)

Show: Today's Messages :: Show Polls :: Message Navigator

Include report variable in Data set SQL query in detail table [message #1020310]

Sun, 17 March 2013 22:18

Jeff Stouse

Messages: 28
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,

Report message to a moderator

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

Sean Lewis

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.

Report message to a moderator

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

Jeff Stouse

Messages: 28
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 20:18:52 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter