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

Use the result from a data set in the other data sets

Olof

Hello

I am working on a report where the customer wants a parameter where they can type "1%, 3.3%". This means they want to see statistics for "1.1 Category A", "1.2 Category B", "3.3.1 Category G", "3.3.2 Category..." and so on.

The query to fetch the matching categories is used in several places and this makes the report very slow.

What I would like is to run a single dataset first, then use the result from there in all the other data set queries. So I get
" ... WHERE t.category IN ('1.1 Category A', '1.2 Category B', ...) "
instead of
" ... WHERE t.category IN ( SELECT DISTINCT category FROM t1 INNER JOIN t2 ON t1.id= t2.id AND t2.valid = 1 WHERE category LIKE '1%' OR category '3.3%' ) "

Is something like this possible in birt?

Find more posts tagged with

Comments

mwilliams

Hi Olof,

What version of BIRT are you using? Newer versions of BIRT should have caching to help with this issue if you're using the same query in different items within your report.

thuston

We did this for an internal report. You have one dataSet populate a String and then use that in the BeforeOpen of other dataSets to update the queryText. 
 
rptdesign initialize 
<pre class='_prettyXprint _lang-auto _linenums:0'>oppIdList=""</pre>
first DataSet OnFetch (also requires this query be called by a Grid or Table at the beginning of your layout, so it executes first) 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (oppIdList == "") {
oppIdList = "'" + row["id"] + "'";
}
else {
oppIdList = oppIdList + ",'" + row["id"] + "'";
}</pre>
subsequent DataSets BeforeOpen 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (oppIdList != null && oppIdList != "")
this.queryText = new Packages.java.lang.String(this.queryText).replaceAll("'dummyValue'", oppIdList);
</pre>
The query before being changed looks like this: 
" ... WHERE t.category IN ('dummyvalue') "

Olof

I'm using v 2.2.2 for this customer.

Thanks thuston, this looks promising.
Is it always the layout order that specifies in what order data-sets are executed?

thuston

Yes, the DataSets execute as they are needed by the report layout. Unless you use some DataSets for dynamic parameter population. In that case those DataSets would fire first.

For any layout execution you don't want to see, just set the Visibility to hide for all outputs.