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.
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;
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.
Note: The ROW_NO column is used to order records internally by Info Services.