iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Developers - REPORTS

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 |

 

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:

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

 

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