Using the output of one dataset as parameters to another dataset in a chain
<p>In BIRT, how can we use the result set of one dataset as parameters to another dataset? I'm not refering to dynamic report parameters (where the user selects between results from a query). I want to chain together datasets from different data sources. How can I use the output of a MySQL dataset to parameterize a query for a PostgreSQL dataset without resorting to creating a BIRT joint dataset? (see below)</p>
<p> </p>
<div>For example:</div>
<div> </div>
<div>Suppose that I have a PostgreSQL table with a very large number of rows but I know that I will only be interested in a few rows per report, depending on the outcome of a MySQL query. How can I use the output of my MySQL dataset to parameterize a query for a PostgreSQL dataset without resorting to creating a BIRT joint dataset (which would involve loading many unnecessary rows from the PostgreSQL datasource)? What if I want to use the result from my second dataset in a query for a third dataset (and so on)?</div>
<p> </p>
<div>For example:</div>
<div> </div>
<div>Suppose that I have a PostgreSQL table with a very large number of rows but I know that I will only be interested in a few rows per report, depending on the outcome of a MySQL query. How can I use the output of my MySQL dataset to parameterize a query for a PostgreSQL dataset without resorting to creating a BIRT joint dataset (which would involve loading many unnecessary rows from the PostgreSQL datasource)? What if I want to use the result from my second dataset in a query for a third dataset (and so on)?</div>
0
Comments
-
<p>Hi,</p>
<p> </p>
<p>The attached design illustrates one possible solution where you can store the result of your first query in the <em>onFetch </em>event of the first Data Set in a report variable.</p>
<p> </p>
<p>Then, in the <em>beforeOpen </em>event of the second Data Set, you can use that report variable and update the SQL accordingly.</p>
<p> </p>
<p>Please note that the example only has one Data Source, but this technique can be applied to multiple (and different) Data Sources.</p>
<p> </p>
<p> </p>
Warning No formatter is installed for the format ipb0 -
<p>The report design that you sent worked. I noticed that the example rptdesign uses a report variable to share information between datasets. This page (<a data-ipb='nomediaparse' href='http://www.eclipse.org/birt/documentation/integrating/scripting.php'>http://www.eclipse.org/birt/documentation/integrating/scripting.php</a>) seems to advocate storing global variables in the reportContext. I've gotten both strategies to work but is there a reason to prefer one way over another? When should I use a report variable over reportContext.setPersistentGlobalVariable()? When would the reportContext.setPersistentGlobalVariable() be preferable?</p>0
-
<p>Hi,</p>
<p> </p>
<p>Typically, use a Persistent Global Variable when you need to share values between the Generation and Presentation phases. In your case, the two data sets are executed during the Generation phase; therefore, using a Report Variable is fine. However, if you wanted to store a value obtained during the Generation phase, say the average sales price of all your orders, and use it during the Presentation phase to create a dynamic marker line in a chart, you would need to store it in the Report Context. The rule of thumb is that if you script something in an onRender method, you will need to use a Report Context variable; otherwise, you are good to go with a Report Variable.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
Warning No formatter is installed for the format ipb0 -
<blockquote class="ipsBlockquote" data-author="pricher" data-cid="130095" data-time="1407743391">
<div>
<p>Hi,</p>
<p> </p>
<p>Typically, use a Persistent Global Variable when you need to share values between the Generation and Presentation phases. In your case, the two data sets are executed during the Generation phase; therefore, using a Report Variable is fine. However, if you wanted to store a value obtained during the Generation phase, say the average sales price of all your orders, and use it during the Presentation phase to create a dynamic marker line in a chart, you would need to store it in the Report Context. The rule of thumb is that if you script something in an onRender method, you will need to use a Report Context variable; otherwise, you are good to go with a Report Variable.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Could you please share an example on this.<br><br>
I'm looking to execute a dataset during generation and use its output as the input parameter of the second dataset's query.</p>0 -
Clement Wong, good day)
please, can you provide your example one more time?0 -
Attaching the example here.
0 -
I'm trying to achieve the same and followed the attached report however it does not work. I have 2 datasets where the output of the first data set should be passed as a parameter to the second dataset. Hence in the first data set in OnFetch I passed the var data = row["column"]. In the second data set in beforeOpen I have mentioned like this.queryText = this.queryText.replace("whereid",vars["data"]); There is no error however I don't see the whereid being replace. Can someone help me with this.
0
Categories
- All Categories
- 118 Developer Announcements
- 52 Articles
- 150 General Questions
- 137 Services
- 56 OpenText Hackathon
- 35 Developer Tools
- 20.6K Analytics
- 4.2K AppWorks
- 9K Extended ECM
- 912 Cloud Fax and Notifications
- 82 Digital Asset Management
- 9.3K Documentum
- 30 eDOCS
- 175 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 6 XM Fax