"Unless we form the habit of going to the Bible in bright moments as well as in trouble, we cannot fully respond to its consolations because we lack equilibrium between light and darkness.”

Helen Keller

Case Study

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?

"Toleration is the greatest gift of the mind; it requires the same effort of the brain that it takes to balance oneself on a bicycle.”

Helen Keller

Tabular

Figure 1

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.

"The world is moved along, not only by the mighty shoves of its heroes, but also by the aggregate of tiny pushes of each honest worker.”

Helen Keller

Group Left

Figure 2

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.

"The marvelous richness of human experience would lose something of rewarding joy if there were no limitations to overcome. The hilltop hour would not be half so wonderful if there were no dark valleys to traverse.”

Helen Keller

Mailing Label

Figure 3

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.

"The highest result of education is tolerance.”

Helen Keller

Matrix Report

Figure 4

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.

"The best and most beautiful things in the world cannot be seen or even touched - they must be felt with the heart.”

Helen Keller

Form Letter Report

Figure 5

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.

“Security is mostly a superstition. It does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing.”

Helen Keller

Group above report

Figure 6

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 shareholders 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.

“The heresy of one age becomes the orthodoxy of the next.”

Helen Keller

Template

Figure 7

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.

“Optimism is the faith that leads to achievement. Nothing can be done without hope and confidence.”

Helen Keller

Layout Appearances (Boilerplate)

Figure 8

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.

“Once I knew only darkness and stillness... my life was without past or future... but a little word from the fingers of another fell into my hand that clutched at emptiness, and my heart leaped to the rapture of living.”

Helen Keller

Parameters

Figure 9

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.

“No pessimist ever discovered the secrets of the stars, or sailed to an uncharted land, or opened a new heaven to the human spirit.”

Helen Keller

“Never bend your head. Always hold it high. Look the world straight in the eye.

Helen Keller”

Helen Keller

Drill Down or Push Button Report

Figure 10

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 shareholders. Your report (Stock History) should have push button to call company’s shares holder from previous Hands-On (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.

“No matter how dull, or how mean, or how wise a man is, he feels that happiness is his indisputable right.”

Helen Keller

LOVs (List of Values)

Figure 11

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.

Answers