1 / 28

Week 8 March 22

Week 8 March 22. Creating a Matrix and Drill-down/Roll-up Report. Create the SQL. Tables. SQL. Create the Data Model. Report Wizard. . Select Matrix. Report Wizard. . Report Wizard. . Rows. Report Wizard. . Columns. Report Wizard. . Report Wizard. . Report Wizard. .

kaz
Download Presentation

Week 8 March 22

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Week 8March 22 Creating a Matrix and Drill-down/Roll-up Report

  2. Create the SQL Tables SQL

  3. Create the Data Model

  4. Report Wizard  Select Matrix

  5. Report Wizard

  6. Report Wizard  Rows

  7. Report Wizard  Columns

  8. Report Wizard

  9. Report Wizard

  10. Report Wizard  Change to small column width

  11. Report Wizard

  12. Initial Report Layout Column totals Redundant

  13. Report Layout Eliminate

  14. Layout Model Remove

  15. Layout Model Push button Separate frames

  16. Push Button Property Palette Leave at least a space

  17. Live Previewer

  18. Programming the Drill-Down/Roll-Up Report Master Report (Annual) Data Model 1 Parameter-list1 Parameters names cannot be the same! Drill-down Report (Quarters) Data Model 2 Parameter-list2 Drill-down Report (Months) Data Model 2

  19. p_class_description p_year From previous report

  20. Transfer values from parameters and columns to local variables Transfer values from parameters and columns to local variables Parameter names should be different from those defined for this report Warning! Following this code verbatim will be a detriment to your grade

  21. p_year p_class_description p_quarter3 From previous report

  22. Creating a Lexical Reference Variable • Create a lexical reference variable under User Parameters of the Data Model (in the Object Navigator)  Select  Create

  23. Create a User Parameter  Select the new parameter, right-mouse click and select Property Palette

  24. Change the Properties Change the name Change the data type to character Assign an initial value (optional)

  25. Warning! Add after creating the initial data and layout models Enlargement select manufacturer_name, to_char(sales_month_98,'fmmm') Sequence, to_char(sales_month_98,'Mon') Month, &p_revenue3, &p_volume3 from sales_97_98 s, manufacturers m where s.manufacturer_code = m.manufacturer_code and m.manufacturer_code = upper(:p_manufacturer_code3) and to_char(sales_month_98,'q') = :p_quarter3 and s.product_class_code = upper(:p_product_class_code3) group by manufacturer_name, to_char(sales_month_98,'fmmm'), to_char(sales_month_98,'Mon') order by to_char(sales_month_98,'fmmm'); Lexical reference variables Otherwise, ORDER BY ignored

  26. During Runtime... Host variable Lexical reference variables (default values) Host variable

More Related