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

Multiple columns of detail data

jmasterx

Here is my situation. 
 
I am working on a report where the SQL data is like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Col1 Col2 Col3 Col4 Costs Stuff
Tool Pencil CategoryA 50.00
Tool Pencil CategoryB 60.00
Tool Crayon CategoryA 20.00
Tool Crayon CategoryB 30.00
Piece Plywood CategoryA 40.00
...</pre>
 
The idea is that in BIRT I will group on Col1, but then I need to show the data of CategoryA and CategoryB for each one, but I need to show it horizontally. 
 
Example: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Type Name CatA CatB
Tool
Pencil
Costs 50.00 60.00
Stuff 16.00 22.00
...
Crayon 20.00 30.00
Costs 80.00 90.00
Stuff 96.00 20.00
...
Piece
...</pre>
 
What would be the best way to do this with BIRT? Ideally with 1 dataset. 
 
 
I looked into crosstabs, it looks like this is the right way, but I'm not sure how. 
 
Thanks

Find more posts tagged with

Comments

Hans_vd

Hi jmasterx, 
 
Write your query like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT col1,
col2,
col3,
'costs' AS type_dimension,
costs AS measure
FROM hdo_tab
UNION ALL
SELECT col1,
col2,
col3,
'stuff' AS type_dimension,
stuff AS measure
FROM hdo_tab</pre>
 
You can now create a cube with col1, col2 and typ_dimension for the row dimensions, col3 for the column dimension and measure for the numerical data. 
 
With such a cube you can create a crosstab that looks like what you want.

jmasterx

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110884" data-time="1351066441" data-date="24 October 2012 - 01:14 AM">
Hi jmasterx, 
 
Write your query like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT col1,
col2,
col3,
'costs' AS type_dimension,
costs AS measure
FROM hdo_tab
UNION ALL
SELECT col1,
col2,
col3,
'stuff' AS type_dimension,
stuff AS measure
FROM hdo_tab</pre>
 
You can now create a cube with col1, col2 and typ_dimension for the row dimensions, col3 for the column dimension and measure for the numerical data. 
 
With such a cube you can create a crosstab that looks like what you want. </blockquote>
 
 
Thanks 
 
Unfortunately what I am working on is a bit more complex and I'm not sure how to get the side sums and the summary sums. 
 
Here is 1 full 'row' of what I'm looking for. 
<img src='http://i.stack.imgur.com/7etng.png' alt='Posted Image' class='bbc_img' /> 
 
Any suggestions on how this could be done in a data cube? 
 
Thank you

Hans_vd

Are these a fixed number of columns?
Or could there be 4B, 5A... whatever... depending on your data selection?

jmasterx

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="110895" data-time="1351079771" data-date="24 October 2012 - 04:56 AM">
Are these a fixed number of columns? 
Or could there be 4B, 5A... whatever... depending on your data selection? </blockquote>
 
The columns come from the database under the field Delivery. As far as I know there is only up to 4B. If there is only a fixed solution that would do, but ideally, I would like it to be dynamic in case I ever add 5A.

MariKoux

Hallo! 
 
I think that i have a similar sitution with jmasterx but in a more simple way. 
 
I have already created a dataset that gives me the data that i need grouped and ordered. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Col1 Col2 Total
x a 1
x b 2
x c 1
x d 4
y a 34
y d 2
z b 22

</pre>
 
In the report i would like my data to appear in table like that (Each type of Col1 only once): 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Col1 Col2 Total
x a 1
b 2
c 1
d 4
y a 34
d 2
z b 22
</pre>
 
How can i do that??? 
 
Thank you!!!

Hans_vd

<blockquote class='ipsBlockquote' data-author="'jmasterx'" data-cid="110898" data-time="1351086020" data-date="24 October 2012 - 06:40 AM">
The columns come from the database under the field Delivery. As far as I know there is only up to 4B. If there is only a fixed solution that would do, but ideally, I would like it to be dynamic in case I ever add 5A. </blockquote>
 
Apart from the two most right columns, as far as I can see, it should be possible to do it in a crosstab. 
You would have 3 dimensions and 6 measures. 
When you select the crosstab, you can add totals on the Row area tab and on the Column area tab in the property editor. You can create totals on any dimension.

Hans_vd

<blockquote class='ipsBlockquote' data-author="'MariKoux'" data-cid="110951" data-time="1351177416" data-date="25 October 2012 - 08:03 AM">
Hallo! 
 
I think that i have a similar sitution with jmasterx but in a more simple way. 
 
I have already created a dataset that gives me the data that i need grouped and ordered. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Col1 Col2 Total
x a 1
x b 2
x c 1
x d 4
y a 34
y d 2
z b 22

</pre>
 
In the report i would like my data to appear in table like that (Each type of Col1 only once): 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Col1 Col2 Total
x a 1
b 2
c 1
d 4
y a 34
d 2
z b 22
</pre>
 
How can i do that??? 
 
Thank you!!! </blockquote>
 
 
This is something different. 
If you create a grouping on Col1, create a group header line in the table and then use the drop property to put the Col1 value on the first detail row within the group, you have what you want. 
 
Read more on the drop property <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/12/13/birt-drop-group-header-property-and-table-border-lines/'>here</a>.