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

Grouped, subcategorized, multi-column report

javaguy

Quite possibly, there has been a post, or several, relating to my problem, but I can't find any.

I need a report generated from a dataset. My dataset is derived from and SQL statement joining three tables.

I need the report to layout as follows:

DESCRIPTION PRODUCT # UNIT PRICE
CATEGORY
Sub-Category
Item Description nnnnn $ 00.00
Item Description nnnnn $ 00.00

CATEGORY
Sub-Category
Item Description nnnnn $ 00.00
Item Description nnnnn $ 00.00

CATEGORY
Sub-Category
Item Description nnnnn $ 00.00
Item Description nnnnn $ 00.00

Any help would be very useful.

Find more posts tagged with

Comments

mwilliams

Hi javaguy,

You just want it in a single column like that? Or multiple columns like your thread title states? If just a single column, a standard group report should do it, with a group and a sub-group for your category and sub-category. Let me know.

javaguy

No, the editor left justified all of my columns. Here is what I need, minus the dashes(they just allow me to define my columns here):

DESCRIPTION

PRODUCT #----UNIT PRICE

-CATEGORY

SubCategory

Item Description---1200

$ 0.25

Item Description---1200

$ 0.25

SubCategory

Item Description---1200

$ 0.25

Item Description---1200

$ 0.25

-CATEGORY

SubCategory

Item Description---1200

$ 0.25

Item Description---1200

$ 0.25

SubCategory

Item Description---1200

$ 0.25

Item Description---1200

$ 0.25

PLUS, I need to have the above format duplicated in a second column with the next set of data, to save paper when printed.

mwilliams

javaguy,

What version of BIRT are you using? Does your data come in pre-grouped/sorted from your query? Or do you rely on the in BIRT table grouping to do this?

javaguy

I use 2.5.2 of BIRT.

My data comes in from a complex join query, not grouped. I group on the first column inside the BIRT report design tool.

javaguy

OK, I have figured out the grouping, now I need to figure out how to make the list I showed previously in a second column. I tried using a 2 column grouped layout, but it just repeated the data in both columns.

roadblock2

Maybe using a grid? Create a two column, one row grid, and place a copy of your formatted table in each cell of the grid.

javaguy

I just tried that, and it duplicated the data in both columns.

mwilliams

javaguy,

You'll also have to use a filter on each table to limit the data in each table to be different from the other. For example, if you brought your data into your dataSet sorted by the column you group on, you could add a computed column that kept a running count of the rows and use this column in your filter to filter one half to one table and the other half to the other table.

Bri

I tried the following, but it wouldn't quite work...
1. Create a variable in the report initialize event: var iGroupCount = 0;
2. Increment the variable in the Category group FOOTER row's onCreate event: iGroupCount++;
3. Use iGroupCount as a filter for your data sets.

This is because I think the filter takes the iGroupCount's value before any of the rows events are executed.

But - in the same spirit, you could create a category group number in the query based on the count of Categories that precede it in order then use that as a filter.

SELECT
COUNT( 1 ) AS category_number,
main.category,
main.subcategory,
main.data_a,
main.data_b
FROM
category_table AS main,
category_table AS counter
WHERE
counter.category < main.category
GROUP BY
main.category,
main.subcategory,
main.data_a,
main.data_b

Then, like step 3 above, use category_number to filter the tables by.
The table on the left would be something like: BirtMath.mod(row["category_number"], 2) Equals 0
The table on the right would be something like:
BirtMath.mod(row["category_number"], 2) Equals 1

Hope that helps.