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

Cross table - Grouping does not work

Miss_Anonymus

Hello,
 
I've done a report in Birt 4.4 with the follwing SQL-Statement as DataSet:
<pre class="_prettyXprint _lang-">
SELECT ID, name, year,

CASE
WHEN year = 0 OR year IS NULL THEN 'no'
WHEN year != 0 THEN 'yes'
END AS 'was there?'

FROM table;

</pre>
My result is:
 
ID name date was there?
1 Lisa 2013 yes
2 Mary 2013 yes
3 Lisa 2014 yes
4 Lisa 2015 yes
5 Tom 2015 no
6 Mary 2014 no
 
 
My expected output in my report should be:
 
 
name 2013 2014 2015
Lisa yes yes yes
Mary yes no --
Tom -- -- no 
 
 
I already tried to solve it with an cross table, but I have no idea how to bring the data in it, so that it aggregates as shown above.
 
What can I do? Thanks already for your help!

Find more posts tagged with

Comments

pricher

Hi,
 
An easy solution would be to create the "Was There" field as a integer instead of a string. For example, use 1 for Yes and 0 for No. In your data cube, you can create a Summary Field on "Was There" and use FIRST as the aggregation method. Then, in your crosstab, you can Map the Summary Field to display Yes when the value is 1, and No when the value is 0. You can also replace null values with "--" by entering this string in For Empty Cell field of the Empty Rows/Columns of the crosstab properties.
 
I have attached an example that uses an Excel file as a data source. Simply unzip both files in your BIRT Project.
 
Hope this helps,
 
P.

Hans_vd

As far as I know the FIRST aggregation can handle a string column, so no need to make the "Was There" field an integer