Reports Is Elf Schooling

Lesson 01 "Well-timed silence hath more eloquence than speech." - Martin Fraquhar Tupper Read first then play the video: RPT001(VIDEO)-Introduction to complete Hands-On 01 to 06

Case Study Introduction In this case study we look at reports, which will show you how to enhance your skills in using Oracle Report Builder. In these Hands-On, your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write them reports based on their reports layout requirements. Before using the Report Builder tool When you use the Report Builder tool, there are few modules that you may use them over and over. They are basics for your understanding how to use the tools. Object Navigator In the Report Builder tool, use the Object Navigator window, to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object. Within the Object Navigator window, you have: • Reports • Templates • External SQL Queries • PL/SQL Libraries • Debug Actions • Stack • Built-in Packages • Database Objects. Reports Object In the Reports object, you have other important objects that are very handy to create, delete, and modify a report such as Live Pre-viewer, Data Model, Layout Model, Parameter Form, Report Triggers, Program Units, and Attached Libraries. • Live Pre-viewer • Data Model • Layout Model • Parameter Form

• Report Triggers • Program Units • Attached Libraries Live Pre-viewer You use the Live Pre-viewer object to view what the report will look like when it is executed. Data Model You use the “Data Model” to display the data content and layout appearance of the report. It’s very important when you use this object that you understand SQL statement and relationship between tables such foreign key and primary key. Layout Model You use the Layout Model object to show you how your report will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships. You can add, modify or delete part of your layout from here. Parameter Form You use Parameter Form to define, remove, and modify your report parameters. You will use the “Parameter Form Builder” tool to design your runtime parameter form. There are two types of parameters: user parameters and system parameters. User Parameters “User parameters” will be used to manage the behavior and output of your reports. System Parameters “System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. Remember that when you run your report, the Runtime Parameter Form will be appeared. Report Triggers You use Report Triggers to define PL/SQL to fire at various points during the execution of your report. You will have: • BEFORE REPORT, • AFTER REPORT, • BETWEEN PAGES, • BEFORE PARAMETER FORM, and • AFTER PARAMETER FORM. Program units and Attached Libraries You can use Program Units and Attached Libraries as the same way we use them in the Forms Builder tool. Property Palette

In the course of our hands-on, you will see that we use Property Palette over and over. In fact you can’t do much without Property Palette. It is very important to understand it. It contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse then select Property Palette.

"Friends may come and go, but enemies accumulate." - Thomas Jones

Questions: Q: What is an Object Navigator in the Report Builder tool? Q: What does the Object Navigator contain? Q: What is a Report Object in the Object Navigator? Q: What is a Pre-viewer in the Report Builder? Q: What is a Data Model in the Report Builder? Q: What is a Layout Model in the Report Builder? Q: What is a Parameter Form in the Report Builder? Q: What are the types of parameters? Q: What is a user parameter? Q: What is a system parameter? Q: What are the report triggers? Q: Describe program units and attached libraries in the report builder? Q: Describe property palette in the report builder? Q: What is the property sheet for an object

Lesson 02 "It is time I stepped aside for a less experienced and less able man." - Professor Scott Elledge on his retirement from Cornell Read first then play the video: RPT002(VIDEO)-(Hands-On 01) How to write a Tabular report

Tabular Introduction Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format. Hands-on In this Hands-On, your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write the reports based on their reports layout requirements.

Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company The user layout format mask requirements are: For date MM-DD-RR (Year 2000 complaint). For dollars $99,999.90. For quantity NNN.N0. See Figure 1. Your tasks are: 1- Write a tabular report. 2- Apply user layout Format mask. 3- Run the report. 4- Test the repot. You will learn how to: use report wizard, object navigator, report builder, “date model”, property palette, work on query and group box, see report style, use tabular style, navigating through report’s record, change the format mask for dollar, numeric and date items.

Figure 1 Open Report Builder tool Open the "Report Builder" tool. Connect to database In the Object Navigator, highlight "Database Objects,” choose "File," then select the "Connect" option. In the ‘Connect’ window, login as “iself” password schooling, then click “CONNECT.”

Save a report In the Object Navigator, highlight the "untitled" report, choose “File,” and select the “Save as” option. In the ‘Save as’ window, make sure to save the report in the ISELF folder and name it "rpt01_stock_history,” report number 1 stock history. Data Model In the Object Navigator, double click on the "Data Model" icon. Create SQL box In the Data Model window, click on the "SQL Query" icon. Then drag the plus sign cursor and click it anywhere in the “Data Model” screen where you wish your object to be. In the ‘SQL Query Statement’ window, write a query to read all the stocks record sorted by their symbol. (SQL Query Statement) SELECT * FROM stocks ORDER BY symbol Click “OK.” Change SQL box’s name In the Data Model window, in the “SQL” box, right click on the ‘Q_1’ and open its property palette. In its property palette, change the name to Q_STOCKS. Then close the window. Change GROUP box’s name In the Data Model, right click on the group box (G_SYMBOL) and open its property palette. In the Group property palette, change the name to ‘G_STOCKS,’ and close the window. Open Report Wizard In the Data Model, click on the ‘Report Wizard’ icon on the horizontal tool bar. In the Style tab, on the Report Wizard window, type ‘Stock History’ in the Title box and choose the report style as ‘Tabular.’ Notice that when you change the report style a layout of that report will be displayed on the screen. Choose a different style to display its layout of its report style. Data, Fields, Totals, Labels and Template tabs Click “NEXT” to go to the Data tab. In the ‘SQL Query Statement’ verify your query. Click “NEXT” to navigate to the Fields tab, select the fields that you would like to be display in your report. Select all the columns to be display. Click “NEXT” to navigate to Totals tab, select the fields for which you would like to calculate totals. We have none in this hands-on exercise.

Click “NEXT” to open the Labels tab, modify the labels and widths for your fields and totals as desired. Click “NEXT” again to go to the Template tab, and choose a template for your report. Your report will inherit the template’s colors, fonts, line widths, and structure. Use the default template and click “finish.” Running a report Now, you should have your output report on the screen. Resize an object Maximize the output report and format the report layout. To resize an object , select it and drag its handler to the preferred size. Move an object To move an object, select and drag it while the cursor is on the object. This is a simple report. Navigate through the output To navigate through the output report in the Report Editor - Live Pre-viewer, click on the "next page" or "previous page" icon on the horizontal toolbar. Do the same with the "first page" or "last page" icon. Use the “zoom in” and “zoom out” icon to preview the report. Know report’s functions To know each icon functionalities, drag your cursor on it and a tooltip will display its function. Change Format Mask To change the "format mask" of a column, the column should be selected. Then go to the toolbar and click on the “$” icon, "add decimal place," and the “right justify” format to the all currency columns (Todays Low, Todays High, and current price) Select the “traded today” column, and click on the ‘,0’ icon (apply commas), and make it right justify. Also, you can change any attributes of field by opening its property palette. To open an object’s property palette, right click on it and select the Property Palette option. Right click on the "trade date" column and open its "property palette." Change the date "Format Mask" property and make it “year 2000 complaint (MM-DDRR).” Navigate through the report pages. Save a report Save the changes (rpt01_stock_history).

"Men and nations behave wisely once they have exhausted all the other alternatives." - Abba Eban (1915-2002)

Questions Q: How can you use the "Report Wizard" tool in the "Report Builder" tool? Q: How can you use the "Object Navigator" window? Q: How can you write a report? Q: How can you save a report? Q: How can you use the "Data Model" tool? Q: What is the "Property Palette" sheet? Q: What is the "Query" box in the "Data Model" window? Q: What is the "Group" box in the "Data Model" window? Q: How can you change a report style? Q: Use a tabular style for a report. Q: How can you navigate through your report? Q: How can you change a format mask for an item in the "Report Builder" tool? Q: What is a Tabular report? Q: Your client is a stock broker that keeps track of its customer stock transactions. You have been assigned to write the reports based on their reports layout requirements. Your client wants you to create a simple listing report to show list of the stock trades by using stocks table for their brokerage company The user layout format mask requirements are: For date MM-DD-RR (Year 2000 complaint). For dollars $99,999.90. For quantity NNN.N0. See Figure 1. Your tasks are: 1- Write a tabular report. 2- Apply user layout Format mask. 3- Run the report. 4- Test the repot

Lesson 03 "Copy from one, it's plagiarism; copy from two, it's research." - Wilson Mizner (1876-1933) Read first then play the video: RPT003(VIDEO)-(Hands-On 02) How to write a Group left report

Group Left Introduction Group-left reports are very useful for generating output reports from SQL statements with the group by clause. The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well.

Hands-on Your client asks you to define a control break on their stocks symbol and company name from previous report and also to print only one company on a page. See Figure 2. Your tasks are: 1- Modify Hands-On report 01 to the “Group Left” style. 2- Apply template to the user report. 3- Run the report. 4- Test the repot. You will learn how to: use report wizard, use report style “group left”, use template, use pre-viewer, use zoom in and out, use the body section in the layout model in the object navigator, and change repeating object’s property palette.

Figure 2 Open a Report Module In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (RPT01_STOCK_HISTORY) from the “iself” folder. Run a report Click on the Run icon to run the report. Maximize the window. Notice that the stock symbol and company name are repeated. We are going to create a control break to eliminate the duplication.

Report Wizard In the Report Editor, click on the Report Wizard icon on the toolbar. Select a Style In the Style tab on the Report Wizard, checkmark the “group left” radio button, then click next. Group-Left Group-left reports are very useful for generating output reports from SQL statements with the group by clause. The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well. Data, Groups, Fields, Totals, and Template tabs In the Data tab on the Report Wizard, click “Next” again. In the Groups tab on the Report Wizard, move the “symbol” and company name” item to the “group fields” box. Drag "company" to level 1, and then click “NEXT.” In the Fields tab on the Report Wizard, don’t change any fields. Click “Next” again. In the Totals in the Report Wizard, calculate the total, minimum, and maximum number of traded shares. Then click “Next.” In the Labels tab in the Report Wizard, change the label, if needed. Click “Next.” In the Template tab in the Report Wizard, use the default template and click “finish.” Navigate through a report Navigate through the report output. Notice that multiple companies are on each page. Change the report to print company’s information on a separate page. Open Repeating Group’s property palette On the Object Navigator, expand “Layout Model,” “Main Section”, then “BODY.” Right click on the first "Repeating Group" (R_G_1) to open its property palette. In its property palette, change "Maximum Records Per Page" to 1 and press the enter key, then close the window. Navigate through the report. Notice that each company is on a separate page. After testing the output report, go to the object navigator window, select the report and save the changes. Save the report as report number 2 (REPT02). A note to remember: In the Object Navigator, if you highlight the report and click on the ‘X’ icon (Delete) on the vertical toolbar, the object will be remove from work area and still a copy of that exist on your disk.

"A pessimist sees the difficulty in every opportunity; an optimist sees the opportunity in every difficulty." - Sir Winston Churchill (1874-1965)

Questions: Q: What is a Group-Left report? Q: How do you use the Report Wizard? Q: How many styles are in the Report Style window? Q: What is a template in the Report Builder? Q: When do you use pre-viewer in the Report Builder? Q: How do you zoon in and out in the Layout Editor? Q: What is the body section in the layout model? Q: How do you change the repeating object’s property palette? Q: Your client asks you to define a control break on their stocks symbol and company name from previous report and also to print only one company on a page. See Figure 2. Your tasks are: 1- Modify Hands-On report 01 to the “Group Left” style. 2- Apply template to the user report. 3- Run the report. 4- Test the repot

Lesson 04 "If Stupidity got us into this mess, then why can't it get us out?" " - Will Rogers (1879-1935) Read first then play the video: RPT004(VIDEO)-(Hands-On 03) How to write a Mailing Label report

Mailing Label Introduction A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa. Hands-on Your client has to mail their customers to inform them of the status of their current market value position. They ask you to create a report that outputs mailing labels. First they ask you to generate mailing labels with three columns and then change it to 1.5” X 3.5” label format. See Figure 3. Your tasks are: 1- Create a report to generate three column labels. 2- Extract customer’s name and address from their database. 3- Change the report to generate two column labels.

4- Apply template to the user report. 5- Run the report. 6- Test the repot. You will learn how to: use report style “mailing label,” use the “query builder,” generate three columns label, change the font and size, change it to two columns label, use handlers on the layout model.

Figure 3 Create a new report In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report. Report Wizard In the New Report window, choose the ‘Use the Report Wizard’ option, and click ‘OK.’ In the Report Wizard window, choose the Mailing Label Style, and click the ‘Next’ push button. Mailing Label A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa. In the ‘SQL Query Statement’ box, write a SQL statement to query all customers name from customers table. (SQL Query Statement) SELECT ALL first_name, last_name, address_line_1, address_line_2, address_line_3 FROM customers Then click Next.

You can create your mailing label by selecting fields and by typing directly into the Mailing Label text. Select the columns into "mailing label text." Use "New line", "space", "Dash", "comma", and "period" if you need them in the "mailing label text." Notice the following selection. (Mailing Label text) &, & & & & Click “NEXT.” Select the draft template; then click “NEXT,” and “Finish.” This is an example of a three column label. In the Live Pre-viewer, you can select the output object and change its font type and size. To change the font, go to toolbar that has B, I, U, type of font, and font size. It is at the top of the report. Change the mailing label Change the three column label to the two column label. In the Object Navigator, click on the “Layout Model” icon. Make the “Object Navigator” window and the “Layout Model” window side by side. Find a repeating group In the “Object Navigator,” expand "Layout Model," “Main Section,” “Body,” and select the repeating group (R_G_FIRST_NAME). The frame item can be selected either by using the “Object Navigator” or the “Layout Model” in the report editor. Drag the frame to 1.5 by 3.5 inches. Click on the text boilerplate. Notice the handlers; drag them to the "repeating group" frame. Select the text and change the font type and size. Run a report Click on the Run icon to run the report. This is an example of a two column label. You can select the customer name and change their font. Select the “untitled” report and save it as report number 3 (REPT03) in the "iself" folder

"The President has kept all of the promises he intended to keep." - Clinton aide George Stephanopolous speaking on Larry King Live

Questions: Q: Describe a Mailing Label report. Q: How do you use the query builder? Q: How do you generate columns label in the query builder? Q: How do you change a report’s font and size? Q: What are the handlers in the layout model? Q: Your client has to mail their customers to inform them of the status of their current market value position. They ask you to create a report that outputs mailing labels. First they ask you to generate mailing labels with three columns and then change it to 1.5” X 3.5” label format. See Figure 3. Your tasks are: 1- Create a report to generate three column labels. 2- Extract customer’s name and address from their database. 3- Change the report to generate two column labels. 4- Apply template to the user report. 5- Run the report. 6- Test the repot

Lesson 05 "There's many a bestseller that could have been prevented by a good teacher." - Flannery O'Connor (1925-1964) Read first then play the video: RPT005(VIDEO)-(Hands-On 04) How to write a Matrix report

Matrix Report Introduction A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results. Hands-on You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock. This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer

and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row. They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total. See Figure 4. Your tasks are: 1- Create a matrix report. 2- Put grid around each cell. 3- Use user layout format mask for cells and sub-totals as NNN,NN0. 4- Calculate the subtotal for rows and columns. 5- Highlight the cells with any color (ex: red) if their values are significant. 6- Highlight the sub-total cells with any color (ex: pink) if their values are significant. 7- Apply template to the report. 8- Run the report. 9- Test the repot. You will learn how to: use query builder, set table relationship, change properties from property palette, use report style matrix, make subtotal for rows and columns, use the “select parent frame” icon, create grid, use the conditional formatting.

Figure 4 Create a new report In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report. Build a new report Manually

In the New Report window, choose the ‘Build a new report manually’ option, and click ‘OK.’ Create a SQL box In the ‘Data Model,’ click on the SQL icon on the vertical toolbar. Drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object. In the ‘SQL Query Statement’ box, write a SQL statement to query all customers with their stocks’ symbol and number of shares owned. (SQL Query Statement) SELECT last_name, stock_symbol, shares_owned FROM portfolio WHERE ( id = customer_id) Then click ‘OK.’ Change a SQL box’s property In the Data Model window, right click on the SQL box (Q_1) and open the property palette option. In the Property Palette window, change the name to Q_PORTFOLIO. Then close the window. Report Wizard In the Data Model window, on the toolbar, select the Report Wizard icon. Matrix Report A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results. Style, Data, Rows, Columns, Cell, Totals, and Template tabs In the Style tab in the Report Wizard, choose the report style as Matrix with a title of ‘Customer Portfolio.’ Then click on the ‘Next’ icon. In the Data tab, select the data that you will use in your report. You should have already had that SQL statement. Don’t change anything and click Next. In the Rows tab, select LAST_name as a "Matrix Row Field" and click Next. In the Columns tab, use “Stock_symbol” as a "Matrix Column Field" and click Next. In the Cell tab, select the sum of the shares_owned as a "Matrix Cell Fields" and click Next. In the Totals tab, to make subtotals for rows and columns, select SumShares_OWNED to calculate the sum of rows or columns {Sum(SumShares_OWNED)}. Then click Next. In the Labels tab, delete the label for the sum of the shares owned, change Last_name to name, change stock_symbol to symbol, and then click Next. In the Template tab, use ‘Cyan Grid’ template report and click ‘Finish.’ Navigate through a report Now, you created a customers portfolio report. Navigate through the report.

Layout Report Editor Use the layout report editor to change the report layout. Layout Model Click on the “Layout Model” icon on the top of the horizontal toolbar. In the Layout Model window, you can change the size of each item. Use the "select parent frame" icon to go to the parent's column and resize it. When finished resizing, click on the “run” icon to run the report. Live Pre-viewer In the Live Pre-viewer, select the name item and then change its alignment to left or right. You can also right click on it, open its property palette, and change its alignment. Change appearance of a report Select the cells and change their alignment and format mask. Do the same for the columns' subtotals. Navigate through the report. Conditional Formatting In the Live Pre-viewer, right click on the cells, and open the "Conditional Formatting" option. In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color. In the Conditional Formatting window, click “New” and In the Format Exception window, change the shares owned value to red if it is greater than or equal 2000. SHARES_OWNED >= 2000 Click “OK.” Check the report. Go to the last page, change the alignment and mask format for the row's subtotals; and change the color if their values are more than 7,000. Go to the first page. Do the same for the columns' subtotal; and change the color if their values are more than 10,000. After testing the report output, highlight the report and save it as report number 4 in the iself folder (REPT04).

"I criticize by creation - not by finding fault." - Cicero (106-43 B.C.)

Questions:

Q: What is a Matrix Report? Q: How do you set a table relationship in the report builder? Q: How do you change an object using its properties palette? Q: How can you make a sub-total for rows and columns in a matrix report? Q: What does the Select Parent Frame icon? Q: What is a conditional formatting in the report builder? Q: You client needs to get the summary report of their customers shares owned. They like to have the output format be the same as spreadsheet format. You are assigned to create a grid style of data output as a spreadsheet, with rows that present customers name and columns that present stock’s symbol. The intersection of these two entities is a cell that shows the number of shares that customer has on that stock. This report should show the number of shares of stock’s holders by each customer in each of the stocks. Make sure the cell format mask is NNN,NN0. You should use the customer and portfolio table, and put grid around each number of shares for easy reading. You should also get the totals for each column and row. They want you to change the cell color to red if its value is greater than 2000 and do the same for rows and columns sub-total. See Figure 4. Your tasks are: 1- Create a matrix report. 2- Put grid around each cell. 3- Use user layout format mask for cells and sub-totals as NNN,NN0. 4- Calculate the subtotal for rows and columns. 5- Highlight the cells with any color (ex: red) if their values are significant. 6- Highlight the sub-total cells with any color (ex: pink) if their values are significant. 7- Apply template to the report. 8- Run the report. 9- Test the repot

Lesson 06 "For centuries, theologians have been explaining the unknowable in terms of the-not-worth-knowing." - Henry Louis Mencken (1880-1956) Read first then play the video: RPT006(VIDEO)-(Hands-On 05) How to write a Form Letter

Form Letter Report Introduction The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings. Hand-on Your client needs to write a letter to all its customers who own Oracle shares for good news of split of their shares. You are asked to write a “Form Letter” to send to each

customer who own Oracle (orcl) shares. You are supposed to notify them of a stock split and tell them the number of shares the broker hold for them, their size of the split and the post-split amount and their current stock market values. See Figure 5. Your tasks are: 1- Create a “Form Letter” report. 2- Extract customer’s information from their database. 3- Generate the report only for Oracle’s share holders. 4- Apply template to the user report. 5- Run the report. 6- Test the repot. You will learn how to: use the report style “form letter,” and the “query builder,” set table relationships, define column from the “query builder,” use sub-query, use of data model, layout model, use of boilerplate, write the letter and pass the column to the letter.

Figure 5 Create a new report In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report. Use the Report Wizard In the New Report window, choose the ‘Use the Report Wizard’ option, and click ‘OK.’ Choose a report style In the Report Wizard, choose the Form Letter style and click Next.

Form Letter The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings. SQL Query Statement In the SQL Query Statement, write a SQL statement to query customers’ information (Name and address), and their portfolio information (Number of shares, double_shares_owned, and current stock market value). (SQL Query Statement) SELECT ALL first_name, last_name, address_line_1, address_line_2, address_line_3, Shares_owned, shares_owned * 2 as double_shares_owned, (shares_owned * 2) * current_price as stock_market_value FROM customers, portfolio, stocks WHERE (( id = customer_id) and ( stock_symbol = symbol)) and trade_date = ( SELECT max(trade_date) FROM stocks Then click Next. Create a Form Letter Create your form letter by selecting fields and by typing directly into the Form Letter text. (Form Letter Text) & & & & & Dear &< FIRST_NAME>, Please …. &<SHARES_OWNED> & &<STOCK_MARKET_VALUE> We selected all the columns that will be used in the letter. In the Report Wizard, select the template of your choice and click on “FINISH.” In this hands-on, use the default template. Data Model In the Live Pre-viewer, click on the “Data Model” icon. Right click on the “Q_1” and open its property palette and change its name to Q_CUSTOMER. Report Editor In the Data Model, click on the Report Editor icon to open the “Report Editor.” Make the Object Navigator and the Report Editor side by side.

Resizing a object In the “Object Navigator,” expand "Layout Model," “Main Section,” “Body,” and select the repeating group (R_G_FIRST_NAME). The frame item can be selected either by using the “Object Navigator” or the “Layout Model” in the report editor. Drag the frame to a size of letter page. Click on the text boilerplate in the Body item. Notice the handlers; drag them to the "repeating group" frame. Write a letter In the Report Editor, write the company letter. (Letter) & & & & & Dear &< FIRST_NAME>, Please be advised that the Oracle Company (ORCL) has announced a stock split for shareholders of record. The split will be 2 for 1. Our records indicate that you now hold &<SHARES_OWNED> after the split that amount will be &. Please contact us if this amount does not agree with your record. Your stock’s market value as of today is: &<STOCK_MARKET_VALUE> Thank you, Customer Service. Run the report. Navigate through the report. You can also change the text on the “live pre-viewer.” Notice that the number of shares got double. Put a dollar sign ($) next to the stock market values. Navigate through all the Oracle's stock's holders. After testing the output report, highlight the report and save it as report number 5 in the iself folder (REPT05).

“Education's purpose is to replace an empty mind with an open one.” Malcolm Forbes (1919 - 1990), in

Forbes Magazine

Questions: Q: What is a Form Letter report? Q: When do you use the query builder tool? Q: How do you set a table relationship in the Layout Module? Q: How do you define a column from the Query Builder? Q: How do you use the Data Model tool? Q: How do you use the Layout Model tool? Q: What is the boilerplate in the Layout Editor? Q: Your client needs to write a letter to all its customers who own Oracle shares for good news of split of their shares. You are asked to write a “Form Letter” to send to each customer who own Oracle (orcl) shares. You are supposed to notify them of a stock split and tell them the number of shares the broker hold for them, their size of the split and the post-split amount and their current stock market values. See Figure 5. Your tasks are: 1- Create a “Form Letter” report. 2- Extract customer’s information from their database. 3- Generate the report only for Oracle’s share holders. 4- Apply template to the user report. 5- Run the report. 6- Test the repot

Lesson 07 “The direction in which education starts a man will determine his future life.” Plato (427 BC - 347 BC), The Republic Read first then play the video: RPT007(VIDEO)-(Hands-On 06) How to use Group Above

Group above report Introduction A Group-Above report is a style you use to demonstrate a master/detail relationship in your database. You should define a master group, and for each master record, the report obtains the related values from the detail groups. Hands-on Your users want to send to their customers their portfolio information at the end of each month. Based on their requirements, you should create a master-detail report that shows the portfolio holdings of each customer of the stock broker company, including their current stock market value.

As a user requirement, you should use format mask NNN,NN0 for shares owned and $99,999.09 for their stock market value. You should also print each customer stock’s information on a separate page so company can mail them to its customer. See Figure 6. Your tasks are: 1- Write a “Group above” report. 2- Print detail share holders record (symbol, shares owned, and its current market value. 3- Apply user layout Format mask. 4- Calculate the customer’s current stock market value. 5- Run the report. 6- Test the repot. You will learn how to: use a master-detail report, build a new report manually, create multiple “SQL Query,” create data link, use the formula column, use the summary column, use the report “group above” style, use object navigator, layout model, main section, and use property palette

Figure 6 Group-Above A Group-Above report is a style you use to demonstrate a master/detail relationship in your database. You should define a master group, and for each master record, the report obtains the related values from the detail groups. Open the Reports Builder. In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report.

Build a new report Manually In the New Report window, choose the ‘Build a new report manually’ option, and click ‘OK.’ Create a SQL box In the ‘Data Model,’ click on the SQL icon on the vertical toolbar. Drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object. In the ‘SQL Query Statement’ box, write a SQL statement to query all customers. (SQL Query Statement) SELECT * FROM customers Then click ‘OK.’ Create a second SQL box In the ‘Data Model,’ click again on the SQL icon on the vertical toolbar. Drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object. In the ‘SQL Query Statement’ box, write a SQL statement to query all customers’ portfolio. (SQL Query Statement) SELECT * FROM portfolio Then click ‘OK.’ Change SQL boxes’ property Right click on the ‘Q_1’ SQL box, and open its property palette. In its property palette, change its name to Q_CUSTOMERS, and close the window. Right click on the ‘G_ID’ Group box, and open its property palette. In its property palette, change its name to G_CUSTOMERS, and close the window. Right click on the ‘Q_2’ SQL box, and open its property palette. In its property palette, change its name to Q_PORTFOLIO, and close the window. Right click on the ‘G_CUSTOMER_ID’ Group box, and open its property palette. In its property palette, change its name to G_PORTFOLIO, and close the window. Create a database relationship In the Report Editor, click on the ‘Data Link’ icon on the vertical toolbar. Notice that the cursor is changed to a cross. Move the cross cursor to the "ID" item and click and drag it to the "customer_id" item of the portfolio group. Now, the link was established. Create a Control Break Drag the "customer_id" item to the outside of the group box. This will create a control break on customer_id. Create a Formula Column

Click on the "Formula Column" icon. Drag the cross to the Group portfolio and click on it again. Right click on it and open its property palette. Change the name to CF_1 to CF_MARKET_VALUE. In the Property Palette, double click on the PL/SQL Formula box. PL/SQL block for Formula Column In the PL/SQL window, write a PL/SQL block to calculate the customer stock market value. Get the current price for the current stock then calculate the market value. (PL/SQL) FUNCTION cf_market_valueFormula RETURN NUMBER IS v_current_price NUMBER: BEGIN SELECT current_price INTO v_current_price FROM stocks WHERE trade_date = (SELECT max(trade_date) FROM stocks) AND Symbol = :stock_symbol; RETURN :shares_owned * v_current_price; END; Compile the PL/SQL block Compile it. It should be successfully compiled. Close the PL/SQL window. Close the property palette. Create a Summary Column In the Data Model window, select the Summary Column icon and move the cross cursor to the "group customer id" and click on the box. Right click on it (CS_1), and open its property palette. In the property palette window, change name to CS_TOTAL_MARKET_VALUE, change "source" to "CF_MARKET_VALUE," change the "Reset At" to the "G_CUSTOMER_ID." Then close the window. Style, Group, Fields, Labels, and Template tabs In the Data Model window, click on the Report Wizard icon. In the Style tab on the Report Wizard window, type the title ‘Customer Portfolio,’ and checkmark the "Group above" style. Then click Next. In the Group tab, move all the items to "displayed groups" using the down arrow option. Click on the ‘Next’ push button. In the Field tab, move last_name, stock_symbol, shares_owned, cf_market_value, and cs_total_market_value to "Displayed fields." Click Next.

In the Label tab, change the Labels if needed. Click “NEXT.” In the Template tab, select a template, and then click “Finish.” Change appearance of the report In the Live Pre-viewer, change the column size and their format mask. Align their labels. Highlight the “Last Name” label and press the "delete" function key. Align the customer last name. Change its color. Create a Control Break on customer Go to the "Object Navigator." Expand the "Layout Model" item. Expand the Body item. Right click on the first repeating group and open its Property Palette from the “Body” item. In its property palette, change "Maximum Records Per Page" to 1, and then close the window. Now, it is one customer per page. Navigate through the output report. Make an adjustment if needed. Navigate through it again. After testing the report output, highlight the report and save the changes as the report number 6 (REPT06).

"We succeed only as we identify in life, or in war, or in anything else, a single overriding objective, and make all other considerations bend to that one objective." Dwight D. Eisenhower (1890 - 1969), speech, April 2, 1957

Questions: Q: Describe a GROUP-ABOVE report in the report builder. Q: How do you use a Master-Detail report in the Data Model? Q: How do you create multiple SQL queries in the Data Model? Q: How do you create a data link in the Data Model? Q: What is the formula column and how do you create it? Q: What is the summary column and how do you create it? Q: What is a main section in the Object navigator? Q: Your users want to send to their customers their portfolio information at the end of each month. Based on their requirements, you should create a master-detail report that shows the portfolio holdings of each customer of the stock broker company, including their current stock market value. As a user requirement, you should use format mask NNN,NN0 for shares owned and $99,999.09 for their stock market value.

You should also print each customer stock’s information on a separate page so company can mail them to its customer. See Figure 6. Your tasks are: 1- Write a “Group above” report. 2- Print detail share holders record (symbol, shares owned, and its current market value. 3- Apply user layout Format mask. 4- Calculate the customer’s current stock market value. 5- Run the report. 6- Test the repot.

Lesson 08 “There is nothing like dream to create the future. Utopia to-day, flesh and blood tomorrow.” Victor Hugo (1802 - 1885), Les Miserables, 1862 Read first then play the video: RPT008(VIDEO)-Introduction to complete Hands-On 07 to 11 RPT009(VIDEO)-(Hands-On 07) How to create and use Customized Template

Template Introduction Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your. Hands-on They need their logo to be displayed at each new or old report. They ask you to create a template that contains their logo. Then run a report. See Figure 7. Your tasks are: 1- Write a template containing your client logo. 2- Run the report against the new created template. 3- Test the report. You will learn how to: create or modify an existing template, save the template with new name, change the default, delete the logo, replace the new logo, save your template.

Figure 7

Open a Report Builder Login to the Report Builder. Connect to a database In the Object Navigator, highlight the Database object, go to the Main menu, choose File, select the Connect option, and in the Connect window enter your USERID and PASSWORD. Open a Report Module In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT06) from the “iself” folder. Open an existing Template In the Object Navigator, highlight the “Template” item, choose File from the Main menu, and select the Open option to open an existing template. Templates Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your. You can find all the Templates in you PC or Share Server. Look for all the objects that their extensions are ‘.tdf.’ Select one that are very similar to your template layout, and start from there. In this hands-on, the selected template is in: C:.tdf It is easier to change an existing “template” rather than to create it from scratch.

Save a template Open the template and save it as a new name (ISELF_TEMPLATE) in the "ISELF" folder. Modify a template In the Object Navigator, double click on the "Layout Model" icon of ISELF_TEMPLATE. In the ‘Template Editor - Layout Model’ window, you can zoon-out or zoon-in to view the template; and then click on the Down Arrow next to the “Report Style” box to select your report style. You report style can be: Tabular, Group Left, Group Above, Form-like, Mailing Label, Form Letter, matrix, and Matrix with Group. Notice that a template has so many different report styles. To view them, click on each report style and browse through them. Import company logo In this hands-on, select the default template in the Template editor and delete the report layout logo and import your own organization logo. To import, go to the Main menu, and choose File > Import > Image… In the Import Image window, select your image file, its format and Quality. The click OK. Resize company logo In the Template Editor, resize your logo if needed. If you are done with your template layout, save the template. To save the template, highlight ISLEF_TEMPLATE and then click on the Save icon on the toolbar. Or, go to the Main menu, choose File > Save option. Save Template and remove it from workstation Once you save the template, you can delete it from your Object Navigator. Highlight ISELF_TEMPLATE, click on the ‘X’ icon (Delete). This will remove the object from Object Navigator and the template is saved. Then remove the template from the "object navigator." Now, you can run the REPT06 using the new template. Make sure that you are in the REPT06 report. Run the report with its old template. Assign the new template In the Live Pre-viewer, click on the "Report Wizard" icon. In the ‘Report Wizard’ window, open the Template tab. Your template is not in the Predefined template; therefore, you should us ‘Template file’ by check marking its radio button. Type or find your template and then click ‘Apply’ and/or ‘Finish.’ Run the report Now, your report has its new template. Navigate through the report pages. After testing the report output, highlight the report and save the changes as the report number 7 (REPT07).

“The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.” William James (1842 - 1910)

Questions: Q: What are the templates in the report module? Q: How can you create or modify an existing template? Q: Your client needs their logo to be displayed at each new or old report. They ask you to create a template that contains their logo. Then run a report. See Figure 7. Your tasks are: 1- Write a template containing your client logo. 2- Run the report against the new created template. 3- Test the report.

Lesson 09 “You can tell the character of every man when you see how he receives praise.” Seneca (5 BC - 65 AD), Epistles Read first then play the video: RPT010(VIDEO)-(Hands-On 08) How to use layout appearances (Boilerplate)

Layout Appearances (Boilerplate) Hands-on Now, you client needs to improve on report appearance. They ask you for any artistic changes. Be creative and artistic to change the appearance of their reports. Use boilerplate to change the title and draw graphic to their reports. Your client wants you to use your imagination that has no limits. See Figure 8. Your tasks are: 1- Modify the appearance of a report. 2- Use your creative imagination. 3- Run the report. 4- Test the report. You will learn how to: use boilerplate; use your imagination that has no limit.

Figure 8 Login to the Report Builder. Connect to a database In the Object Navigator, highlight the Database object, go to the Main menu, choose File, select the Connect option, and in the Connect window enter your USERID and PASSWORD. Open a Module In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT07) from the “iself” folder. Run the report. Layout Model In the Live Pre-viewer, click on the "Layout Model" icon. In the ‘Report Editor - Layout Model’, click on the "Edit Margin" icon on the horizontal toolbar. Your report heading margin will be appeared. You can change anything on that margin. Change appearance of a report Use your creative imagination and change the report title. There is no limit on your creativities. By now, you should know how to use the Boiler plate objects. We have done that in one of Forms’ hands-on. Review that and do the same here. Run the report. Navigate through the report pages. After testing the report output, highlight the report and save the changes as the report number 8 (REPT08).

“Life is like playing a violin in public and learning the instrument as one goes on.” Samuel Butler (1835 - 1902)

Questions: Q: What is the boilerplate in the Layout Editor? Q: How do you select an object or objects in the layout editor? Q: How do you move an object or objects in the layout editor? Q: How do you delete an object or objects form the layout editor? Q: When do you use the Edit Margin icon? Q: Now, your client needs to improve on report appearance. They ask you for any artistic changes. Be creative and artistic to change the appearance of their reports. Use boilerplate to change the title and draw graphic to their reports. Your client wants you to use your imagination that has no limits. See Figure 8. Your tasks are: 1- Modify the appearance of a report. 2- Use your creative imagination. 3- Run the report. 4- Test the report.

Lesson 10 “We make a living by what we get, we make a life by what we give.” Sir Winston Churchill (1874 1965) Read first then play the video: RPT011(VIDEO)-(Hands-On 09) How to use Parameters

Parameters Introduction There are two types of parameters: user parameters and system parameters. User Parameters “User parameters” will be used to manage the behavior and output of your reports. System Parameters “System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. Parameter Form Builder “Parameter Form Builder” tool will be used to design your runtime parameter form. The Runtime Parameter Form appears whenever you run your report.

Hands-on The user wants to execute this report just against a specific company’s symbol. They want to use company’s symbol as a parameter to pass to the report. Also the report should calculate the total and average of shares sold for the company at the end of report. You should create a simple report to list the name of customers including their shares owned. See Figure 9. Your tasks are: 1- Create a new portfolio report for each company. 2- Use parameter to run the report for a specific symbol. 3- Print the list of their customers and their number of shares. 4- Print the total and average of shares sold for the company. 5- Run the report. 6- Test the report. You will learn how to: use the “group above,” use the “query builder,” set table relationship, create and use binding variable, calculate total and average shares, delete the object from layout.

Figure 9 Open the Report Builder. Create a New Report In the Object Navigator, highlight the Reports item, and click on the "create" icon (Green ‘+’) to create a new report. Use the Report Wizard In the New Report window, choose the ‘Use the Report Wizard’ option, and click ‘OK.’

Select a style In the Report Wizard, type ‘Customer List’ in the Title box, choose the ‘Group Above’ style and click Next. Group-Above A Group-Above report is a style you use to demonstrate a master/detail relationship in your database. You should define a master group, and for each master record, the report obtains the related values from the detail groups. SQL Query Statement In the SQL Query Statement, write a SQL statement to query customers’ information (Name), and their portfolio information (Number of shares, and stock’s symbol) with an binding variables to be used as parameter field. (SQL Query Statement) SELECT ALL first_name, last_name, stock_symbol, shares_owned FROM customers, portfolio WHERE id = customer_id and stock_symbol = :symbol Use of a Colon The colon next to the symbol (:symbol) means: pass a symbol as a parameter to the report. Click OK. You get This message: Note: The query ‘Q_1’ has created the bind parameter(s) ‘SYMBOL’. Click "OK" to confirm it. Group fields In the Report Wizard, in the Group Field section, select the "Stock_Symbol" item as a "Group Fields." Display Fields In the Display Fields section, select all the items to display in your report. You may add other fields later. Total and Average … Calculate the total and average shares sold for a company (Sum(SHARES_OWNED), Average(SHARES_OWNED)). Label and Template Sections In the Label section, modify the labels and widths for your fields and totals as desired. In the Template section, use the default template and click Next. In the congratulation section, click Finish to run the report. Parameter Form Prompt

The parameter form will be opened. In the Runtime Parameter Form, enter the company’s symbol such as ORCL, MSFT, etc. The click on the Run icon to run the report. Remove an object or objects Hold the control key and click on the duplicate Total and Average objects. Then press the "delete" function key to remove them. Objects will be removed. Format Masking and Layout adjustment Do all the items format mask and layout adjustment, if needed! Navigate through the report pages. After testing the report output, highlight the “UNTITLED” report and save the changes as the report number 9 (REPT09). Run a report and pass a parameter In the Object Navigator on the vertical toolbar, click on the ‘Run’ icon to run the report with different company symbol. In the Runtime Parameter Form, enter the company’s symbol that does not exist. The click on the Run icon to run the report.

“There is no remedy for love but to love more.” Henry David Thoreau (1817 - 1862), Journal, July 25, 1839

Questions: Q: How do you create a binding variable? Q: How do you use a binding variable? Q: How do you remove an object from Layout Editor? Q: The user wants to execute this report just against a specific company’s symbol. They want to use company’s symbol as a parameter to pass to the report. Also the report should calculate the total and average of shares sold for the company at the end of report. You should create a simple report to list the name of customers including their shares owned. See Figure 9. Your tasks are: 1- Create a new portfolio report for each company. 2- Use parameter to run the report for a specific symbol. 3- Print the list of their customers and their number of shares. 4- Print the total and average of shares sold for the company. 5- Run the report. 6- Test the report.

Lesson 11 “Age does not protect you from love. But love, to some extent, protects you from age.” Jeanne Moreau Read first then play the video:

RPT012(VIDEO)-(Hands-On 10) How to use Drill Down or Push Button report

Drill Down or Push Button Report Introduction A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form. Hands-on You client likes what you did on the previous Hands-On and ask you to use the report on the “Stock History” report. This should give them an option to get customers list (Customers List) for each company. They ask you to create a drill down report on the company’s stock information so they get more detailed information about that company’s shares holders. Your report (Stock History) should have push button to call company’s shares holder from previous HandsOn (Customers List). The report should show for the security shown on the screen all customers of that security, the amount of their position, and the total number of shares held. See Figure 10. Your tasks are: 1- Modify the “Stock History” report. 2- Add the Push Button option to call the “Customers List” report. 3- Write a trigger to execute the “Customers List” report. 4- Run the report using “Runtime Pre-viewer.” 5- Test the report. You will learn how to: use push button to execute another report, use or pass parameter, use the “select parent frame” icon, use or open objects property, compile report, use the boilerplate, write and compile PL/SQL trigger, use “runtime pre-viewer.”

Figure 10 Drill-Down Report A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form. Open the Reports Builder tool. Open a Report Module In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT02) from the “iself” folder. Save a Report Module Save it as report number 10 (REPT10). You do this to leave the REPT02 module untouched. Run a report In the Object Navigator, on the vertical toolbar, click on the ‘Run’ icon to run the report. Browse a report Navigate through the report. Notice that there are black spaces under the SYMBOL and COMPANY column. That is a perfect place to add a push button to list the company's shares holders. Create a push button To do that: In the Live Pre-viewer, click on the "Layout Model" icon. In the ‘Report Editor - Layout Model’ window, select the company item, and click on the "Parent Frame" icon until the handlers do not change. Then drag the handler to make more free space to work with.

Once again, click on the COMPANY item, and click only once on the "Parent Frame" icon. Then drag the handler as far as the first one. Click on the Push Button icon on the vertical toolbar and move the cross cursor to the “Layout Editor” under the SYMBOL and COMPANY columns and then click and drag to make a push button on a suitable size. Right click on it and open its property. In its property palette, type “Customer's list” on the text box, change the “Type” item to "PL/SQL” and then double click on the “PL/SQL trigger’ item. PL/SQL for push button A Program Unit window will be opened. In its PL/SQL section, write a PLSQL statement to call the previous report that was created from the previous hands-on. Use the binding variable to pass the company symbol as a parameter. Write all the exception. (PL/SQL) PROCEDURE U_1ButtonAction IS BEGIN -- make sure that REPT09 has already been compiled successfully. srw.run_report (‘c:PARAMFORM=NO SYMBOL=’ || :symbol) EXCEPTION WHEN srw.run_report_failure THEN srw.message (1002, ‘Could not run the Detail Report Customers List’); WHEN others THEN srw.message (1003, ‘Unknown problem, please see your developer.’); END; Compile the PL/SQL block Compile the trigger. If the output is successfully compiled, then close the window. Close the property palette. Close the “Layout Model” window. Compile a report Make sure that the REPT09 has already been compiled. (File > Administration > Compile file…) Run the new report. Runtime Pre-viewer To test the Push Button, you should use the "Runtime Pre-viewer” option. To use the "Runtime Pre-viewer” option, go to the Main menu, choose the ‘View’ sub-menu, select the ‘Runtime Pre-view…’ option, and the Pre-viewer screen will be appeared. Notice that your Push Button color was changed. Now, click on "Customer's List" to run the report.

Navigate through the report pages and then click on the Customer's List to see the company's shares holders. After the testing was done, close the windows and save the changes (REPT10).

“Slow down and enjoy life. It's not only the scenery you miss by going too fast - you also miss the sense of where you are going and why.” Eddie Cantor (1892 - 1964)

Questions: Q: Describe a drill-down report. Q: How do you use a push button in the report? Q: How do you execute a report form another report? Q: How do you pass parameter from one report to another? Q: How do you compile a report? Q: How do you write a PL/SQL trigger? Q: How do you compile a PL/SQL trigger? Q: You client likes what you did on the previous Hands-On and ask you to use the report on the “Stock History” report. This should give them an option to get customers list (Customers List) for each company. They ask you to create a drill down report on the company’s stock information so they get more detailed information about that company’s shares holders. Your report (Stock History) should have push button to call company’s shares holder from previous HandsOn (Customers List). The report should show for the security shown on the screen all customers of that security, the amount of their position, and the total number of shares held. See Figure 10. Your tasks are: 1- Modify the “Stock History” report. 2- Add the Push Button option to call the “Customers List” report. 3- Write a trigger to execute the “Customers List” report. 4- Run the report using “Runtime Pre-viewer.” 5- Test the report.

Lesson 12 “Love all, trust a few. Do wrong to none.” William Shakespeare (1564 - 1616) Read first then play the video: RPT013(VIDEO)-(Hands-On 11) How to use LOVs (List Of Values)

LOVs (List of Values)

Introduction A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements. Hands-on Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol. Also add more parameter to pass such as the destination of printer, and so on. See Figure 11. Your tasks are: 1- Modify the “Customers List Parameter” Form. 2- Add LOV to the “symbol” item. 3- Add one more parameter “Destination Type” to the “customers List Parameter” Form. 4- Run the report. 5- Test the report. You will learn how to: use parameter, modify parameter properties using property palette, use query builder, and add more parameters to existing parameter form.

Figure 11 List of Values A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements. Open the Report Builder tool.

Open a Module In the ‘Object Navigator’ window, go to the Main menu and choose “File,” select “Open” to open an existing report (REPT09) from the “iself” folder Save a Module Save it as report number 11 (REPT11). You do this to leave the REPT09 module untouched. (File > Save as) In the Object Navigator, on the vertical toolbar, double click on the "Layout Model" icon." Open Parameter Form Builder window In the ‘Report Editor - Layout Model’ window, click on the "Parameter Form" icon which is on the horizontal toolbar. The Report Editor - Parameter Form window, will be opened. In the Parameter Form window, click on the "Parameter Form Builder" icon which is on the horizontal toolbar. Add objects to Parameter Form The Parameter Form Builder window will be displayed. In the ‘Parameter Form Builder’ window, change the Title item to ‘Customer’s Symbol List,’ click on the DESTYPE (Destination Type) parameter to select it, notice that the “Symbol” parameter has already been selected, and then click "OK." Now in your ‘Report Editor - Parameter Form,’ you should see only two parameters such as Destination type and Symbol (PF_DESTYPE and PF_SYMBOL). Change a Fields’ properties Right click on the PF_SYMBOL field and open its property palette. In its parameter palette, type “ORCL” in the ‘Initial Value’ box, and double click on the "List of Values" box. In the ‘Parameter List of Value’ window, checkmark the “Select Statement” radio button and type the following SQL statement into the ‘SQL Query Statement’ box. SELECT DISTINCT symbol FROM stocks Click "OK," close the property window, and then close “Parameter Form Builder.” Run a report In the Object Navigator, highlight the report and click on the Run icon on the toolbar to run the report. A parameter window will be displayed. Now, you can generate your report on a different destination type such as Screen, File, Printer, Mail, and Preview.

Select your prefer destination type, your company’s symbol, and click on the Run icon to run the report Repeat this process for different companies. At the end, highlight the report and save the changes (REPT11).

“It is pretty hard to tell what does bring happiness; poverty and wealth have both failed.” Kin Hubbard (1868 - 1930)

Questions: Q: Describe a List of Values in the report module? Q: How do you create a user parameter item? Q: How do you define a system parameter item? Q: How do you modify a user parameter properties using property palette? Q: How do you use the Query Builder tool? Q: Since your parameter report is very handy for your client organization, they ask you to create a list of values (LOV) for the parameter so they can only select from the list rather than trying to remember the company symbol. Also add more parameter to pass such as the destination of printer, and so on. See Figure 11. Your tasks are: 1- Modify the “Customers List Parameter” Form. 2- Add LOV to the “symbol” item. 3- Add one more parameter “Destination Type” to the “customers List Parameter” Form. 4- Run the report. 5- Test the report.

Lesson 13 “Education is the ability to listen to almost anything without losing your temper or your self confidence.” Robert Frost (1874 - 1963)

IMPORTANT-READ CAREFULLY Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format. Group-left reports are very useful for generating output reports from SQL statements with the group by clause. The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well. In Hands-On 02, you will experience how to generate a group left report.

A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa. In Hands-On 03, you will experience how to generate a Mailing Label report. A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results. In Hands-On 04, you will experience how to generate a Matrix report. The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings. In Hands-On 05, you will learn how to generate a Form Letter report. A Group-Above report is a style you use to demonstrate a master/detail relationship in your database. You should define a master group, and for each master record, the report obtains the related values from the detail groups. The “Data Model” will be used to display the data content and layout appearance of the report. The “Live Pre-viewer” will be used to view what the report will look like when it is executed. Object Navigator will be used to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object. Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse then select Property Palette. Templates are generic objects that allows you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your. Layout Model shows you how your report will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships. There are two types of parameters: user parameters and system parameters.

“User parameters” will be used to manage the behavior and output of your reports. “System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. “Parameter Form Builder” tool will be used to design your runtime parameter form. The Runtime Parameter Form appears whenever you run your report. A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form. A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements. The Object Navigator will be used to view report components and navigate through the report objects. It gives you a hierarchical listing of all the modules open in your current session. You use this listing to navigate to, and work on, those objects. It gives you access to all database objects you own or have grant to, as well as a list of all the built-in functions and procedures you can use in Oracle Form Builder. Clicking on the plus sign next to an object in the Object Navigator will expand the object and clicking on the minus sign will collapse the object. A Property Palette contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. You use the Property Palette to modify object properties. To open the Property Palette of an object, go to the object and right click on the mouse, then select property palette.

Lesson 14 REPORTS Final Exam Evaluation “Finish each day and be done with it. You have done what you could; some blunders and absurdities have crept in; forget them as soon as you can. Tomorrow is a new day; you shall begin it serenely and with too high a spirit to be encumbered with your old nonsense.” --Ralph Waldo Emerson “Everyone is trying to accomplish something big, not realizing that life is made up of little things.” -Frank A. Clark

Questions and Answers: Q: What is an Object Navigator in the Report Builder tool? A: The Object Navigator window will be used in the Report Builder tool, to view report components and navigate through the report objects. It gives us a hierarchical listing of all the modules open in our current session. We use this listing to navigate to, and work on, those objects. It gives us access to all database objects we own or have grant to, as well as a list of all the built-in functions and procedures we can use in Oracle Form Builder. Q: What does the Object Navigator contain? A: The Object Navigator contains the following items: • Reports

• Templates • External SQL Queries • PL/SQL Libraries • Debug Actions • Stack • Built-in Packages • Database Objects. Q: List all important objects that are handy in the Reports object. A: The following are a list of important objects in the Reports object. These objects are very handy to create, delete, and modify a report. • Live Pre-viewer • Data Model • Layout Model • Parameter Form • Report Triggers • Program Units • Attached Libraries Q: What is a Pre-viewer in the Report Builder? A: The Live Pre-viewer object is a tool that provides developer to view what the report will look like when it is executed. Q: What is a Data Model in the Report Builder? A: The “Data Model” in the Report Builder is a tool that will provide developer to display the data content and layout appearance of the report. Q: What is a Layout Model in the Report Builder? A: The Layout Model object is a utility in the Report Builder that shows users how their reports will appear. When you run your report, Report Builder fills in the objects in the Layout Model with actual data. The Layout Model will show you several types of layout objects and the relationships that exist between them. The Layout Model uses symbols and graphical representations to show these relationships. You can add, modify or delete part of your layout from here. Q: What is a Parameter Form in the Report Builder? A: The Parameter Form in the Report Builder is a utility that provides developers how to define, remove, and modify their report parameters. They will use the “Parameter Form Builder” tool to design their runtime parameter form. Q: What are the types of parameters in the Report Builder? Describe them. A: There are two types of parameters: user parameters and system parameters. The “User parameters” will be used to manage the behavior and output of your reports. The “System parameters” will be used to manage the report runtime behavior of your reports such as defining your output destination. Remember that when you run your report, the Runtime Parameter Form will be appeared.

Q: What are the report triggers? A: The Report Triggers are a small unit of a program that developers write. They will be used to define PL/SQL to fire at various points during the execution of your report. You will have: • BEFORE REPORT, • AFTER REPORT, • BETWEEN PAGES, • BEFORE PARAMETER FORM, and • AFTER PARAMETER FORM. Q: Describe program units and attached libraries in the report builder? A: The “Program units” section will be used by developers to write their PL/SQL programs. The developers use the “PL/SQL libraries” to store their stored procedures and “Attached libraries” to access to those created stored procedures. You will use Program Units and Attached Libraries as the same way we use them in the Forms Builder tool. Q: Describe property palette in the report builder? A: The Property Palette in the Report Builder contains object properties. The contents of the Property Palette are referred to as the “Property sheet” for the object. We use the Property Palette to modify object properties. To open the Property Palette of an object, we can go to the object and right click on the mouse then select Property Palette. Q: What is the property sheet for an object? A: The contents of the Property Palette are referred to as the “Property sheet” for the object. Q: What is the "Query" box in the "Data Model" window? A: It refers to a SQL statement. Q: What is the "Group" box in the "Data Model" window? A: It refers to a control break or grouping rows. A SQL statement can have one single group referring to its entire table or more than one by using the GROUP BY statement. Q: What is a Tabular report? A: A Tabular report shows data in a table format. It is similar in concept to the idea of an Oracle table. Oracle, by default, returns output from your select statement in tabular format. Q: What is a Group-Left report? A: The Group-left report suppresses redundant printings of the same value in the leftmost column of the report. It is like making a control break in your report. You can use the “Group-left” report on a master-detail relationship as well. Q: Describe a Mailing Label report. A: A Mailing Label report is a list of addresses designed to be generated on mailing labels. The addresses can be printed across the page, and then down or vice versa. Q: What are the handlers in the layout model?

A: The handlers are small square boxes that have been attached to a selected object. You can use those handlers to change the size of an object by dragging the handlers. Q: What is a Matrix Report? A: A matrix report is a chart with two axes (rows and columns) that display for sets of data. On the rows, the report displays one set of data, while on the columns the report displays another set. Within the two axes, report displays a cross-product of results. Q: How do you set a table relationship in the report builder? A: In the Report Editor, click on the ‘Data Link’ icon on the vertical toolbar. Notice that the cursor is changed to a cross. Move the cross cursor to the one item (the item that needs a link) then click and drag it to another item (the item that needs a link) of the other group. Now, you establish and set a table relationship between these two tables. Q: What does the Select Parent Frame icon? A: The "Select Parent Frame" icon selects the parent frame of the selected object. The position of the selected object (handlers) will change if the object has a parent. Q: What is a conditional formatting in the report builder? A: In the Live Pre-viewer, if you right click on the cells, and open the "Conditional Formatting" option, you can change the display output of the object. In the Conditional Formatting window, you can define exceptions to highlight data for specified conditions with different formatting such as color. Q: What is a Form Letter report? A: The Form letter report will complement the mailing label report. You can pull several database columns plus your mailing message to produce form mailings. Q: What is the boilerplate in the Layout Editor? A: The boilerplate utility in the Layout Editor will be used by developers to change the text fond or color and draw graphic on a report. Q: Describe a GROUP-ABOVE report in the report builder. A: A Group-Above report is a style you use to demonstrate a master/detail relationship in your database. You should define a master group, and for each master record, the report obtains the related values from the detail groups. Q: How do you use a Master-Detail report in the Data Model? A: You use a Master-Detail report in the Data Model to generate a control break report. Q: How do you create multiple SQL queries in the Data Model? A: In the Data Model, you can create as many SQL queries as you need. To create a SQL query, just click on the SQL icon on the vertical toolbar, drag the ‘+’ sign in to the Data Model and click any where that you wish to have your object. Then in the ‘SQL Query Statement’ box, write your SQL statement. Q: How do you create a data link in the Data Model?

A: In the Report Editor, click on the ‘Data Link’ icon on the vertical toolbar. Notice that the cursor is changed to a cross. Move the cross cursor to the one item (the item that needs a link) then click and drag it to another item (the item that needs a link) of the other group. Now, you establish and set a table relationship between these two tables. Q: What is the formula column and how do you create it? A: The Formula Column is an object that a developer defines to establish his/her own formula. To create it, click on the "Formula Column" icon in the Data Model, drag the cross to the Group object and click on it again. Then right click on it, open its property palette, and change its name. In the Property Palette, double click on the PL/SQL Formula box. In the PL/SQL window, write a PL/SQL block to calculate your formula. Q: What is the summary column and how do you create it? A: The summary column is an object that a developer defines to establish the SUM, AVG, MIN, etc functions on a table. To create it, go to the Data Model window, select the Summary Column icon, move the cross cursor to the "group customer id" and click on the box. Then right click on it, and open its property palette. In the property palette window, change its name, change its "source," and change the "Reset At" options." Q: What are the templates in the report module? A: The Templates are generic objects that allow you to force a set of standard features and attributes onto your reports. They help you impose uniformity on your. Q: How do you select an object or objects in the layout editor? A: To select an object, click on the object and you will see the handlers (small boxes) attached to the object. To select multiple objects, move the cursor outside of all the objects that needs to be selected and click and drag the cursor to put all the objects in a box, and then release the cursor. Now, all the objects should have handlers attached to them. Q: How do you move an object or objects in the layout editor? A: Take the cursor on the selected object(s)--in side of its handlers--and then drag the object(s). Q: How do you delete an object or objects form the layout editor? A: Select an object and click on the delete key. Q: When do you use the Edit Margin icon? A: In the ‘Report Editor - Layout Model’, click on the "Edit Margin" icon on the horizontal toolbar. The report heading margin will be appeared. Now, we can change anything on that margin. Q: How do you create and use a binding variable? A: A colon next to an item means that the item is a binding variable. For example to define a binding variable, see the following SQL statement. SELECT ALL first_name, last_name FROM customers, portfolio

WHERE id = :id; (:id is a binding variable) Q: Describe a drill-down report. A: A drill-down report lets you get more detailed information at a different level of data aggregation. The most common methods of drilling-down use the mouse or a button to call another report, graph, or form. Q: Describe a List of Values in the report module? A: A “List of Values”, can be used as a validation mechanism for values defined in user parameters. Your LOV or “List of Values” can consist either of static values or items queried from the database using select statements. Q: How do you create a user parameter item? A: A colon next to an item means that the item is a user parameter. For example to define a user parameter, see the following SQL statement. SELECT ALL first_name, last_name FROM customers, portfolio WHERE id = :id; (:id is a binding variable) Q: How do you define a system parameter item? A: In the ‘Report Editor - Layout Model’ window, click on the "Parameter Form" icon which is on the horizontal toolbar. The Report Editor - Parameter Form window will be opened. In the Parameter Form window click on the "Parameter Form Builder" icon which is on the horizontal toolbar. The Parameter Form Builder window will be displayed. In the ‘Parameter Form Builder’ window, change the Title item if you wish and then click on the DESTYPE (Destination Type) parameter, etc to select system parameters.