11 Replies Latest reply on Oct 18, 2013 6:03 PM by FJW

    Customized Report Totals/Subtotals

    FJW

      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?

        • 1. Re: Customized Report Totals/Subtotals
          fac586

          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?

          • 2. Re: Customized Report Totals/Subtotals
            FJW

            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

            • 3. Re: Customized Report Totals/Subtotals
              fac586

              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.

              • 4. Re: Customized Report Totals/Subtotals
                FJW

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

                • 5. Re: Customized Report Totals/Subtotals
                  Scott Wesley
                  1 person found this helpful
                  • 6. Re: Customized Report Totals/Subtotals
                    fac586

                    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.

                    • 7. Re: Customized Report Totals/Subtotals
                      FJW

                      Thanks Scott, this is a helpful example

                      • 8. Re: Customized Report Totals/Subtotals
                        FJW

                        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.

                        • 9. Re: Customized Report Totals/Subtotals
                          FJW

                          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?

                          • 10. Re: Customized Report Totals/Subtotals
                            fac586

                            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.

                            • 11. Re: Customized Report Totals/Subtotals
                              FJW

                              Thanks - I had tried without the sum as well. I actually have a more complex query then I gave in this question to try and make my question more clear, and realized that it wasn't working because I didn't extrapolate your instructions correctly to my more complex query. I've got it working now, now just trying to get the break formatting just right. Thanks again for all your help!