What's the best way to modify the way dynamic cells are rendered ?

xrorox · February 13, 2019

Hi,

We often use Birt to generate some xlsx.
Quite often we need to be able to merge cells horizontally or vertically to met requirements.
Some good examples, would be to merge cells that have same values vertically ( drop property of grouping in table is broken) in table.
I know this works in crosstabs but usually the need happens in use case where we can't use crosstabs.

Another case, was to force a cell to have a colspan of 2 in a crosstab. We managed to do it, but I think we exploited a bug in this particular case.
For this kind of operations, what is the way to go (mostly developpers are working on reports) ?

Also as a side question, do you have any sources about birt best practices ?
Because we feel that even for things that looks basics, we spend a lot of time and end up scripting just to get "basic" behaviors working.

jfranken · February 14, 2019

Could you please provide examples of how the spreadsheet displays and how you want it to display?

Regarding learning the product, the first step would be going through the User Guide. If it doesn't have the information you need, there are some good books that have been written (search "BIRT field guide"). Also, training courses are offered: https://www.opentext.com/products-and-solutions/services/training-and-learning-services

xrorox · February 18, 2019

Excuse me for the delay, I was busy.

I have outlined in yellow the problematic areas and in green some explanations about relevant technical datas.

Actually, we managed to generate this, but this is overcomplicated or outright bugs exploits that helped us achieve these results.

**Case 1 : **
The group function of tab with drop property would have been perfect. Except it don't work the way it is supposed to.
There is a workaround that work, if you only have one group (using group header as first line, and hiding first data line through script).

So we had to resort to using multiple sub tabs, one for each grouping.
Where it became outright ugly, is that we have to handle some dynamic columns at the end of the tab.
There is a maximum of 10 top criterias that can have 10 subcriterias.
I think this requirement prevented us to use a crosstab that would have solved our merging problem.

I would like a one tab approach to handle groups properly, but we didn't found any.

**Case 2 : **

In this case, we could use crosstabs, but for design reasons we needed that each cell of the first crosstab had a colspan of 2.
Because, the following crosstables display data linked to the same dimension.

We managed to do it by creating one empty cell at the right of each useful cell.
At the "render" stage we drop the empty cells and set a colspan of 2 to the useful ones.
That work, but from what I understand it shouldn't work at this stage of the lifecycle.

What I'm looking for : I wish to know if there are "best practices" ways to handle these problems.

jfranken · February 20, 2019

Thank you for providing the example and the description. Unfortunately I have read what you wrote a few times and I'm not sure if I understand what you are asking. For example, you said, "The group function of tab with drop property..." I don't know what a "tab" means in this context. I also don't know what "drop property" or "group function" you are referring to.

The only thing I can think of is that you might be talking about "guide cells" on a table in the designer. When you select a table, guide cells appear around the outside of the table. They look like tabs.

If, for example, you have multiple header rows and you want to combine them into one row, there is no grouping command for the tabs which might be what you are describing. However, you can click in a cell inside the table, hold down the left mouse button, and drag to select multiple cells (or shift+click). Then you can right click on the selected group of cells and choose the "merge" command in the popup menu. Note that this is only for rectangular shapes. You cannot select two columns in one row and three columns in another row and merge them. Sometimes it takes multiple merges to achieve the desired merged cells. Like table cells, grid cells can be merged in a similar manner. When you create larger cells via merging in either a table or a grid, use the padding and margin properties to position data within the cells.

In terms of creating colspans and other formatting when exporting to Excel, there isn't an exact formula for how to get things to position and size correctly in Excel. The emitter software does its best, but there are unlimited scenarios for converting sizes and positions of text into a spreadsheet that by its basic design conforms all content into columns. It is a difficult thing to automate. If you don't link the way the built-in emitter functions, there are third party emitters (i.e. Spudsoft) that might suit your needs better. The Spudsoft emitter is included with some versions of BIRT. Please keep in mind that the conversion from a report editor to a spreadsheet isn't always going to be perfect, but the Excel emitter generally does a very good job.

If this is the type of information you are looking for, some training might help. Also, it is free to start new threads on this forum, so don't hesitate to ask more questions. In general, it helps if you start a new thread for each new question, include your product version information, and include screenshots or sample reports illustrating the issue.

xrorox · February 20, 2019

I'm sorry, I wasn't very clear. By tabs I'm meaning "tables". I was referring to the grouping options. On the header cell of the group we can set a property named "drop" to "detail" (others values are all and none).
The way it is supposed to work with the details property, is that it should drop the "Header" line of the group into the "detail" line of the table.

Example of theorical output :

grouped_value -- non grouped value
-- non grouped value
-- non grouped value

In reality, the header line of the group is still there in every output format.
There are workaround for most formats, but in xlsx it is displayed.

We are going to try with the spudsoft emitter.

Actually, our requirements on the merging part may not be possible. Usually, we have needs to conditionnally merge expanded details cells. By extended, I mean when data has already be bound to the table.

The other problem we had regarding merging, was to merge crosstabs cells (or at least emmitting a cell with a colspan of 2).

Truth be told, is that these uses cases come from Xlsx files created with specifics libraries like infragistics.
And that's probably too specific to be supported use cases in Birt.
We are using Birt 4.5.0, if that help.

jfranken · February 20, 2019

Thank you for the clarification. I have attached a report that shows how to drop a group to the detail row. I tested exporting it to Excel and it worked fine.

Steps:
1. Add a hidden parameter to the report
2. Move the group data element to the detail row
3. Add a few lines of code in the data element expression to conditionally display the value.

I don't completely follow what you want to do with the merging of detail data, but merging data is generally the same technique as shown in the attached example. In some situations it is better to use computed columns in the data set.

What's the best way to modify the way dynamic cells are rendered ?

Comments

Categories