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

JDBC DataSource - DetailRow as DetailColumn

Miss_Anonymus

Hello,
 
I'm new in the BIRT World, so I don't have much experience. I hope you can help me with my problem.
 
I have created a JDBC DataSource on my Report (I'm using the drag and drop plug in by eclipse) and I have a Database connected with one table. It looks like that:
 
user function description
Julia cooking very fine
Julia washing no good idea
Julia do the dishes ok
Peter cooking fine
Peter washing very fine
Peter do the dishes ok
Laura cooking the worst
Laura washing super
Laura do the dishes no good idea
 
Now I want to create a table in BIRT which has to look like that:
 
user cooking washing do the dishes
Julia very fine no good idea ok
Peter fine very fine ok
Laura the worst super no good idea
 
 
So, that the names are on the left and the functions on the top, under which the descriptions are listed.
 
I haven't any idea how to do that. When I create a DataSet in BIRT with
<pre class="_prettyXprint">
Select user, function, description
from table;
</pre>
I only get the listing in the first picture. When I drag an drop the DataSet into the white area, I get a table where the three attributes are listed. There are only "DetailRows", but the function should be a "DetailColumn", how can I do that? And how can I then list the descriptions below?
 
Do I have to make two DataSets, or a JavaScript Function or something else? I'm perplexed.
 
Thank you so much for your help!

Find more posts tagged with

Comments

pricher

Hi,
 
Two ways to do this:
 
1. You use a cross-tab item in your report to let BIRT pivot the data. I have attached an example based on your data, but using a flat file;
 
2. Pivot the data in SQL. Many commercial have proprietary SQL extensions to pivot row data into column; I'll let you find them based on the database your are using. You can also use this SQL statement to pivot your sample data; it should work everywhere:
 
select t1.USER 
, min(t1.COOKING) as cooking 
, min(t1.WASHING) as washing 
, min(t1.DO_THE_DISHES) as do_the_dishes 
from 
(select user 
, case when function = 'cooking' then description end as "cooking" 
, case when function = 'washing' then description end as "washing" 
, case when function = 'do the dishes' then description end as "do_the_dishes" 
from test) t1 
group by t1.USER
 
Once pivoted, you can use a simple table to display the results in BIRT
 
Hope this helps,
 
P.

Miss_Anonymus

Thank you so much for your help!
 
I used your rtpdesign File as example and adapted it to my JDBC Connection...it works fine! The crosstable was the exact result I wanted.

Miss_Anonymus

Just one more question... how can I insert a default message like "no description", when in field description nothing is found? Now there is just a white, blank space.I tried clicking on the measure summary cell and wrote at the alternate text option something...but doesen't work.
 
What can i do?

Miss_Anonymus

....And how can I adapt the word wrap for the column headers, the functions?
There is an automatic break after a white space, that's ok, but I want to break it after a letter, when the word is too long, for example:
 
Hello this is a 
veryveryveryveryvery long <-- the italic part is cut of
 
but I want
 
Hello this is a
veryveryvery
veryvery long
 
 
Just cut of at a letter where it gets too long. 
Which approach is that?
 
I hope you can help me.

pricher

Hi,
 
The first question is easy: you can set a string that will appear in a cell if the value is null. You can find this property in the CrossTab Property Editor under Empty Rows/Columns as shown in this screen shot:

Miss_Anonymus

Thank you much ! I finally got it...