CODEWITHSUNDEEP
reportgroupingbirtdata-cube
Luke
Luke

Reputation: 67

BIRT Report: I have performed Data cube operation. But want to group medicine with qty for each record

The sql query is as follows:

SELECT  ROW_NUMBER() OVER() AS row_num,
        v.INVOICE_DATE,
        v.EMPLOYEE_ID,
        v.PATIENTNAME_AR,
        v.USER_LABEl, 
        v.OFFICE_ID,
        v.ITEM_NAME,
        v.ITEM_QTYSOLD  
FROM 
(
SELECT i.INVOICE_DATE,
       i.EMPLOYEE_ID,
       dc.PATIENTNAME_AR,
       us.USER_LABEl, 
       dc.OFFICE_ID,
       ii.ITEM_NAME,
       ii.ITEM_QTYSOLD
FROM ECLINIC_KNG.DOCTOR_CONSULT dc 
INNER JOIN ECLINIC_KNG.INVOICE i 
    ON(dc.CONSULT_ID=i.INV_CONSULT_ID)
INNER JOIN ECLINIC_KNG.INVOICE_ITEM ii
    on(i.INVOICE_ID=ii.ITEM_INVOICE_ID)
INNER JOIN ECLINIC_KNG.USER_SETUP us
    ON(dc.DOCTORS_ID=us.USER_ID)
WHERE 
    (i.INVOICE_DATE BETWEEN ? AND ?)
AND
 i.employee_id IN 
(
    Select employee_id 
    from ECLINIC_KNG.invoice
    WHERE employee_id <>'' AND length(ltrim(employee_id,' +-.0123456789')) = 0
    AND length(EMPLOYEE_ID)<6
    AND to_NUMBER(employee_id)< 50000
)
ORDER BY
    i.INVOICE_DATE ASC
)AS v;

I performed data cubing such that i grouped all the 7 parameters. The output is as follows

No Date Patient Id Patient Name Doctor Name Medicine Name Quantity
1 02-02-2023 001 Pat A Doc A Adol 30
2 02-02-2023 001 Pat A Doc A Panadol 20
3 02-02-2023 001 Pat A Doc A Paracetamol 10

I want the output as follows:

No Date Patient Id Patient Name Doctor Name Medicine Name Quantity
1 02-02-2023 001 Pat A Doc A Adol 30
Panadol 20
Paracetamol 10
2 02-02-2023 002 Pat B Doc A Lipitor A 30
Lipitor B 20
3 02-02-2023 003 Pat C Doc A Lipitor C 10

How to get the required output in BIRT REPORT. Please specify the steps

Upvotes: 0

Views: 89

Answers (1)

hvb
hvb

Reputation: 2669

Your wording is misleading. This question is not related to data cubes.

You only need a vanilla query (as you showed) and a table item with grouping.

There is no perfect solution (in regard to page-breaking), but there are two ways you can go:

  1. This is the easiest solution. It is restricted to simple tables like the one you showed and might not work if a single cell can contain multiple data items: Just select the corresponding table column in the layout and mark the "suppress duplicates" checkbox in the properties register.

  2. The second option is more complicated:

First, create your table in such a way as BIRT does automatically when you create a group:

You will have a group header row where the column identifying the group is shown. The same data item is shown in the detail cell in the corresponding column. Just delete this, IOW clear the detail cell.

The output will now look similar to what you want, but the group columns all appear in additional (group header) rows.

The trick is to select the group header cell and in the properties, choose Drop: detail. This will "drop" the data item from the group header cell into the first detail cell.

Now the output will look more or less like you want it.

The difficult part now is to handle padding and border to assure that the texts are vertically aligned. You'll have to experiment with the border and padding values (it seems that BIRT adds the padding values of the header cell and the detail cell).

Upvotes: 0

Related Questions