Non-trivial tree structure

Hi! 
I'm a BIRT newbie trying to figure out whether BIRT would be a suitable tool for my needs. Basically I'm looking for a reporting engine for a financial application. 
 
Unfortunately I'm stuck trying to figure out a reasonable way of implementing the hierarchical structure common to many of the reports I'd like to implement. 
 
A good example of this kind of report is an ordinary Balance sheet: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Liabilities & equity
Liabilities
Current liabilities
Accounts payable
... # ###.##
Total accounts payable # ###.##
Payroll liabilities
... # ###.##
Total payroll liabilities # ###.##
Other current liabilities
... # ###.##
Total other current liabilities # ###.##
Total current liabilities # ###.##
Long term liabilities
... # ###.##
Total long term liabilities # ###.##
Total liabilities # ###.##
Equity
... # ###.##
Total equity # ###.##
Total liabilities & equity # ###.##</pre>
 
I.e. there's a nested set of groups, each group with a header, a number of items, and a footer. 
Each footer has the calculated sum of the items within the group (including any subgroups). 
 
The structure is fixed, and in fact, I would very much prefer the report design to dictate the structure. However, the header and footer of groups without any values should not be displayed. 
 
I tried two approaches; 
1. A "static" design using a grid. In this case I haven't been able to figure out how to fetch the data items for each row. Also I have no idea how to make it hide "groups" without content. 
 
2. Using a table with nested groups. The major problem I have with this approach is that the groups have different depths, i.e. the data items are not all at the same level. Some reports even need to mix data items and groups at the same level. 
 
Are there any good examples of similiar designs out there? 
Any suggestions? 
 
Regards, 
Christer

Find more posts tagged with

Comments

mwilliams

Can you show a sample of what some data would look like and what you want the output to be? This way, I'll have a better idea of what you're working with, from start to finish.

Christer

Sure, attached is a PDF as generated by my current home-brewn XSL-FO-based report generator (in swedish though...). 
As you can see, it's pretty much like the table in my original post, although this example has one level less detail. 
 
The source input data is a table of account/balance pairs and a set of mapping tables that maps each account into a category (i.e. a line in the report). 
For example: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
ACCOUNT BALANCE
======= ==========
3080 14211.00
3780 -0.38
4000 1680992.17
4500 14760.00
5010 7000.00
5210 305000.00
5405 43248.00
...

ACCOUNT CATEGORY_ID
======= ===========
3080 1
3780 1
4000 2
4500 3
5010 4
5210 4
5405 4
...

CATEGORY_ID CATEGORY
=========== =====================
1 Accounts payable
2 Payroll liabilities
3 Other current liabilities
4 Long term liabilities
...
</pre>
 
The PDF example has a line for each category with the sum of the account balances for the accounts mapped to that category. 
 
My original example would additionally have each account and its balance appear as an additional level under the respecive category, which is a variant of the report. 
 
However, I don't worry too much about the data as there's no problem to feed it in cooked form to the report generator (although, as stated before, it would be nice if the structure in which the categories appear in the report would be dictated by the report design rather than the cooking logic). 
 
Regards, 
Christer

Christer

[Double post removed... <a class='bbc_url' href='http://www.birt-exchange.org/org/forum/public/style_emoticons/'>http://www.birt-exchange.org/org/forum/public/style_emoticons/</a><#EMO_DIR#>/confused.gif ]

mwilliams

It would appear, from your data, that you could join all of these tables together to have a single dataSet, then you could simply use a grouped table, with multiple levels of grouping to display your structure. If there are no rows in a group, it won't display. Are all three of the set of data above brought in in different dataSets? Let me know. Also, what's your BIRT version?

Christer

Hi! 
Yes, that exactly one of the approaches I tried. But as I tried to explain, I can't get it to work when the branches of the tree has different depths. For example, taking my sample report (leaving aside labes, summing etc.) I have the following data set: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
group1 group2 name value
A 1 A11 77912
A 1 A12 315631
A 2 A21 277297
A 2 A22 2154
B 1 B11 2063742
B 1 B12 538396
B 1 B13 5876
B 2 B21 424835
B B3 105254
</pre>
 
The result I want is: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Title A
Subtitle 1
A11 77912
A12 315631
Subtitle 2
A21 277297
A22 2154
Title B
Subtitle 1
B11 2063742
B12 538396
B13 5876
Subtitle 2
B21 424835
B3 105254
</pre>
 
But the result I get is: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Title A
Subtitle 1
A11 77912
A12 315631
Subtitle 2
A21 277297
A22 2154
Title B
Subtitle 1
B11 2063742
B12 538396
B13 5876
Subtitle 2
B21 424835
Subtitle null
B3 105254
</pre>
Notice how B3 is put inside the 'null' group. 
 
I'm using Eclipse Indigo, my BIRT plugin version is 3.7.2.v20120213-4617w31231324. 
 
Regards, 
Christer

mwilliams

Using that data, here's a fairly simple solution. It checks the group2 value and displays the name, instead of the group 2, if the group is null. Visibility expressions on the detail row and value element in the group2 header are also used. You may have to get a little more complex or a little different with your expressions, if your data is more complex, but this may be a place to start.

Christer

Thanks Michael! 
Your trick with having the group header double as a detail item is clever, but unfortunately it doesn't work very well; 
<ul class='bbc'><li>It doesn't work correctly when there's more than one detail row at a given group level</li><li>The ordering is not correct</li><li>It doesn't work well when group headers vs. detail rows should be formatted differently</li></ul>
 
However, as it turns out it is possible to have multiple detail items in a table. Using that together with a 'level' column to help control the visibility for the various items I was able to get exactly what I want without too much complexity. 
 
I would have attached an example if it wasn't that I'm "not permitted" to upload .csv files.

Regards, 
Christer

mwilliams

If you'd like to upload an example, you can always zip the csv and rptdesign and put that in here. Or change the .csv to .txt.