Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Beyond the Crown
    OTBI Analysis: Is it possible to add a column that...Answered
    Topic posted May 15, 2019 by Beyond the CrownBlack Diamond: 60,000+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Reports, Sample Reports 
    112 Views, 2 Comments
    Title:
    OTBI Analysis: Is it possible to add a column that summarizes Invoice Amount by Supplier?
    Summary:
    I'm building an analysis in OTBI and wanted to know if it's possible to have a calculated column that summarizes one of the other columns
    Content:

    I'm building an OTBI Analysis using Payables Invoices - Transactions Real Time and I wanted to know if it's possible to create a calculated column that summarizes the invoice column to show total spend by that vendor for the period of the report.

    For example, I have 3 columns:

    Supplier          Invoice Number       Invoice Amount

     

    I want to have 4 columns

    Supplier         Invoice Number        Invoice Amount        Total Spend by Supplier

     

    There are some summary functions in OTBI but so far they just sum up the total but not by Suppler. I don't seem to be able to "Group By" or get anything to work in the calculated field to do this.

     

    Thank you,

    Brian

    Best Comment

    Aaron Leggett

    Hi Brian, 

    This might help: 

    1) Create the calculated column, using this formula: RSUM("Invoice Amount Column") 

    2) Go to the 'Advanced' tab and scroll down to 'Advanced SQL Clauses'. Within here, there will be a GROUP BY box. 

    3) Enter the column name of the supplier (e.g. "Supplier"."Supplier Name") 

    4) Click 'Apply SQL', a warning box will pop up, click ok

    This should now give you a rolling sum row-by-row, grouped by supplier. Alternatively, you can use the SUM function to SUM up the total invoice amount in conjunction with the Advanced SQL Group By clause (so it won't be a do a sum row-by-row). Ensure that you set the column properties to 'Supress' rather than 'Repeat', otherwise you'll get a summed up amount on every row there is an invoice. 

    Comment

     

    • Gopinath Kartheesan

      Hi Brian,

      Rather than a separate column to summarize that might repeat for every invoice record for the supplier, did you try doing the "Sum" at the supplier level which will automatically create a group by and display the total by supplier in a separate row like how you can do in Excel using Group function.

    • Aaron Leggett

      Hi Brian, 

      This might help: 

      1) Create the calculated column, using this formula: RSUM("Invoice Amount Column") 

      2) Go to the 'Advanced' tab and scroll down to 'Advanced SQL Clauses'. Within here, there will be a GROUP BY box. 

      3) Enter the column name of the supplier (e.g. "Supplier"."Supplier Name") 

      4) Click 'Apply SQL', a warning box will pop up, click ok

      This should now give you a rolling sum row-by-row, grouped by supplier. Alternatively, you can use the SUM function to SUM up the total invoice amount in conjunction with the Advanced SQL Group By clause (so it won't be a do a sum row-by-row). Ensure that you set the column properties to 'Supress' rather than 'Repeat', otherwise you'll get a summed up amount on every row there is an invoice.