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

Using output of one dataset in query of another

lfreeman

Hi,

My report design has two datasets.

DataSet #1: Querys a table that is actually a list of the dbs other tables and returns the name with the most recent date in it's name. ie: table_2012_03_04

DataSet #2: I want to use the value returned from DataSet 1 as the table name in my Data Set #2.

I've created a report parameter - "tablename"- , with the idea of using the following in the beforeOpen of DataSet #2:

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

But it's only using the parameter's default value. I can't seem to link the result of DS#1 to the parameter. How do I do this, and do I need to do something to set the order in which the DataSets are processed?

Any help would be appreicated.

Thanks.

Find more posts tagged with

Comments

RichT

Hi, 
 
I think this might work for you but it will depend on the order of the data sets (executes alphabetically or by Element ID or ???- not sure) - In the first dataSet's onFetch event add 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>params["tablename"].value = row["COL_NAME"];
</pre>
 
where COL_NAME = the name of the column from your first data set query. Keep in mind if you have more than one row returned in the first data set then the param will continue to update giving you the last record's COL_NAME. 
 
Hope that helps, 
Rich

lfreeman

Hi Rich,

Thanks for the advice. Tried this, and it does change the parameter value to what I want, but not until after Dataset #2 has already executed with the parameters default value. Dataset #2 is executing before Dataset #1.

Looks like my problem is that the datasets are executing in the wrong order. I'm not sure how to change this.

(Maybe something in Datasets #2 beforeOpen to execute DS #1, or something in DS #1 beforeClose to (re)execute DS #2? But I wouldn't know how to do this.)

Thanks,

Luke

RichT

Hi Luke,

My apologies - I didn't fully test this idea out before I submitted a response. I attached an example recreating a scenario similar to yours (I think) using the models db. Originally I had a data set that was only used to set the parameter value but the onFetch wasn't running and I didn't understand why. It wasn't until I put a table in the report that the onFetch began executing. Additionally I discovered that you have to place the table above the items using the second dataset.

In the example attached I have a default parameter value of 121 and a data set that assigns a value of 141. Take a look and let me know if it works. If it does, you just have to hide the table producing your parameter data.

BIRT version 2.6

Hope it helps,
Rich

lfreeman

Works!

Thanks for the help Rich!

pravar

Hi,
This is working for single value i.e where userid='a'
how to solve this in in condition i.e where userid in ('a','b','c');

Please help me out thanks in advance

RichT

Hi,

Attached is an example of how to use multiple values from the first data set into the WHERE condition of the second data set query. It's probably not the most elegant but it does seem to work. Is this what you had in mind?

Hope it helps,
Rich

pravar

Hi Rich,
I tried with the given example but its not solve my issue :
I had an report parameter.
Eg(1,2,3)
the selected values from the report parameter will pass to the dataset1.
Eg query in dataset1 (select name from tableA where id in(1,2,3)
From the dataset1 we will get multiple values as ouput.
The Output of dataset1 has to use in filter for dataset2 with in condition
Eg: Dataset2 query (select values from tableB where name in ('output of dataset1')

Note : here the datset1 and dataset2 are from different Databases one from oracle and the other is from Mysql

Thanks

Abhijit Mandal

Hello,
 
I have a situation where i need to fetch column value from dataset say datasetA do some modification to the data
and push to datasetB,so i tried the sample here <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_id=6700' title="Download attachment">"new_report_4.rptdesign</a>" given here to pass the value from one dataset
to other dataset ,but it is not passing the value with "NewParameter" basically the default value setted in NewParameter is getting
passed to datasetB ,not the value setted from datasetA.