Example of creating a report accessing multiple report views

This example describes how to create a Crystal reports report, which uses multiple views created against the report archive. These multiple views may be created for master data set and for child data set. The example shows the Demo Order Overview report with its master data, details and summary data in three separate views created against the INFO_SERVICES_RPT table.

Modify the report definition (.rdf) file to create these views.

View definition for Demo Order Overview

PROMPT Creating report view &VIEW
CREATE OR REPLACE VIEW &VIEW AS
SELECT RESULT_KEY RESULT_KEY,
       ROW_NO ROW_NO,
       s1 COMPANY_ID,
       n1 ORDER_ID,
       d1 ORDER_DATE,
       s2 NAME
FROM &TABLE
WHERE s4 = 'MASTER'
WITH read only;

-- view comments and column comments for &VIEW

PROMPT Creating report view &DETAILS_VIEW

CREATE OR REPLACE VIEW &DETAILS_VIEW AS
SELECT RESULT_KEY RESULT_KEY,
       ROW_NO ROW_NO,
       n1 ORDER_ID, 
       s3 DESCRIPTION,
       n2 PRICE,
       n3 QUANTITY,
       n4 AMOUNT,
       n5 ROW_NUMBER,
       n6 DISC_AMOUNT
FROM &TABLE
WHERE s4 = 'DETAIL'
WITH read only;
-- view comments and column comments for &DETAILS_VIEW

PROMPT Creating report view &SUMMARY_VIEW

CREATE OR REPLACE VIEW &SUMMARY_VIEW AS
SELECT RESULT_KEY RESULT_KEY,
       ROW_NO ROW_NO,
       n1 ORDER_ID,
       n7 SUM_DISC_AMOUNT,
       n8 SUM_AMOUNT,
       n8 - n7 TOTAL_DISCOUNT
FROM &TABLE
WHERE s4 = 'SUMMARY'
WITH read only;

Change the insert statements of the report method so that the row type and the order_id are inserted for each record.

PROCEDURE &METHOD (
	report_attr_    IN VARCHAR2,
	parameter_attr_ IN VARCHAR2 )
IS
   result_key_        NUMBER;
   row_no_            NUMBER := 1;
   parent_row_no_     NUMBER := 0;
   company_id_        VARCHAR2(100);
   order_id_          VARCHAR2(2000);
   order_item_row_no_ NUMBER;
   sum_amount_        NUMBER;
   sum_disc_amount_  NUMBER;

   CURSOR get_master IS
   SELECT company_id,
          order_id,
          order_date,
          Demo_Customer_API.Get_Name(company_id,customer_id) customer_name
   FROM   demo_order
   WHERE  Report_SYS.Parse_Parameter(company_id, company_id_) = 'TRUE'
   AND    Report_SYS.Parse_Parameter(order_id, order_id_) = 'TRUE';

   CURSOR get_details(comp_id_ IN VARCHAR2, ord_id_ IN NUMBER) IS
          SELECT order_id,
          description,
          price,
          quantity,
          price * quantity amount,
          amount disc_amount
   FROM   demo_order_item
   WHERE  company_id = comp_id_
   AND    order_id = ord_id_;
BEGIN
   result_key_ := Client_SYS.Attr_Value_To_Number(Client_SYS.Get_Item_Value('RESULT_KEY', report_attr_));
   company_id_ := Client_SYS.Get_Item_Value('COMPANY_ID', parameter_attr_);
   archive_variable_api.set_object(result_key_, 'vCompanyLogo',
   demo_company_api.get_logo_file_name(company_id_));
   order_id_ := Client_SYS.Get_Item_Value('ORDER_ID', parameter_attr_);

   FOR master IN get_master LOOP
      INSERT INTO &TABLE (
           result_key,
           row_no,
           parent_row_no,
           s4,
           s1,
           n1,
           d1,
           s2 )
      VALUES (
           result_key_,
           row_no_,
           0,
           'MASTER',
           master.company_id,
           master.order_id,
           master.order_date,
           master.customer_name
           );

   parent_row_no_ := row_no_;
   row_no_ := row_no_ + 1;
   order_item_row_no_ := 1;
   sum_amount_ := 0;
   sum_disc_amount_ := 0;
 
   FOR detail IN get_details(master.company_id, master.order_id) LOOP
      INSERT INTO &TABLE (
            result_key,
            row_no,
            parent_row_no,
            n1,
            s4,
            n5,
            s3,
            n2,
            n3,
            n4,
            n6 )
      VALUES (
            result_key_,
            row_no_,
            0,
            detail.order_id,
            'DETAIL',
            order_item_row_no_,
            detail.description,
            detail.price,
            detail.quantity,
            detail.amount,
            detail.disc_amount
            );
    sum_amount_ := sum_amount_ + detail.amount;
    sum_disc_amount_ := sum_disc_amount_ + detail.disc_amount;
    order_item_row_no_ := order_item_row_no_ + 1;
    row_no_ := row_no_ + 1;
   END LOOP;
   INSERT INTO &TABLE (
            result_key,
            row_no,
            parent_row_no,
            n1,
            s4,
            n8,
            n7 )
   VALUES (
            result_key_,
            row_no_,
            0,
            master.order_id,
            'SUMMARY',
            sum_amount_,
            sum_disc_amount_
          );
      row_no_ := row_no_ + 1;
   END LOOP;


END &METHOD;

Note: For clarity the method logic was not changed here. It is not a must that you have to change the logic, but it is recommended to change the logic where possible for performance.

Deploy this server code to the Oracle Server.
The Crystal Reports report will have a main report with MASTER and SUMMARY views and a sub report with the DETAIL view. Follow steps 1 to 2 in Example of Creating a Basic Report for Crystal Reports Plug-in to start the new report.
Add the MASTER and SUMMARY views at the Standard Report Creation Wizard dialog and click Next >.
Since you have selected more than one view for this report the "Standard Report Creation Wizard" will have an additional step called "Links". Rearrange the links between views columns properly. You may have to delete some or all of the default links. For the example report ROW_NO link should be removed.

Note: The ROW_NO column is used to order records internally by Info Services.
Select the columns to be shown in the report from all the tables. You do not need to select the linked fields unless they should be shown in the report. For the example report you can select Finish now, as it does not have any groupings in this case.
Follow steps 7 to 11 in "Example of Creating a Basic Report for the Crystal Reports Plug-in" to start the new report. In step 11 the layout of the report is arranged. The detail rows of the report will be inserted as a sub report linked to the "DETAILS" view.
Insert a new subreport (Insert/Subreport…). Give a name to the subreport and select Report Wizard… . Create and layout the sub report linked to the details view (DEMO_ORDER2_DETAILS_REP in the case of example report)
Select the IFS_RESULT_KEY parameter field of the parent report as the linked field between the two reports.
Place the new sub report inside the detail block and set the layout of the sub report. It is also required to edit the selection formula of the sub report. (Report\Selection Formulas\Record…)
Save the report layout without data (Save Data with Report option unchecked).