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

Add columns to a data set from a globalPersistentVariable

ArK

Hi,
 
I have a JDBC data set and in the DB there's a table that contains 'extensions' for another table. I'm currently collecting these extensions to a globalPersistentVariable.
Is it possible to iterate this variable after it has been fetched and then add columns to another data set based on these values?
 
To clarify: 
Let's say there's a table containing cars that are unique.
There's also an extension table that contains owners and dates for that car (identified by car_id)
 
I want to add these owners to the car data set, but I can't join them as multiple owners would lead to multiple rows for the same car in the data set, right? 
Is it possible to collect the owner data (lets say only the names) and then add it as columns for a single car or is there a more simple / better way of implementing this?
 
Thanks,
Arto

Find more posts tagged with

Comments

pricher

Hi,
 
You can use the CONCATENATE function in an aggregation component to add together values from multiple rows. Let's say you have the following join:
 
<div>
<pre class="_prettyXprint _lang-sql">
select c.customername
, o.ordernumber
from customers c inner join orders o
on c.customernumber = o.customernumber
</pre>
In a table using this query, group by CUSTOMERNAME, then create an aggregation using the following definition:

ArK

Thanks for your reply!
 
Unfortunately I might have over simplified my problem as I was only trying to convey the concept. The actual values I'm dealing with are numeric and I need to sum them in a crosstab. The column dimension contains certain categories and the rows would have numeric values to be summed. The rows, though, don't hold these values in the DB but they need to be added to the data set via the extension table.
There are a few different extension tables and some of them only show WHERE the values can be found...the values are always in an extension table but one might point to another. There are lots of dynamically added values here so I figured that collecting them in a variable and then adding them to correct data sets might be the way to go.
 
-Arto

pricher

Hi,
 
You can use the onFetch method of the data set to add or modify the value of a column. The syntax is simply:
<pre class="_prettyXprint _lang-js">
row.myColumn = <value>
</pre>
where myColumn is either a column in your query or a computed column added to your data sets.
 
The onFetch method is called for each row returned by the query.
 
Hope this helps,
 
P.

ArK

Hi,
 
I also thought of something like this, but how can I make sure that the variable (extensions) are fetched before I reference them in the other data set? 
 
Is there a way I can define the order in which the data sets are fetched? I tried adding a bound element to the top of the report, but the other data set is fetched with parameter values so I'm guessing it will be fetched first anyway?
 
-Arto

pricher

Hi,
 
As you have found out, the order in which the data sets are executed is based on when their results are needed in the report design.
 
P.