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

Show Group header for all distinct elements of a column even if 0 data rows exist

George Xry

Hello everyone!
 
I am attempting to design a report that has a grouping on one of the columns. My dataset has both parameters and filters that minimize the size of the result set. In that result I am grouping on a column called "status" that can always have values A B and C.
 
When I produce the report, depending on the parameters i might have values in all sets or not.
So sometimes my report produces result as
A
(5 data rows)
B
(2 data rows)
C
(3 data rows)
 
and other time such as :
 
A
(2 data rows)
C
(2 data rows)
 
How can I force birt to always keep the same outline (like below) even if there are no data rows to be shown?
 
 
A
(2 data rows)
B
(0 data rows)
C
(2 data rows)

Find more posts tagged with

Comments

pricher

Hi,
 
If you're not able to perform a left outer join in your data set to force the presence of a Status for which there is no data, you may be able to do it in a BIRT report using a Joint Data Set.
 
In the attached example, the Main Data Set consists of 3 columns: Group, Item, Amount. The data set is filtered on Amount using the pAmount parameter. If pAmount is greater than 1500, no data for Group B will be output. 
 
I have also created a Scripted Data Set of one column, Group, that outputs 3 rows: A, B and C.
 
The Joint Data Set creates a Left Outer Join on column Group. 
 
If you run the report with pAmount less than or equal to 1500, all groups will have data. Otherwise, group B will be shown but with no data underneath.
 
Hope this helps,
 
P.

George Xry

Thank you for your response it was very helpful to create another purspective to the problem.
 
I have been looking through it and managed using outer join to include in my data set the missing status records, however due to the fact that the birt report has filters, eventually the records are filtered out.
 
In SQL I can solve this by filtering in a nested statement
 
select * from
 ( select * 
from TABLE_A 
where columnA = 5
 ) 
as A 
right outer join table b on a.status = b.status
 
Is there a way (without joint datasets) to filter as such within birt?

pricher

Hi George,
 
Joint datasets are mainly used when you have 2 or more data sources and you cannot have the join performed by the database. For example, Table_A may be in MySQL and Table_B in a flat file or SQLServer. In that case, the join must be done by the BIRT engine through a Joint Data Set. If Table_A and Table_B are in the same database, you should not use a Joint Data Set (databases are a lot more performing at joining data than the BIRT engine...) In that case, write the SQL including the join clause. Any filtering you do in your SQL will be passed to the database engine.
 
Regards,
 
P.