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

How to insert an extra row into the results of query?

millvall

Is there a way to insert an extra row into the results of a query?

REASON: I have a report parameter (List, drop down type) which is populated by a query. I want to insert a row to allow users to pick ALL accounts. eg: Parameter Text: "All Accounts" Parameter Value "%"

THINGS I?VE TRIED
- In the OnFetch event I thought I could alter column values as each row went thru.
If I could, I might be able to buffer the rows in an array and insert a new row, but no success.

dataSetRow["ACCTEXT"] = "All Accounts";
row["ACCTEXT"] = "All Accounts";

- THOUGHTS
- I could use a scripted data set to read an RDB table but that seems really clumsy.

Any suggestions welcome.
Thanks
Milton.
(OS BIRT 2.6.2)

Find more posts tagged with

Comments

Hans_vd

Hi,

You can add a "union all" to your SQL statement.

Regards
Hans

millvall

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="80493" data-time="1311530986" data-date="24 July 2011 - 11:09 AM">
Hi, 
 
You can add a "union all" to your SQL statement. 
 
Regards 
Hans </blockquote>
 
Hi Hans, 
Could you explain a little more. Below is the query used to populate the parameter. 
Text: CUSTOMERNAME 
Value: CUSTOMERNUMBER 
I want to insert 
Text: ALL CUSTOMERS 
Value: % 
How would I use a UNION ALL to do this. 
 
Many Thanks 
Milton. 
 
 
select CUSTOMERNAME, CUSTOMERNUMBER 
from CUSTOMERS 
order by CUSTOMERNAME

Hans_vd

The dataset for the report parameter would be like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select CUSTOMERNAME, CUSTOMERNUMBER
from CUSTOMERS
order by CUSTOMERNAME
union all
select 'All accounts' as customername, -1 customernumber
from dual</pre>
 
 
In the dataset that is used in the report you have to add an extra dataset parameter that is also bound to the report parameter and in the query you have to do something like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>select ...
from table
where (field = ? or ? = -1)</pre>
 
 
Hope this helps 
Hans

millvall

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="80612" data-time="1311704249" data-date="26 July 2011 - 11:17 AM">
The dataset for the report parameter would be like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select CUSTOMERNAME, CUSTOMERNUMBER
from CUSTOMERS
order by CUSTOMERNAME
union all
select 'All accounts' as customername, -1 customernumber
from dual</pre>
 
 
In the dataset that is used in the report you have to add an extra dataset parameter that is also bound to the report parameter and in the query you have to do something like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>select ...
from table
where (field = ? or ? = -1)</pre>
 
 
Hope this helps 
Hans </blockquote>
 
============================= 
Hans, 
Thank You. It works great. My query below. 
I inserted a SPACE in front of ' All Customers' to force it to the top of the list. 
Then I added a few lines to Validate() on the parameter to convert -1 to % and all works great. 
Thanks again, Milt. 
 
select 
CUSTOMERNAME, 
CUSTOMERNUMBER 
from 
CUSTOMERS 
UNION 
select 
' All Customers' AS CUSTOMERNAME, 
-1 AS CUSTOMERNUMBER 
FROM CUSTOMERS