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 groups using same data value

Elja

Hello.
 
I'm trying to figure out the best way to create a Birt report with several groups using the same data field as their source.
 
The idea is to count aggregate sums for several field values from DB and organize these values by different age groups (for example: how many customers are there of age 1-6). I'd like to count all the different report values by age groups like 0 years, 1-6 years, 7-14 years of age etc.
 
I tried creating several groups for different ages and one tested using several filters inside one group but it seems that using the same data field for several groups isn't that straight forward to accomplish. I've had also difficulties to find the answer from the net..
 
I'll attach a sample excel screen shot about the result I'm trying to acchieve.
 
Birt version is 4.5.0.
 
Thanks in advance, Elja

Find more posts tagged with

Comments

micajblock

Create a computed field for age group and then group on the computed field. Set the sort order of the group to be the age (so 15-18 will not be before 7-14)

Elja

Hello mblock and thanks for your advice.
 
I looked into the computed field -option before but couldn't quite get the hang of it.
 
So, could you help me a bit more with this approach:
 
* Is the computed column created in the main Data Set?
* What would be the Aggregation type for it?
* Where do I create the age -ranges? In Computed columns Expression with some kind of If -case formula?
 
- Elja

micajblock

Can you provide sample data as CSV and I can build a quick example. BTW what is the source data?

Elja

Hey mblock.
I'll try to produce a csv file out of my basic model of the report (I've mainly been trying to solve this issue).
 
We're using postgreSQL DB.
 
 -Elja

micajblock

I attached an example using Classic Models. Ideally you use a case statement in the query (as I did). Alternatively you can create a computed field on the data set (I added this column but I am not using it). The aggregate would be whatever you need it to be.
 
Hope this helps.

Elja

Hey mblock and sorry I didn't manage to produce the csv file during work day.
 
Thank you so much for the example report. I'll check both variations of the solution.
 
I have to admit I've got to dig deeper into computed field. I thought the aggregation was compulsory.
 
- Elja

Elja

OK now.
I was a bit unsure how the age group values would show in report (with the main group grouping on computed column age group).
 
But it seems to work really nicely. I think I'm able to create the report based on this approach (I'll try the computed version first).
 
Thanks a lot, once again and have a nice weekend.
 
- Elja

micajblock

You're very welcome.

Elja

Hei mblock, another question aroused..
 
I did some more testing with the computed field -approach.
 
It seems that while I get the grouping going OK (first column shows the age group), and the aggregate function counts the rows fine (second column), it still adds the "empty rows" in between the age groups.
 
I tried to suppress duplicates from column properties - Advanced for both columns, but it seems to work for only the first 2 age groups.
First row in excel shows "0" and second "1-6", but the third age group "7-14" is on the third excel sheet..
 
Any ideas what would be an effective way to compress the values? I don't need any data row info, just aggregate count of sum values based on the age groups..
 
- Elja

micajblock

There is the easy way and the right way. Easy way to to delete the detail. Simpy select the whole detail row by clicking on the place holder on the left and click delete.
 
The right way is to create a table with 'Auto Summarize On'. Just create a new table and then you will have option to check 'Auto Summarize On'. This method just creates a more efficient design. With tables in this mode there are no detail rows.

Elja

Thanks again mblock.
 
I got it working with both solutions. I'll get back to this reports development as soon as I'll get the former report working correctly.
 
- Elja

Elja

Hey again, mblock.
 
I'm wondering how could I use a computed field in joint data set for grouping..
 
With one data set I could choose the computed column to group on, but in joint data set (regardless whether I've created it in one of the original data sets OR directly to the joint data set, the computed column is not on the list of the possible Group on fields..
 
Is this somehow off the limits or have I done something wrong?
 
- Elja

Elja

Oh, it seems that after I placed it in the report table it appears as a value in the grouping properties!
 
- Elja

micajblock

Yes, it needs to bound to the table before you can use it.

Elja

Ok! Thanks for clarifying it.
 
- Elja

Elja

Hey Mblock, I've still got a question:
 
I have to create a joint table (combining 2 different data set info). Earlier you mentioned about ditching the detail row by either deleting it OR "creating a table with 'Auto Summarize On'.
 
Is it possible to use the the auto summarize table -option with joint table? Or do I simply use the detail-row deletion in this case..
 
- Elja

Elja

.. and another question following:
 
It seems that just hiding the visibility of the detail row works fine.

BUT: How could I make all the age groups visible? Even if there were no rows in the report related to the specific age group, I'd like to show them all in the report..
 
- Elja

micajblock

Yes, setting visibility works but it is not efficient as the content gets created. As far as your other requirement. You can create a scripted data source with the list and create another joined data set with an outer join.

Elja

Thanks again for your help, mblock!
 
- Elja

Elja

Hmm, sorry to return once again to this issue..
 
Did you mean scripted Data Set or scripted Data Source? Any hints (more specific) how to proceed with this approach?
 
- Elja

micajblock

Attached example. You will need both a scripted data set and data source. On the scripted data set look at the open and fetch events.

Elja

Thank you again, mblock.

I'll look into this approach.
 
- Elja