|Include report variable in Data set SQL query in detail table [message #1020310]
||Sun, 17 March 2013 22:18
| Jeff Stouse
Registered: May 2012
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.
Powered by FUDForum
. Page generated in 0.15051 seconds