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

Setting up new Data Set

Felix15

Hello,
I select data with a sql query from a remote database.

The problem is that I need to run procedures on the data before it is displayed which is not supported by sql
and BIRT functions.

So my thought is that I create a new DataSet with the same fields and copy the data from the original
DataSet in the correct way.
Afterwards I want to bind the new DataSet to the a table in the report.

My question is, how can I copy the old DataSet and add the data from the old one field by field in a loop.
Which event should I use for this?

Thanks for the help,
best regards!

Find more posts tagged with

Comments

mwilliams

Hi Felix15,

You can create and use your own functions within BIRT to do the procedures on your data before displaying. BIRT uses java/javascript, so anything you can do within those, you can do with BIRT. You could also connect to your database using a scripted dataSource and do your computations in there.

Felix15

Hi mwilliams,
thanks for the reply, but I can't get it to run.
Let's say I connect to a database and make a aql statement. Afterwards I store the desired data in an array.

How can I write the data to DataSet before I display the report?

I thought I name the new DataSet as the one I use during the design phase and use the same field names so that I don't have to bind the DataSet to my table in code.

Regards, Felix

mwilliams

Felix,

Can you describe the computations/changes you're trying to do? If you're using external functions to manipulate your data, you should be able to bring them into BIRT and use them in your dataSet editor to create computed columns to use in your display. Maybe I'm not quite understanding the issue.

Felix15

Hello,
I have two tables with an 1:n relationship.

Table 1 (System) :
Serial number|Attributes

Table 2 (Machine):
Serial Number (Foreign Key)| Machine Number| Attributes

So on serial number can have n machines connected to it. This is just an example. I want to make a report of the type
Serial number | machine 1 | machine 2| .. |machine n

Therefore I have to loop over the entries of the second table and find out for every serial number which machine is connected to it. this can't be done in native sql as for as i know.

My plan was to make a DataSet with the Sql query "Select serial number as S, machine number as M from table 2"

Then loop over the data and Connect the machine numbers for every system in a string and write one line for every system in a DataSet which has the same fields.
Example:
serial number | machine number
Sys1 | mach1,mach2,mach3

I was thinking that I wouldn't have to change anything at the table, since i am using a dataset with the same fieldnames. My problem is how to write the data in a new DataSet.

mwilliams

So, you just want each serial number listed with the different machines listed horizontally next to it? Like:

Serial1 | Machine1 Machine2 Machine3 etc...
Serial2 | Machine2 Machine4 Machine5 etc...
etc...

from data that looks like:

Serial1 | Machine1
Serial1 | Machine2
Serial1 | Machine3
Serial2 | Machine2
etc...

If so, you could group by serial number, then include an aggregation in the header that concatenates the machines together for the group. Then, just delete the detail line of the table. Let me know if I've got something wrong/confused.

Felix15

Hello Michael,
sorry for the late reply. This is exactly what I want to do...

Unfortunately I can't make it work. How do I concatenate the fields on an aggregation?

Thank for the help,
Felix

mwilliams

Felix,

What is your BIRT version? I'll make a quick example using the sample database.

Felix15

Hello Michael,
I am using Birt 2.2.1

Thanks a lot for the help..

mwilliams

Felix,

There wasn't a concatenation aggregation function in 2.2.1, so I did the concatenation manually in the detail row script and placed the resulting value in the footer using a text box. Take a look and let me know if you have any questions.