Gouping Queried Data on a specific ID value in Birt Reporting Tool

Question

For a report that I am creating, I pull certain information from a report parameter. Say my user1d is 145, all my desired information shows up in my form in list order because I am using tables and grids for my form structure. I have four sections of information userid(in a grid in the header of my table) orders, payments and payment history which are fields in my table. Instead of listing all the information one after the other. I would like to group/link my results upon its userid, with its respective orders, payments, and payment history, and then the following userid's and their respective attributes in order.

EX: USER: 145 145 ORDERS 145 PAYMENTS 145 PAYMENT HISTORY

USER: 146 146 ORDERS 146 PAYMENTS 146 PAYMENT HISTORY

and so on....

It is important that my form follows this structure. Is this possible to do in Birt? If so does my issue have anything to do with how I am pulling my information? Do I need separate queries for each section? Or am I not utilizing a grouping method that BIRT has built in? If anyone has experience with BIRT and has done something like what I am trying to do I would greatly appreciate your help!

THANK YOU in advance!

score 0 · Answer 1 · answered Jun 25 '20 at 06:51

This is possible with one query containing all the data as well as with separate queries (one for the each section).

But probably the columns for each section differ a lot, so the second approach is usually better, so I'll give you the idea for the second approach only.

I guess you have a column USER_ID or whatever in your USER query and you want to use this column to restrict the data in each of the other sections.

For simplicity, I assume your Da Sets are called USER, ORDERS, PAYMENTS and so on.

The idea is as follows:

For the layout, use the following construction (item type and "name", you should always define a name for container items as a best practice):

List "List_USER"
  Detail
    Grid "Grid_USER"
       -- shows a USER records columns in its cells.
    Table "Table_ORDERS"
    Table "Table_PAYMENTS"
    Table "Table_PAYMENT_HISTORY" (*)

"List_USER" is bound to the dataset USER.

"Grid_USER" is not bound, it inherits the bindings from "List_USER".

"Table_ORDERS" is bound to the dataset ORDERS.

"Table_PAYMENTS" is bound to the dataset PAYMENTS.

"Table_PAYMENT_HISTORY" is bound to the dataset PAYMENT_HISTORY.

The last three tables and their datasets (e.g. the details) needs special handling, I'll show this for "Table_ORDERS".

The dataset (e.g. ORDERS) needs a dataset parameter param_USER_ID.

The SQL query needs a bind variable (with the syntax ? or :USER_ID if you are on Oracle) which is used in the WHERE clause, typically it will look like

SELECT ORDERS.ORDER_ID
     , ORDERS.ORDER_DATE -- and so on
FROM   ORDERS
WHERE  ORDERS.USER_ID = ?

-- On Oracle you may want to use WHERE ORRDERS.USER_ID = :P_USER_ID instead, this will allow testing your query in Oracle SQL*Developer.

Note that dataset parameters and bind variable occurrences in the SQL are mapped by position, the first dataset parameter corresponds to the first ? and so on.

In the "Table_ORDERS" "Binding" tab, open the "Data Set Parameter Binding..." dialog and assign the value row["USER_ID"] to the dataset parameter param_USER_ID.

Note that you can choose from the "Available Column Bindings" in the edit dialog. Also note that "OK" just closes that dialog. To actually choose an a column binding, you need to double-click, e.g. double-clicking on USER_ID will copy the text row["USER_ID"] into the expression text.

Gouping Queried Data on a specific ID value in Birt Reporting Tool

1 Answers1