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

Grouping by large number of columns

edoetsch

I have been charged with changing an existing Birt Report.

It is a summary report that previously gathered all of its data from a single data source. Therefore, all grouping was done on the Oracle side of the equation.

The resulting data set has LOTS of columns (15 more than my example below) by which it is grouped only 2 summed columns. So the layout was essentially a detail report of summary data. For example:

select
super,
ent,
acct,
campaign,
subcampaign,
sum(count) as "count",
sum(duration) as "duration"
from DataTable1 d1, DataTable2 d2
where d1.id = d2.id
group by
super,
ent,
acct,
campaign,
subcampaign

Now I have two data sets because of different connections for the two tables:

select
id,
super,
ent,
acct,
campaign,
subcampaign,
from DataTable1 d1

AND

select
id
count as "count",
duration as "duration"
from DataTable2 d2

So I now have to create a joint data set at the row level on id's and I've done this.

However, nowe I need to group on super, ent, acct, campaign, subcampaign in the report.

A cross tab seems to only handle two group by columns, one for the X-axis and another for the Y-axis.

What is the best way to approach this problem?

Find more posts tagged with

Comments

kclark

Can you post the rptdesign that you're modifying?

Hans_vd

Hi,

Crosstabs can have as many groups as you want. Just add a group to the Data Cube and drag to the row or column area in the crosstab.

But I don't think you need a crosstab. You can create a table with multiple groupings and remove all grouping headers and footers except the most inner one. That will be your detail row. The detail row itself you can remove.

Hope this helps
Hans

edoetsch

The upload of my rptdesgin keeps saying "Error This upload failed" It's size is 100K so should not be a problem

03/05/2013 09:35 AM 123,999 sbServiceSummaryReport.rptdesign

Hans_vd

Hi,

If you zip the rptdesign file, you should be able to upload it.
Is there anything unclear about my answer? I'd be glad to clarify.

Regards
Hans

edoetsch

Well, adding a group is a bit of a mystery. It seems to add an entire row per group.

I am right clicking on the Table and insert group above. Then it creates an entire row in the table with just the single column on which I group. Do I just keep going with this process until I've got my 23 groups appearing as separate rows in the table?

Hans_vd

It may not seem ideal, but yes, that's it.
When you are done with adding all 23 groups, you can drag the data bindings to the most inner group row and remove all the others header and footer rows. The rows are then removed, but the groups still exist.

edoetsch

It seems to be working but the layout is not doing what I expect. I think it is due to each grouping being put on one row of the report. 
 
 
 
 
Super 
>>>>>>>Ent 
>>>>>>>>>>>>Acct 
>>>>>>>>>>>>>>>>>>>Campaign 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>Subcampaign 
Super Ent Acct Campaign Subcampaign Count Duration

edoetsch

Attaching the report which I think it pretty close, though the math is wrong. I left the detail row in which I eventually plan on taking out. This simply shows the rows that comprise the summary row.

Hans_vd

<blockquote class='ipsBlockquote' >It seems to be working but the layout is not doing what I expect. I think it is due to each grouping being put on one row of the report.</blockquote>
 
That's why I wrote: 
<blockquote class='ipsBlockquote' >you can drag the data bindings to the most inner group row and remove all the others header and footer rows. The rows are then removed, but the groups still exist.</blockquote>

edoetsch

I guess this doesn't make sense as to what to do...

"you can drag the data bindings to the most inner group row and remove all the others header and footer rows. The rows are then removed, but the groups still exist."

Which bindings? Under Data Sets or cubes?

Hans_vd

Hi,

I took a look at your report. Seems to me you already did what needs to be done.
You can just remove all the empty header and footer rows and the detail row.
Or am I misunderstanding what the problem is?

Regards
Hans

edoetsch

The math is not correct. The summary line simply prints the first value of its detail rows.

Hans_vd

Instead of just showing the fields (the last two fields I mean) in the header row, you should to create SUM aggregations for them:
- remove both fields from the header line
- drag an "aggregation" item from the palette into the table
- create a SUM aggregation, enter (or select) RMS::Count field as the expression
- do the same for RMS::DurationSecs

edoetsch

I think my confusion is on the "- drag an "aggregation" item from the palette into the table"

Not sure what this is. If I right click on the now empty count cell and try to insert an aggregation, I seem to only be able to select a single column over which to aggregate. The aggregation item from the palette should allow me to group over the 20 or so other columns. Am I right?

Hans_vd

You go to the palette view, click aggregation, and while keeping the mouse button down, move the item to the header row in the report. That's what dragging is :-)

edoetsch

This appears to give me the same option as right clicking on the cell and selecting Insert -> Aggregation.

Should I pick Table? Or should I pick one of the other columns?

Thanks so much for all of the hand holding on this. I feel I am micrometers from completing this and just getting hung up on terminology. I've only bee doing Birt for a few days now.

thanks again.

Hans_vd

Hi,

You need to create the aggregation in the header row. If you do that it will automatically select Group and enter UnitType in the Group field. You keep it like that, or, if it is not automatically filled in, select the Group radio button and select UnitType yourself.

Jenkinsj5

There are also some great tutorials in the help text

edoetsch

OK I think I am centimeters from finishing this thing.

By grouping on the last column of the drop-down when creating my aggregate, the match seems correct.

Now I simply have the extra rows probably due to all of the groupings in the report. Thanks to everyone for your help. It's tough when I only have 30-60 minutes a day to work on Birt. Switching gears is terribly tough.

Hans_vd

To get rid of the grouping rows, right click and delete them :-)
Don't fear, the groupings themselves will not disappear by deleting the according grouping header and footer rows.

edoetsch

Thanks Hans_vd,

Now I am moving on to an unfortunately harder issue...

http://www.birt-exchange.org/org/forum/index.php/topic/28980-birt-eclipse-no-longer-launches/