11 Replies Latest reply: May 30, 2014 2:57 AM by fac586 RSS

    Adding a Sub Report to a Row as a sub report

    PKB_82

      Hi all,

       

      I'm new to ApEx and learning as I go, however an interesting question arose about a report.

       

      I can generate a report from a table and display it with no issue, however I want to display a sub report per row based on different criteria but linked to the primary report.

       

      Eg.

       

      I have columns of Customer, Number of Products, Cost

       

      Which displays;

      Customer 1, 5, £25

      Customer 2, 2, £10

      etc

       

      What I want is to then display the actual products underneath eg.

       

      Customer 1, 5, £25

      Cow, Dog, Monkey, Horse, Mouse

       

      Customer 2, 2, £10

      Fish, Llama

       

      I understand i'll probably need to templates, and I could probably do with an IR and a submit to a linked page, but realistically it all needs to be on one page.

       

      Any help appreciated for a newbie

       

      Thanks

        • 1. Re: Adding a Sub Report to a Row as a sub report
          TexasApexDeveloper

          Something like this: https://apex.oracle.com/pls/otn/f?p=11933:108:110315066126585::NO:RP::

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: Adding a Sub Report to a Row as a sub report
            jwellsnh

            I did this with a combination of CSS and package functions.  Here is a demo.

             

            https://apex.oracle.com/pls/apex/f?p=82514:2

             

            Jeff

            • 3. Re: Adding a Sub Report to a Row as a sub report
              PKB_82

              Yes Jeff! Exactly like the Funding / Technical Report - Format wise anyway.

               

              Would you be so kind as to direct me on how that was achieved?

               

              Many thanks

               

              Paul

               

              Thanks for the link Tony, not quite but that will help with another issue I have.

              • 4. Re: Adding a Sub Report to a Row as a sub report
                jwellsnh

                Morning Paul,

                 

                There are 3 parts to this puzzle:

                1. CSS
                2. Function calls from the report SQL
                3. The functions themselves

                 

                <style type="text/css"><!--.inlineTable {
                    border: 1px solid #000000;
                    border-collapse: collapse;
                    width: 100%;
                }
                .inlineTable th {
                    color: #000000;
                    border: 1px solid #000000;
                    padding: 2px;
                }
                .inlineTable td {
                    border: 1px solid #000000;
                    border-left: none;
                    border-top: none;
                    padding: 2px;
                }
                /* additional formatting for the specific columns*/
                .inlineTable .colFundingSource {
                    width: 300px;
                    text-align: center;
                }
                .inlineTable .colJobNbr {
                    width: 60px;
                    text-align: center;
                }
                .inlineTable .colBLNbr {
                    width: 60px;
                    text-align: center;
                }
                .inlineTable .colBudget {
                    width: 120px;
                    text-align: right;
                }
                .inlineTable .colApproval {
                    width: 120px;
                    text-align: right;
                }
                .inlineTable .colItemAmt {
                    width: 120px;
                    text-align: right;
                }
                .inlineTable .colFundedAmt {
                    width: 120px;
                    text-align: right;
                }
                --></style><style type="text/css"><!--.inlineTech {
                    border: 1px solid #000000;
                    border-collapse: collapse;
                    width: 100%;
                }
                .inlineTech th {
                    color: #000000;
                    border: 1px solid #000000;
                    padding: 2px;
                }
                .inlineTech td {
                    border: 1px solid #000000;
                    border-left: none;
                    border-top: none;
                    padding: 2px;
                }
                /* additional formatting for the specific columns*/
                .inlineTech .colID {
                    width: 20px;
                    text-align: center;
                }
                .inlineTech .colQty {
                    width: 20px;
                    text-align: right;
                }
                .inlineTech .colManfName {
                    width: 80px;
                    text-align: center;
                }
                .inlineTech .colManfPartNbr {
                    width: 80px;
                    text-align: center;
                }
                .inlineTech .colDescription {
                    width: 400px;
                    text-align: leftr;
                }
                .inlineTech .colRenewDate {
                    width: 80px;
                    text-align: center;
                }
                .inlineTech .colStatus {
                    width: 80px;
                    text-align: center;
                }
                .inlineTech .colComment {
                    width: 300px;
                    text-align: leftr;
                }
                .inlineTech .colInstruction {
                    width: 2000x;
                    text-align: left;
                }
                .inlineTech .coltss {
                    width: 2000px;
                    text-align: left;
                }
                --></style>
                

                 

                WITH itemfunding 
                     AS (SELECT ae_request_header_tbl.request_id         AS REQUEST_ID, 
                                ae_request_detail_tbl.item_id            AS ITEM_ID, 
                                ae_request_detail_funding_tbl.funding_id AS FUNDING_ID 
                         FROM   ae_request_detail_funding_tbl, 
                                ae_request_detail_tbl, 
                                ae_request_header_tbl 
                         WHERE  ae_request_header_tbl.request_id = 
                                ae_request_detail_tbl.request_id 
                                AND ae_request_detail_tbl.item_id = 
                                    ae_request_detail_funding_tbl.item_id) 
                SELECT rid_item_funding_pck.Get_item_inline_f (request_id, item_id) item,
                       rid_item_funding_pck.Get_funding_inline_f (item_id, funding_id) funding
                FROM   itemfunding
                

                 

                The code won't copy correctly; I will continue on a second reply

                • 5. Re: Adding a Sub Report to a Row as a sub report
                  jwellsnh

                  Package Specification:

                  create or replace PACKAGE rid_item_funding_pck

                  AS

                     FUNCTION get_item_inline_f (request NUMBER, item NUMBER)

                        RETURN CLOB;

                   

                     FUNCTION get_funding_inline_f (item NUMBER, funding NUMBER)

                        RETURN CLOB;

                   

                  END rid_item_funding_pck;


                  Package Body:

                  create or replace PACKAGE BODY rid_item_funding_pck

                  AS

                     FUNCTION get_item_inline_f (request NUMBER, item NUMBER)

                        RETURN CLOB

                     IS

                        l_str   VARCHAR2 (32767);

                        l_cnt   NUMBER := 0;

                     BEGIN

                        l_str := '<table class="inlineTech">';

                        l_str :=

                              l_str

                           || '<tr>'

                           || '<th style="background-color:#5A99E8;" class="colID"><font color="FFFFFF"><strong>ID</strong></font></th>'

                           || '<th style="background-color:#5A99E8;" class="colQty"><font color="FFFFFF"><strong>Qty/Unit</strong></font></th>'

                           || '<th style="background-color:#5A99E8;" class="colManfName"><font color="FFFFFF"><strong>Manufacturer Name</strong></font></th>'

                           || '<th style="background-color:#5A99E8;" class="colManfPartNbr">Manufacturer Part #</th>'

                           || '<th style="background-color:#5A99E8;" class="colDescription">Description</th>'

                           || '<th style="background-color:#5A99E8;" class="colRenewDate">Renew Date</th>'

                           || '<th style="background-color:#5A99E8;" class="colOPS">OPS Status</th>'

                           || '<th style="background-color:#5A99E8;" class="colTSS">TSS Status</th>'

                           || '</tr>';

                   

                        FOR cur

                           IN (SELECT item_id id,

                                      quantity_amt || ' ' || unit_amt qty,

                                      manufacturer_nme manfname,

                                      manufacturer_part_number_cde manfpartnbr,

                                      description_dsc description,

                                      TO_CHAR (renew_dt, 'mm/dd/yyyy') renewdate,

                                      install_instructions_txt instruction,

                                      ops.status_title_nme ops_status,

                                      tss.status_title_nme tss_status

                                 FROM ae_request_detail_tbl,

                                      ae_status_lookup_tbl tss,

                                      ae_status_lookup_tbl ops

                                WHERE     request_id = request

                                      AND item_id = item

                                      AND ae_request_detail_tbl.ops_approval_status_cde =

                                             ops.status_id(+)

                                      AND ae_request_detail_tbl.network_approval_status_cde =

                                             tss.status_id(+))

                        LOOP

                           l_str := l_str || '<tr>';

                           l_str :=

                                 l_str

                              || '<td rowspan="2" class="colID">'

                              || cur.id

                              || '</td><td class="colQty">'

                              || cur.qty

                              || '</td><td class="colManfName">'

                              || cur.manfname

                              || '</td><td class="colManfPartNbr">'

                              || cur.manfpartnbr

                              || '</td><td class="colDescription">'

                              || cur.description

                              || '</td><td class="colRenewDate">'

                              || cur.renewdate;

                           l_str := l_str || '</td><td  class="colOPS">' || cur.ops_status;

                           l_str := l_str || '</td><td  class="colTSS">' || cur.tss_status;

                           l_str := l_str || '</td></tr>';

                           l_str := l_str || '<tr>';

                           l_str :=

                                 l_str

                              || '<td colspan="7" class="colInstruction">'

                              || cur.instruction;

                           l_str := l_str || '</td></tr>';

                   

                           l_cnt := l_cnt + 1;

                        END LOOP;

                   

                        l_str := l_str || '</table>';

                   

                        IF l_cnt = 0

                        THEN

                           RETURN '';

                        ELSE

                           RETURN l_str;

                        END IF;

                     END;

                   

                     FUNCTION get_funding_inline_f (item NUMBER, funding NUMBER)

                        RETURN CLOB

                     IS

                        l_str   VARCHAR2 (32767);

                        l_cnt   NUMBER := 0;

                     BEGIN

                        l_str := '<table class="inlineTable">';

                        l_str :=

                              l_str

                           || '<tr>'

                           || '<th style="background-color:#B6CEED; class="colFundingSource">Funding Source</th>'

                           || '<th style="background-color:#B6CEED; class="colJobNbr">Job #</th>'

                           || '<th style="background-color:#B6CEED; class="colBLNbr">BL #</th>'

                           || '<th style="background-color:#B6CEED; align="right" class="colBudget">Budget</th>'

                           || '<th style="background-color:#B6CEED; align="right" class="colApproval">Fund Approval</th>'

                           || '<th style="background-color:#B6CEED; align="right" class="colItemAmt">Total Item Amt</th>'

                           || '<th style="background-color:#B6CEED; align="right" class="colFundedAmt">Funded Amt</th>'

                           || '</tr>';

                   

                        FOR cur

                           IN (SELECT    fund_cde

                                      || '-'

                                      || agency_cde

                                      || '-'

                                      || org_cde

                                      || '-'

                                      || activity_cde

                                      || '-'

                                      || class_cde

                                      || '-'

                                      || object_cde

                                      || '-'

                                      || commodity_cde

                                         fundingsource,

                                      job_number_cde jobnbr,

                                      logical_link_nbr blnbr,

                                      TO_CHAR (budget_amt, '$9,999,999.00') budget,

                                      'Approved' approval,

                                      TO_CHAR (budget_amt, '$9,999,999.00') itemamt,

                                      TO_CHAR (budget_amt, '$9,999,999.00') fundedamt

                                 FROM ae_request_detail_funding_tbl

                                WHERE item_id = item AND funding_id = funding)

                        LOOP

                           l_str := l_str || '<tr>';

                           l_str :=

                                 l_str

                              || '<td class="colFundingSource">'

                              || cur.fundingsource

                              || '</td><td class="colcolJobNbr">'

                              || cur.jobnbr

                              || '</td><td class="colcolBLNbr">'

                              || cur.blnbr

                              || '</td><td class="colBudget">'

                              || cur.budget

                              || '</td><td class="colApproval">'

                              || cur.approval

                              || '</td><td class="colItemAmt">'

                              || cur.itemamt

                              || '</td><td class="colFundedAmt">'

                              || cur.fundedamt

                              || '</td>';

                           l_str := l_str || '</tr>';

                           l_cnt := l_cnt + 1;

                        END LOOP;

                   

                        l_str := l_str || '</table>';

                   

                        IF l_cnt = 0

                        THEN

                           RETURN '';

                        ELSE

                           RETURN l_str;

                        END IF;

                     END;

                   

                  END rid_item_funding_pck;

                  • 6. Re: Adding a Sub Report to a Row as a sub report
                    PKB_82

                    Many thanks for this, time to dive in and have a look,

                     

                    from what i understand thus far, you are using a package to produce the report as a  html block instead of an actual interactive report itself - I was trying to do it as a  true IR and modify the template to do it - is that right?

                    • 7. Re: Adding a Sub Report to a Row as a sub report
                      jwellsnh

                      That is correct Paul.  An IR wouldn't fit my requirements since the page has a lot more going one.

                       

                      Good luck on implementing this for your situation; let me know how you make out.

                       

                      Jeff

                      • 8. Re: Adding a Sub Report to a Row as a sub report
                        fac586

                        PKB_82 wrote:

                         

                        I'm new to ApEx and learning as I go, however an interesting question arose about a report.

                        When you have a question (especially an interesting one), please post using these guidelines: Re: how to get answers from forum

                         

                        Including this information enables us to suggest solutions that are appropriate to your environment quickly and effectively without wasting a lot of time on follow-up questions. For questions like this that involve UI, layout and visual formatting, sharing an example on apex.oracle.com is the best way to get help.

                        I can generate a report from a table and display it with no issue, however I want to display a sub report per row based on different criteria but linked to the primary report.

                         

                        Eg.

                         

                        I have columns of Customer, Number of Products, Cost

                         

                        Which displays;

                        Customer 1, 5, £25

                        Customer 2, 2, £10

                        etc

                         

                        What I want is to then display the actual products underneath eg.

                         

                        Customer 1, 5, £25

                        Cow, Dog, Monkey, Horse, Mouse

                         

                        Customer 2, 2, £10

                        Fish, Llama

                         

                        I understand i'll probably need to templates, and I could probably do with an IR and a submit to a linked page, but realistically it all needs to be on one page.

                        This can be done completely declaratively using a custom row report template with conditional row templates. This avoids the additional development and performance overheads entailed by using procedural PL/SQL code.

                         

                        See these previous threads for examples:

                         

                        • 9. Re: Adding a Sub Report to a Row as a sub report
                          jwellsnh

                          This can be done completely declaratively using a custom row report template with conditional row templates. This avoids the additional development and performance overheads entailed by using procedural PL/SQL code.

                          Thanks Paul for the indirect feedback, I was sure that was a more efficient way to accomplish this task.

                           

                          Jeff

                          • 10. Re: Adding a Sub Report to a Row as a sub report
                            PKB_82

                            Thanks for the feedback, however I couldn't phrase my requirements in such a way and i certainly couldn't set it up on apex.oracle.com as i've only been 'playing' with apex for a week or so, but it seems like its going to become a big part of my work life, I will have a look at the guidelines and try to adhere to them in the future

                            • 11. Re: Adding a Sub Report to a Row as a sub report
                              fac586

                              PKB_82 wrote:

                               

                              Thanks for the feedback, however I couldn't phrase my requirements in such a way and i certainly couldn't set it up on apex.oracle.com as i've only been 'playing' with apex for a week or so, but it seems like its going to become a big part of my work life, I will have a look at the guidelines and try to adhere to them in the future

                              There's nothing to stop you playing with it on apex.oracle.com, and then when you have a problem it's already there for someone to look at.

                               

                              In this case all you need is an apex.oracle.com workspace, a guest developer user, table(s) with data for the report you are creating, and a standard report...