Forum Stats

  • 3,816,070 Users
  • 2,259,137 Discussions
  • 7,893,384 Comments

Discussions

Customized Report Totals/Subtotals

FJW
FJW Member Posts: 30
edited Oct 18, 2013 2:03PM in APEX Discussions

Hi - hoping someone can point me in the right direction.

I have a report as follows:

select DISTINCT LOB as "LOB",

PRODUCT_TYPE as "Product_Type",

sum(LINE_REVENUE)||' / '||sum(TRX) as "Rev/Trx"

from REVENUE_TABLE

GROUP BY LOB,PRODUCT_TYPE

I would like to display LOB and Product Type subtotals, as well as report totals, in this same format "Rev/Trx". Is there a way to do this?

FJW

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,821 Red Diamond
    Answer ✓

    There are various ways of doing this using different combinations of SQL, standard report features, report templates, and jQuery/JavaScript. Exactly which method is most appropriate depends mainly on how you want to format the total rows, whether you need pagination (which is mainly dependent on the volume of data), and whether you need to be able to sort the data.

    Using SQL, standard report features, and the Standard report template, here's a basic example—no formatting of totals, fixed sort order, and no pagination.

    The report query is:

    select
        lob
      , product_type
      , sum(line_revenue) rev
      , sum(trx)          trx
      , grouping_id(
          lob
        , product_type)   gid
    from
        revenue_table
    group by
        rollup(
            lob
          , product_type)
    order by
        lob nulls last
      , product_type nulls last
      , gid
    

    In the report attributes, the Pagination Scheme is set to None; Number of Rows to 500; the TRX and GID columns are hidden; the REV column is formatted using an HTML Expression of #REV#/#TRX#; and Break Columns is set to First Column.

    If that's not a suitable starting point then you'll have to provide more detailed information on your requirements.

    FJW
«1

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,821 Red Diamond
    FJW wrote:
    
    Hi - hoping someone can point me in the right direction.
    
    I have a report as follows:
    
    select DISTINCT LOB as "LOB",
    PRODUCT_TYPE as "Product_Type",
    sum(LINE_REVENUE)||' / '||sum(TRX) as "Rev/Trx"
    from REVENUE_TABLE
    GROUP BY LOB,PRODUCT_TYPE
    
    I would like to display LOB and Product Type subtotals, as well as report totals, in this same format "Rev/Trx". Is there a way to do this?
    

    It depends.

    APEX version?

    Standard or interactive report?

    Theme and report template if it's a standard report?

  • FJW
    FJW Member Posts: 30

    Thanks for your reply.

    -Apex Version 4.2

    -standard report (could be flexible here if helps get the desired output)

    -template is "reports region" - again could be flexible here if it helps get the desired output

    -theme is Cloudy - 24

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,821 Red Diamond
    FJW wrote:
    
    -template is "reports region" - again could be flexible here if it helps get the desired output
    

    That's a region template, not a report template. The report template is specified on the report attributes tab.

  • FJW
    FJW Member Posts: 30

    Sorry its Template 24: Standard - but again, pretty flexible there, can change if it will help

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,821 Red Diamond
    Answer ✓

    There are various ways of doing this using different combinations of SQL, standard report features, report templates, and jQuery/JavaScript. Exactly which method is most appropriate depends mainly on how you want to format the total rows, whether you need pagination (which is mainly dependent on the volume of data), and whether you need to be able to sort the data.

    Using SQL, standard report features, and the Standard report template, here's a basic example—no formatting of totals, fixed sort order, and no pagination.

    The report query is:

    select
        lob
      , product_type
      , sum(line_revenue) rev
      , sum(trx)          trx
      , grouping_id(
          lob
        , product_type)   gid
    from
        revenue_table
    group by
        rollup(
            lob
          , product_type)
    order by
        lob nulls last
      , product_type nulls last
      , gid
    

    In the report attributes, the Pagination Scheme is set to None; Number of Rows to 500; the TRX and GID columns are hidden; the REV column is formatted using an HTML Expression of #REV#/#TRX#; and Break Columns is set to First Column.

    If that's not a suitable starting point then you'll have to provide more detailed information on your requirements.

    FJW
  • FJW
    FJW Member Posts: 30

    Thanks Scott, this is a helpful example

  • FJW
    FJW Member Posts: 30

    Thanks fac586 - this is perfect. I'll play around with this and let you know if I have any follow up but I think I can tailor this to my needs.

  • FJW
    FJW Member Posts: 30

    One more question on this - the rows are formatting just like i intended, but the totals are still just showing the REV total rather than REV/TRX. I followed your instructions and selected "SUM" for the rev column in Report Attributes. Do I need to add something in Break Formatting?

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,821 Red Diamond
    FJW wrote:
    
    One more question on this - the rows are formatting just like i intended, but the totals are still just showing the REV total rather than REV/TRX. I followed your instructions and selected "SUM" for the rev column in Report Attributes. Do I need to add something in Break Formatting?
    

    Using "SUM" in the Report Attributes was never mentioned. All of the required summation is performed in the SQL query. The REV/TRX values are displayed in one column by using an HTML Expression of #REV#/#TRX# in the Column Formatting section of the REV column attributes.

    FJW
This discussion has been closed.