How to sum distinct values within the same transaction number in RTF using BI Publisher?

Question

I am seeking guidance on how to calculate the sum of the line amount when the transaction number is same. This calculation is required for TOTAL_AMOUNT column based on their particular TRX_NUMBER. Want to sum the unique transaction number LINE_AMT on a BI Publisher RTF file. Attempting many method, it was unsuccessful and resulted in an empty value.

Answer 1

Not quite sure what do you want to sum, but it looks like it is the sum of the line amount per transaction. If that is the case you could use Sum() Over() analytic function in your data set sql query. Maybe something like here:

WITH    --  S a m p l e    D a t a :
    tbl (A_DATE, TX_NUM, CGST_AMT, SGST, SGST_AMT, IGST, IGST_AMT, TCS, TOTAL, SELL_GSTIN, LINE_AMT) AS
        ( Select To_Date('09-12-2023', 'dd-mm-yyyy'), 67364, 4784, 9, 4784, NULL, 0, 0, 62728, '29AAOCA1', 62729.71 From Dual Union All
          Select To_Date('09-12-2023', 'dd-mm-yyyy'), 67366,  373, 9,  373, NULL, 0, 0,  4899, '29AAOCA1',  4900.75 From Dual Union All
          Select To_Date('02-12-2023', 'dd-mm-yyyy'), 64117,  349, 9,  349, NULL, 0, 0,  4585, '29AAOCA1',  4587.38 From Dual Union All
          Select To_Date('05-12-2023', 'dd-mm-yyyy'), 64117, 1010, 9, 1010, NULL, 0, 0, 13247, '29AAOCA1', 13248.00 From Dual Union All
          Select To_Date('09-12-2023', 'dd-mm-yyyy'), 67455,  524, 9,  524, NULL, 0, 0,  6874, '29AAOCA1',  6874.89 From Dual Union All
          Select To_Date('09-12-2023', 'dd-mm-yyyy'), 67455,  747, 9,  747, NULL, 0, 0,  9800, '29AAOCA1',  9801.53 From Dual 
        )

--   S Q L :
Select tbl.*, Sum(LINE_AMT) OVER(Partition By TX_NUM) "TX_NUM_LINE_AMT_TOTAL"
From   tbl

/*    R e s u l t :
A_DATE       TX_NUM   CGST_AMT       SGST   SGST_AMT IGST   IGST_AMT        TCS      TOTAL SELL_GST   LINE_AMT TX_NUM_LINE_AMT_TOTAL
-------- ---------- ---------- ---------- ---------- ---- ---------- ---------- ---------- -------- ---------- ---------------------
02.12.23      64117        349          9        349               0          0       4585 29AAOCA1    4587,38          17835,38
05.12.23      64117       1010          9       1010               0          0      13247 29AAOCA1      13248          17835,38
09.12.23      67364       4784          9       4784               0          0      62728 29AAOCA1   62729,71          62729,71
09.12.23      67366        373          9        373               0          0       4899 29AAOCA1    4900,75           4900,75
09.12.23      67455        747          9        747               0          0       9800 29AAOCA1    9801,53          16676,42
09.12.23      67455        524          9        524               0          0       6874 29AAOCA1    6874,89          16676,42    */