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

Data Set with a dynamical amount of fields

Enrico

Hi there, 
 
I have a DataSet which does a 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT * FROM mytable</pre>
well quite simple. My problem is the amount of columns in this table could be different, every time the report runs. Believe me I tried to avoid this but there is no workaround for that so far. 
 
Finally there is a table which has at certain moment in time 8 columns and on another moment in time just 4 columns. 
 
The idea I have to fix it, goes like this. Lets assume one special column is always part of the table, lets say its the primary key column. Then create a DataSet with just one column but still doing a SELECT * FROM mytable, which means I get all columns anyway. Finally I do a loop inside the FETCH-block running over alle fetched columns. 
 
The code could look like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var amountColumns = maximoDataSet.length;

for(i=0; i<amountColumns; i++){
row["PKcolumn"] += ","+maximoDataSet.getString(i);
}</pre>
 
Here are my questions: 
 
1.) Is my solution possible to implement or are there any basical constrains/restrictions which cause it to fail? Something like you can't restrict the dataSet just to one column, but still getting all columns... 
 
2.) Assuming it would be possible, could it be done with a non scripted data source, but still with the LOOP inside the FETCH-block? 
 
BTW, the reason I wasn't able to try it till now is, I haven't found a JAVA DOC for all the Objects used in BIRT. Could you provide me a link to it please? 
 
Greetings and THX in advance :unsure: 
 
 
I am using BIRT 2.3.2!

Find more posts tagged with

Comments

mwilliams

So, you don't know what the columns will be in the table. Any given time you run the report, they could all be different? So, the main issue is being able to display the table with the correct bindings? Is this correct?

Here's a link to the documentation:

http://www.birt-exchange.org/org/resources/documentation/

Enrico

Hi Michael, 
 
first of all thx for the quick reply. 
 
Thats exactly my problem, I don't know what columns the table could have when the report is beeing executed. 
 
Well, binding the results to a a certain table is the next problem on my list. But first of all I wanted to know if its possible to "cheat" by defining a single column in the dataSet, but still accessing columns of the table. So you think it should be possible, right? 
 
Regarding the binding of the result I was thinking about a dynamical solution. I want to create a table which columns a generated on demand. To do that, I found that code on the internet. <a class='bbc_url' href='http://digiassn.blogspot.com/2007/11/birt-dynamic-adding-tables-and-columns.html'>Link</a> 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>//get a reference to the ElementFactory
elementFactory = reportContext.getReportRunnable().designHandle.getElementFactory();

//create a new table with 3 columns
dynamicTable = elementFactory.newTableItem("myNewTable", 3);
dynamicTable.setWidth("100%");

//set reference to the first detail row
myNewRow = dynamicTable.getDetail().get(0);

//get references to the first 3 cells
firstCell = myNewRow.getCells().get(0);
secondCell = myNewRow.getCells().get(1);
thirdCell = myNewRow.getCells().get(2);

//create the cells and add
label = elementFactory.newLabel("firstCellLabel");
label.setText("First Cell");
firstCell.getContent().add(label);

label = elementFactory.newLabel("secondCellLabel");
label.setText("Second Cell");
secondCell.getContent().add(label);

label = elementFactory.newLabel("thirdCellLabel");
label.setText("Third Cell");
thirdCell.getContent().add(label);

//although it is not in the autocomplete, getBody is a method of the ReportDesignHandle class
reportContext.getReportRunnable().designHandle.getBody().add(dynamicTable);

//now, to demonstrate, get a reference to the table fromt he report design, this automatically cast
//to a TableHandel type
dynamicTable = reportContext.getReportRunnable().designHandle.findElement("myNewTable");

//now insert the column to the right of the indicated column position. Column number is 1 based, not 0 based
dynamicTable.insertColumn(3, 1);

//get the first detail row and the 4th column. This is 0 based
myNewRow = dynamicTable.getDetail().get(0);
forthCell = myNewRow.getCells().get(3);

//create a new label
label = elementFactory.newLabel("forthCell");
label.setText("dynamic cell");
forthCell.getContent().add(label);</pre>
 
Whats your opinion about my suggestion? 
 
Greetings 
 
I will take a look on the documentation link - thx! ^_^

mwilliams

I'm not totally sure what you mean by defining only one column in the dataSet. BIRT will automatically define columns for the dataSet based on your returned fields from the query. At least if you're using the designer. Are you talking about making a query that returns only one column? Let me know.

As for creating a dynamic table in the beforeFactory method, it will be necessary to know the bindings that you'll have to create the table. I'm not exactly sure how to handle this off the top of my head. I'll have to take a look into creating a table when you don't know how many columns there will be or what the bindings will be. Let me know more about your one column idea for sure.

Enrico

Ok, so if you are using a scripted dataSource, the columns in the dataSet are not genereated automatically. You have to define them manually, right?

The idea is to define one column which is the PK of the table and always there. But the query is still a SELECT * FROM table; which gives me access to all columns of the table, right?

:unsure:

thuston

The columns in your result set have to be stored in variables.

Having a query return an unknown amount of columns is a terrible idea.
However, the solution would be to create more DataSet columns than you could ever need and then you can dynamically bind your actual result set to each column.
Obviously you will have issues with dataTypes. You'll have to cast everything as a String.

Why are you doing this?
Why not just create a query that returns all the data and then you can decide whether or not to use it?

Enrico

Maybe I expressed myself not clearly enough... <_< 
 
Actually I want to do exactly this: 
 
<blockquote class='ipsBlockquote' >Why not just create a query that returns all the data and then you can decide whether or not to use it?</blockquote>
 
Is it possible to do a SELECT * FROM table without beeing able to know how many colums the query returns and to decide onFetch which columns to use or not? 
 
Lets assume I will be able to make sure alle columns within the table are having the same dataType...

Hans_vd

Actually I want to do exactly this: 
<blockquote class='ipsBlockquote' >
Why not just create a query that returns all the data and then you can decide whether or not to use it? </blockquote>
 
Well, you can't. Because you never know what "all the data" is, as the definition of your database table may have changed. 
 
<blockquote class='ipsBlockquote' >
Having a query return an unknown amount of columns is a terrible idea. </blockquote>
Thuston is right. 
And having a database table that is constantly changing it's definition is even worse. 
 
 
Now, can you do something like this: 
- Create a new table in your database with, let's say, 100 columns 
- Create a procedure that, based on your "dynamic" table, fills the right number of columns in this 100 column table 
- Build a dataset that selects all these columns 
- Create a table on the report with all these dataset columns 
- Use an expression in the visibility property in every column to hide empty columns 
 
Regards 
Hans

thuston

I agree with Hans.
You can do a Select * from Table, but it will break as soon as your table returns fewer columns or the columns are reordered in such a way that the datatypes don't match.