How to join a crosstab with a table?

[ltuch]

I have a report where I'd like to join a crosstab with some additional columns which are not part of the crosstab itself. Please see the screenshot below.

The green columns show a cross tab where the product code is a dimension, the month of the sale is a dimension, and the dollar sales are a measure. There can be many months of sales in actual report - 12 months, 24 months, or more.

It's easy to create a crosstab which has these green columns, but I'd like to join the crosstab with some additional columns, which are derived from other database tables. Columns B->D have some product data which is derived from a product table. Columns H and I have some additional information which is derived from a stock on hand table, and a purchase order table. Columns B->D and H->I are columns which are fixed, whereas Columns E->G can be variable depending on the date range for the report.

I came across one solution (https://forums.opentext.com/forums/developer/discussion/301542/combining-static-and-dynamic-columns-in-a-birt-report) which involves using a grid and aligning a table with a crosstab. This seems to work when producing HTML and ODS format but not when using XLSX output. Also, there are some hidden columns which don't get properly hidden from the output.

I can implement the desired report using Java/Javascript - I can join a crosstab and table in SQL and dynamically build a table in BIRT, without using BIRT's crosstab feature. But this if quite painful compared to implementing a report in the report designer.

Are there any alternative where I can make use of BIRTs crosstab feature?

[hvbtup]

I would use a pure SQL solution to create a table with N columns.

The idea is like this:

SQL Part: Step by step, use CTEs to develop your query:

1. Create a query which produces your data as a flat list.
2. Based on this query, use SQL analytic functions to assign logical row and column numbers to each record (imagine the size of your paper is unlimited)
3. In reality, your paper width is not unlimited, so for example you can have at most 10 physical columns. Use the MOD function etc to split your wiiiiiide logical matrix into several physical matrices, each matrix with at most 10 physical columns, with an additional column for the logical matrix number.
   Your query now still produces a flat list, but with addition columns for logical matrix number, logical row number and physical column number
4. Use GROUP BY and constructs like
   MAX(CASE WHEN phys_col_num = 1 THEN measure END) as measure1,
   MAX(CASE WHEN phys_col_num = 2 THEN measure END) as measure2,
   ...
   MAX(CASE WHEN phys_col_num = 10 THEN measure END) as measure10
   in the SELECT clause for the green columns.
   This is possible not only for the measures, but also for the column headers (you can have columns header1, ... header10 as well),
   but note that that the headerX column may be null for the first row, so you should wrap the header columns in a MAX(...) OVER(partition by logical_matrix_num).

The rest is layout work in BIRT:

Use a normal "table" item to show the data, not a cross tab.

Add a group based on logical_matrix_num to the data.

Use the "measureX" columns to display the data in your green columns. Use the "headerX" columns for the corrsponding column headers.
Optionally, use a visibility expression to hide empty green columns (the expression could be !row["header2"] for the second green column.