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

Trying to pass to data set a Report Date from another data set

ayoung

I have two data sets - one is used to bring back the start and end date of the reporting period from an oracle table, and the other is used to select main report data (also from oracle).

I want to be able to pass the start and end date to the 2nd data set to restrict the data, ideally as part of the Select statement to help performance.
I have tried creating global variables to try and assign and pass the dates, and scripting parameters, but having absolutely no luck.
I also tried creating a gui paramter, that was hidden from the user, but can't manage to get a date to pass to this through scripting.
My scripting skills are pretty non-existent so i've just been trailing forums trying to figure it out.

Can anyone suggest a solution?

I imagine this is pretty basic functionality, but a step by step guide as to what i need to put and where would be great.

Find more posts tagged with

Comments

Hans_vd

Why going the difficulty of using two datasets?
Can't you create just one dataset that contains a query that joins the tables from the main dataset to the table that has the start and enddate in it?

Hans_vd

Another possibility might be that you create a table on the report that is linked to dataset that has the start and enddate in it (I suppose it will always return only one row). Within that table you add another table that is linked to the main dataset.

Good luck

KavithaD

Hi,

Create a Main Data set and map it to Main Table.Create Child Data Set and create Parameters,Give default value to newly created parameters say current date.Map child data set to child Table.In Child Table Binding Property edit the Data set Parameter binding, change the mapping from default value to required mapping with Main table.Hope this may help you.

Thank you,
Kavitha.

ayoung

Hi, Thanks for all the responses so far.

Kavitha - i tried your suggested method, but when i click the Dataset paramter Binding button, a new window pops up with no parameters present (even though one was created), and therefore does not let me edit/add anything here.

Hans_VD - the SQL query for returning the dates is quite a complex case statement, and involves changing data types. I think combining the queries will have an impact on performance.

The version of BIRT i'm using is the trial version of Pro designer (2.3.2). I should also highlight that the parameter is hidden, as i don't want the user to be prompted for this value.

KavithaD

Hi,

Have you created Parameter as shown in screen shot? Please check the screen shot.

Thank you,
Kavitha

ayoung

Ok, yes i was missing that step. I've now edited the data binding on the child table so that the From_dt parameter is set to the Start_Date coming from the child data set (the one that brings back the report dates).

How do i get the main table (or graph in this case) to filter the data based on these paramters now?

ayoung

I think i figured out the filtering - in the select data gui for the graph, i choose filter, then restricted data based on those two dates.

However if i refresh report or go into the data set that brings back the two report dates, i get an error message (pictured). The dates are of type timestamp, as are the 2 paramters i set up in the Dataset gui.

Hans_vd

<blockquote class='ipsBlockquote' >Hans_VD - the SQL query for returning the dates is quite a complex case statement, and involves changing data types. I think combining the queries will have an impact on performance. </blockquote>
 
I would be very surprised if this would have any impact on performance (the query that returns start and enddate does return 1 row, isn't it?). I don't see any reason for that. 
 
But I do understand that you don't like your main query to start looking too complex. To avoid that you can create a view in the database, being the query that selects start and enddate, and they you have to add only 1 line in the FROM of your main query. 
 
 
<blockquote class='ipsBlockquote' >However if i refresh report or go into the data set that brings back the two report dates, i get an error message (pictured). The dates are of type timestamp, as are the 2 paramters i set up in the Dataset gui</blockquote>
 
Seems like there's a conflict between Oracle datatypes and BIRT datatypes. 
Too make sure it all works like you want, you can do the datatype conversions yourself by having your first dataset returning string datatypes (in Oracle use "to_char(date_field, 'ddmmyyyy hh24:mi:ss')"), and make the input parameters of your main dataset also String parameters (in the query that goes with the main dataset you need to do "where date_field = to_date(?, 'ddmmyyyy hh24:mi:ss')") 
 
 
Hope this helps

KavithaD

Are you asking about Mapping data set to Main Table ??? or else create your sample report using classic model and share it.

If you have 2 Tables say Table2 inside Table1 then you can map data set1 to Table1 and data set2 to Table2.Edit Data set Parameter binding and map it with Table1 data set column.