godok.id

Tutorial ScenarioFigure 1–2 Adding Data to an HTML Page2.In Chapter 3, "Reviewing the Source Code of the Web Report", you will review theresulting report to analyze what the steps you completed in Chapter 2, "AddingData to a Report" did to the sample Web page to help you understand whatReports Builder did to the sample Web page. You can compare the sample Webpage we have provided with the resulting JSP-based Web page. Here, you will beable to examine how the data model looks in Reports Builder, and how it looks inXML.3.In Chapter 5, "Creating a Report Block for the Web Report", you will use theReport wizard to add a report block to the JSP and generate a simple JSP-basedWeb report to your Web browser.Figure 1–3 Creating a Report Block for your JSP-based Web Report4.In Chapter 4, "Reviewing the Source Code for the Report Block", you will analyzethe Web source of your report to review how the report block was inserted intoyour JSP-based Web report. Again, you will examine the XML code to see how thesource code has changed.5.In Chapter 6, "Creating a Graph for the Web Report", you will use the Graphwizard to add a graph to the JSP, then generate the completed JSP report to yourWeb browser.1-2 Oracle Reports Tutorial

SummaryFigure 1–4 Adding a Graph to a JSP-based Web Report6.In Chapter 7, "Reviewing the Source Code for the Graph", you will review the newcode that Reports Builder added to your Web source to produce the graph.7.In Chapter 8, "Generating a Paper Report", you will generate a paper report basedon the same data model and layout that you created for the JSP-based Web report.Figure 1–5 Generating a Paper Report from a JSP-based Web Report Data Model1.2 SummaryNow that you know what this tutorial covers, you can go on to Chapter 2, "AddingData to a Report" to learn how to create a data model for your report using the DataWizard.Tutorial Overview 1-3

Summary1-4 Oracle Reports Tutorial

2Adding Data to a ReportEstimated completion time: 15 minutesConsider the scenario where you have to create a Web report for your company’shuman resources managers. This Web report must display salary information abouteach employee in a department. You already have an HTML page that matches thecompany’s logo and colors. You need to add the necessary information to this page.This chapter shows you how to use the Data Wizard in Reports Builder to add data toan existing HTML page as depicted in Figure 2–1. At the end of the chapter, you willhave created a data model to make data available for use in a Web report.Figure 2–1 Adding Data to an HTML Page2.1 Opening the Web PageThe steps in this section show you how to open the Web page template we haveprovided, called emprev.htm. First, you will open the Web page in your Webbrowser so that you can see what our template looks like.If you do not have this file, refer to the Prerequisites section,in the Preface.Note:To open the sample HTML page in your Web browser In your Web browser (for example, Netscape or Internet Explorer), choose File Open, and navigate to emprev.htm. The emprev.htm file is the sample file thatwe have provided in this Tutorial.Adding Data to a Report2-1

Using the Data Wizard to Add Data to a Sample Web PageTo open an existing HTML page in Reports Builder1.Open Reports Builder.If you are using UNIX, navigate to the directory whereReports Builder is installed, and then execute runbuilder.sh.Note:2.In the Welcome dialog box, click Open an existing report, then click OK.3.In the Open dialog box, navigate to the folder where the tutorial sample files arelocated. For example, d:\Reports Tutorial.4.Find the emprev.htm file and click Open.The EMPREV report is displayed in the Object Navigator.Figure 2–2 Object Navigator Displaying an Existing HTML Page2.2 Using the Data Wizard to Add Data to a Sample Web PageWhen building a report, you must first build a data model by selecting the data thatyou want to use in the report.The steps in this section describe the procedure to use the Data Wizard to build a datamodel for your Web report. This data model makes the data from the sample schemaavailable to use in your report.To add data to an existing HTML page1.Open the Data Wizard to define a layout and add a data model.a.In the Object Navigator, double-click the Data Model node.b.In the Data Model view, right-click the canvas, then choose Data Wizard fromthe pop-up menu.2.If the Welcome page displays, click Next.3.On the Query name page, click Next to accept the default name.4.On the Data Source page, make sure SQL Query is selected, then click Next.On the Data Source page, you can choose any data source youwish to use in your report. For more information about usingpluggable data sources, see Oracle Reports online Help.Note:2-2 Oracle Reports Tutorial

Using the Data Wizard to Add Data to a Sample Web Page5.On the Data page, you can do either of the following: 6.Open the tutorial sql.txt file in a text editor, copy the query, andpaste it into the SQL Statement box. Then, proceed to Step 16.Learn to use the Query Builder. To do this, follow steps 6 to 15.To use the Query Builder, click Query Builder.If you do not know the connection information for thedatabase that contains the sample schema we have provided, contactyour administrator.Note:7.In the Query Builder, double-click the EMPLOYEES table.8.Double-click the EMPLOYEES table again, then click Close.Figure 2–3 Employees Tables Displayed in the Query BuilderThe EMPLOYEES table displays in the Query Builder as EMPLOYEES andEMPLOYEES A1. The link between MANAGER ID in EMPLOYEES andEMPLOYEE ID in EMPLOYEES A1 is automatically created because of theconstraints that exist in the EMPLOYEES table.You will notice that some of the column names are bold, andsome are italicized. Column names that are in bold are primary keysand column names that are in italics are foreign keys.Note:9.In the EMPLOYEES table, select the check boxes for the following fields (you mustselect the fields in the following order): EMPLOYEE ID FIRST NAME LAST NAME HIRE DATE JOB ID SALARY DEPARTMENT ID10. In the EMPLOYEES A1 table, select the check boxes for the following fields: EMPLOYEE IDAdding Data to a Report2-3

Using the Data Wizard to Add Data to a Sample Web Page FIRST NAME LAST NAME11. Click OK.12. The query that Query Builder generates is displayed in the SQL Query Statementtext box, and should look like the following:SELECT ALL EMPLOYEES.EMPLOYEE ID, EMPLOYEES.FIRST NAME,EMPLOYEES.LAST NAME, EMPLOYEES.HIRE DATE, EMPLOYEES.SALARY, EMPLOYEES.DEPARTMENT ID, EMPLOYEES A1.EMPLOYEE ID, EMPLOYEES A1.JOB ID, EMPLOYEES A1.FIRST NAME, EMPLOYEES A1.LAST NAMEFROM EMPLOYEES, EMPLOYEES EMPLOYEES A1WHERE (EMPLOYEES.MANAGER ID EMPLOYEES A1.EMPLOYEE ID)13. To restrict the retrieved data to only the employees in Department 100, add anAND clause, so that your query looks like this (new code is in bold text):SELECT ALL EMPLOYEES.EMPLOYEE ID, EMPLOYEES.FIRST NAME,EMPLOYEES.LAST NAME, EMPLOYEES.HIRE DATE, EMPLOYEES.SALARY, EMPLOYEES.DEPARTMENT ID, EMPLOYEES A1.EMPLOYEE ID, EMPLOYEES A1.JOB ID, EMPLOYEES A1.FIRST NAME, EMPLOYEES A1.LAST NAMEFROM EMPLOYEES, EMPLOYEES EMPLOYEES A1WHERE (EMPLOYEES.MANAGER ID EMPLOYEES A1.EMPLOYEE ID)AND EMPLOYEES.DEPARTMENT ID 10014. Let us make the report look more organized by displaying the employee nameswith their last names first.Concatenate the EMPLOYEES.FIRST NAME and EMPLOYEES.LAST NAMEfields into an alias called emp name.The line of the query should now look like this:, EMPLOYEES.LAST NAME ', ' EMPLOYEES.FIRST NAME emp name15. Concatenate the EMPLOYEES A1.FIRST NAME and EMPLOYEES A1.LASTNAME fields into an alias called mgr name.The line of the query should now look like this:, EMPLOYEES A1.LAST NAME ’, ' EMPLOYEES A1.FIRST NAME mgr name16. Verify that your query is correct. The entire query should now look like this:SELECT ALL EMPLOYEES.EMPLOYEE ID,EMPLOYEES.LAST NAME ',' EMPLOYEES.FIRST NAME emp name, EMPLOYEES.HIRE DATE,EMPLOYEES.JOB ID, EMPLOYEES.SALARY, EMPLOYEES.DEPARTMENT ID, EMPLOYEESA1.EMPLOYEE ID,EMPLOYEES A1.LAST NAME ',' EMPLOYEES A1.FIRST NAME mgr nameFROM EMPLOYEES, EMPLOYEES EMPLOYEES A1WHERE (EMPLOYEES.MANAGER ID EMPLOYEES A1.EMPLOYEE ID)AND EMPLOYEES.DEPARTMENT ID 1002-4 Oracle Reports Tutorial

Saving your Report as a JSP FileIf you are not sure whether your query is correct, open thetutorial sql.txt file from the example files we provided to you.Note:17. Click Next.18. Now that we have selected the raw data, let us organize this data into groups bydepartment manager name.On the Groups page, in the left column, click the MGR NAME field, click theright arrow to move the field to the Group Fields list, then click Next.19. Now, let us calculate some of our data. The Totals page displays some of thecommonly-used calculations.On the Totals page, in the left column, click SALARY, then click Sum to displaythe sum of the Salary column in the Totals list.20. Click Finish.Figure 2–4 Data Model View for the ReportA report-level summary displays in the upper left-handsection of the Data Model view, but is not shown in this image.Note:2.3 Saving your Report as a JSP FileIn Reports Builder, you can save your report using several formats. Since we arecreating a JSP-based Web report, we will save our report in the JSP format.To save your report as a JSP1.Select your report in the Object Navigator, then choose File Save As.2.In the Save dialog box, change the report name to emprev your initials,change the type to Reports JSP (.jsp), and then click Save.Adding Data to a Report2-5

SummaryBe sure to include your initials so that you do not overwritethe files we have provided. Since JSP is the primary technologyReports Builder uses to publish reports to the Web, saving your reportin the JSP format prepares your report for the later chapters in thetutorial.Note:3.Choose File Close.2.4 SummaryCongratulations! You have now created a data model that lets you include a report onyour Web page. You now know how to: Open an existing Web page (HTML file) in Reports Builder Use the Data Wizard to specify data for a report Use the Query Builder to select data Save your report as a JavaServer Page (JSP)To review your work, continue to Chapter 3, "Reviewing the Source Code of the WebReport". Otherwise, skip to Chapter 5, "Creating a Report Block for the Web Report" tocontinue building your report.2-6 Oracle Reports Tutorial

3Reviewing the Source Code of the WebReportEstimated completion time: 5 minutesIn Chapter 2, "Adding Data to a Report", you added a data model to your Web page.Here, we show the relationship between the entries you made in the wizard and thedata model you created, as well as the JSP and XML code.You will also examine the custom JSP tags that Reports Builder inserted into your JSP.These custom JSP tags enable Oracle Reports to add the data you have chosen in theData Wizard to the JSP-based Web report.3.1 Viewing the Web Source in Reports BuilderTo view the Web source of your report in Reports Builder1.Make sure the report emprev your initials.jsp is open in Reports Builder.2.In the Object Navigator, double-click the Data Model node to display the datamodel for this report. Your data model should look like this:Figure 3–1 Data Model View for the Report3.In the Data Model view, click the Web Source button in the toolbar.Reviewing the Source Code of the Web Report3-1

Verifying Your JSP CodeYou can also double-click the Web Source icon under thereport name in the Object Navigator.Note:The source code displays in the Web Source view. Scroll through the code andnote the following items: The @ taglib line references the reports JSP library for all tags starting withrw. The %.% tags mark JSP-relevant tags, and point to the JSP CustomTag Library.Notice the opening rw:report tag and the opening and closing rw:objectstags.The rw:report tag appears after the library call. In a report, all ReportsBuilder JSP tags must appear between an opening and closing rw:reporttag. If the closing rw:report tag comes before the data is used, the reportwill be empty.The rw:objects tags appear in the Web Source. Note that you do not seeany text between the opening and closing tags. When you save the report toyour file system, Reports Builder encodes the data model and other elementsin a language called XML, and places the data model between these tags.Reports Builder hides the XML code between these tags to maintain XMLintegrity.Unless you are comfortable using XML and JSPs, it isrecommended that you do not type anything between the(rw:objects) tags in this view.Note:3.2 Verifying Your JSP CodeIf you have gone through this chapter and are still not sure whether your JSP iscorrect, open the emprev.jsp file. This file is located in the tutorial examplesdirectory you created.3.3 SummaryCongratulations! You have finished reviewing the data you added to your Web reportin Chapter 2, "Adding Data to a Report". You now know how to view the source codein the Reports Builder Web Source view and have learned about these Oracle Reportscustom JSP tags: rw:report rw:objectContinue to Chapter 5, "Creating a Report Block for the Web Report" to add a reportblock and finish your Web report.For more information on Oracle Reports JSP tags, see the Oracle Reports online Help.3-2 Oracle Reports Tutorial

4Reviewing the Source Code for the ReportBlockEstimated completion time: 5 minutesIn Chapter 5, "Creating a Report Block for the Web Report", you added a report blockto a JSP-based Web report. This chapter reviews the source code added for the reportblock.4.1 Viewing the Web Source in Reports BuilderTo view the Web source Open the Web source for the emprev your initials.jsp report you createdin Chapter 5, "Creating a Report Block for the Web Report" by double-clicking theWeb Source node in the Object Navigator.4.1.1 Reviewing the Header Tag and Body1.In the Web Source view, locate the !-- Header -- line.This HTML code indicates a comment line that helps you locate the report headinginformation in the file.2.Locate the th and tr tags, which should look something like this: tr th rw:id id "HBEMPLOYEEID92" asArray "no" / class "OraColumnHeader" Employee Id /th th rw:id id "HBempname92" asArray "no" / class "OraColumnHeader" EmpName /th .4.1.2 Reviewing the rw:foreach Tag and BodyThe rw:foreach tag loops across a group. The layout is repeated for each instance ofthe specified group.1.In the Web Source view, locate the rw:foreach JSP tag and find this line: rw:foreach id "R G EMPLOYEE ID921" src "G EMPLOYEE ID" Here, R G EMPLOYEEID is a unique identifier for this loop and G EMPLOYEE IDis the data model group on which the loop repeats.2.Notice that the /tbody tag that closes the table follows the closing /rw:foreach tag, as shown here:Reviewing the Source Code for the Report Block 4-1

Verifying Your JSP Code td rw:headers id HFDEPARTMENTID92" src. /tr /rw:foreach /tbody 4.2 Verifying Your JSP CodeIf you have gone through this chapter and are still not sure whether your JSP iscorrect, open the file we have provided, called empreva.jsp. This file is located inyour examples directory.4.3 SummaryCongratulations! You have finished reviewing the source code for the report block youadded in Chapter 5, "Creating a Report Block for the Web Report". Continue toChapter 6, "C

the tutorial, you will learn to generate a paper report based on the same data model. Figure 1–1 shows an overview of the first part of the tutorial. Figure 1–1 Tutorial Overview: Creating the Web Report 1.1 Tutorial Scenario In the chapters of this tutorial: 1. You will open the We