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. 3. Deploy this server code to the Oracle Server. 4. 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. 5. Add the MASTER and SUMMARY views at the Standard Report Creation Wizard dialog and click Next >.
6. 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.
7. 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. 
8. 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.
9. 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)
10. Select the IFS_RESULT_KEY parameter field of the parent report as the linked field between the two reports.
11. 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…) 
12. Save the report layout without data ( Save Data with Report option unchecked).