Birt-Crosstab with empty columns

Question

so I'm a BIRT beginner, and I just tried to get a real simple report from one of my tables of a postgres DB.

So I defined a flat table as datasource which looks like:

+----------------+--------+----------+-------+--------+
|      date      | store  | product  | value | color  |
+----------------+--------+----------+-------+--------+
| 20160101000000 | store1 | productA |  5231 | red    |
| 20160101000000 | store1 | productB |  3213 | green  |
| 20160101000000 | store2 | productX |  4231 | red    |
| 20160101000000 | store3 | productY |  3213 | green  |
| 20160101000000 | store4 | productZ |  1223 | green  |
| 20160101000000 | store4 | productK |  3113 | yellow |
| 20160101000000 | store4 | productE |   213 | green  |
| ....           |        |          |       |        |
| 20160109000000 | store1 | productA |   512 | green  |
+----------------+--------+----------+-------+--------+

So I would like to add a table / crosstab to my birt report which creates a table (and after that a page break) for EVERY store which looks like:

**Store 1**
+----------------+----------+----------+----------+-----+
|                | productA | productB | productC | ... |
+----------------+----------+----------+----------+-----+
| 20160101000000 |     3120 |     1231 |     6433 | ... |
| 20160102000000 |     6120 |     1341 |     2121 | ... |
| 20160103000000 |     1120 |     5331 |     1231 | ... |
+----------------+----------+----------+----------+-----+
--- PAGE BREAK --- 
....

So what I tried in first was: Getting to work the standard CrossTab tutorial-template of BIRT.

I defined the DataSource, and created a datacube with dimension-group of 'store' and 'product' , and as SUM / detail -data the 'value' and for this example I just selected ONE day.

But the result looks like this:

+--------+----------+----------+----------+----------+-----+----------+
|        | productA | productC | productD | productE | ... | productZ |
+--------+----------+----------+----------+----------+-----+----------+
| Store1 |      213 |          |     3234 |      897 | ... |     6767 |
| Store2 |      513 |     2213 |     1233 |          | ... |      845 |
| Store3 |       21 |          |          |       32 | ... |          |
| Store4 |      123 |      222 |      142 |          | ... |          |
+--------+----------+----------+----------+----------+-----+----------+

It's because not every product is selled in every store, but the crosstab creates the columns by selecting ALL products available.

So, I just have no idea how to generate dynamicly different tables with different (but also dynamic) amount of columns.

The second step then would be to get the dates (days) to work.

But thanks in advance for every hint ot tutorial link to question one ;-)

Jeroen Jeroen · Accepted Answer · 2016-03-25T14:51:29

You can just add a table with the complete datasource. Select the table and a group. Group by StoreID. You can set the pagebreak options for each grouping. Set the property for after to "always exluding last".

BIRT will add a group header. You can add multiple groupheader rows get the layout you're after.

For crosstabs it works in a similar way. After you added the crosstab to your page and set the info for the groups on rows and columns and added summaries. You can view the data. Select the crosstab and View the Row Area properties, select the pagegroup settings and add a new pagebreak. You can select on which group you want to break, choose your storeID group and select after: "always excluding last"

Birt-Crosstab with empty columns

1 Answers