2
Adding Data to a Report

• EMPLOYEE_ID

• FIRST_NAME

• LAST_NAME

• HIRE_DATE

• JOB_ID

• SALARY

• DEPARTMENT_ID

• EMPLOYEE_ID

• FIRST_NAME

• LAST_NAME

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_NAME
FROM EMPLOYEES, EMPLOYEES EMPLOYEES_A1
WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID)

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_NAME
FROM EMPLOYEES, EMPLOYEES EMPLOYEES_A1
WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID)
AND EMPLOYEES.DEPARTMENT_ID=100

Concatenate the EMPLOYEES.FIRST_NAME and EMPLOYEES.LAST_NAME fields into one alias called emp_name.

The line of the query should now look like this:

, EMPLOYEES.LAST_NAME ||', '|| EMPLOYEES.FIRST_NAME emp_name

15. Concatenate the EMPLOYEES_A1.FIRST_NAME and EMPLOYEES_A1.LAST_NAME fields into one alias called mgr_name.

    The line of the query should now look like this:

      , EMPLOYEES_A1.LAST_NAME ||', '|| EMPLOYEES_A1.FIRST_NAME mgr_name
    
    

16. 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, EMPLOYEES_A1.EMPLOYEE_ID, 
    EMPLOYEES_A1.LAST_NAME||','|| EMPLOYEES_A1.FIRST_NAME mgr_name
    FROM EMPLOYEES, EMPLOYEES EMPLOYEES_A1
    

    WHERE (EMPLOYEES.MANAGER_ID = EMPLOYEES_A1.EMPLOYEE_ID)
    AND EMPLOYEES.DEPARTMENT_ID = 100
    

    Note: If you're not sure whether your query is correct, open the file tutorial_sql.txt from the example files we provided to you

17. Click Next.

18. Now that we've selected the raw data, let's organize it into groups by department manager name.

    On the Groups page, in the left column, click the MGR_NAME field, click the right arrow to move the field to the Group Fields list, then click Next.

19. Now, let's calculate some of our data. The Totals page displays some of the commonly-used calculations.

    On the Totals page, in the left column, click SALARY, then click Sum to display the sum of the Salary column in the Totals list.

20. Click Finish.

    Figure 2-4 Data Model view for the report

    

    Text description of the illustration adddata_dm.gif

    Note: A Reports-level summary displays in the upper left-hand section of the Data Model view, but is not displayed in this image.

    2.3 Save your report as a JSP file

    In Reports Builder, you can save your report using several formats. Since we're creating a JSP-based Web report, we will save our report as a JSP (JavaServer Page).

    To save your report as a JSP:

    1. With your report selected in the Object Navigator, choose File > Save As.

    2. In the Save dialog box, change the report name to emprev_<your initials>, and change the type to Reports JSP (.jsp), then click Save.

       Note: Be sure to include your initials so you do not overwrite the files we've provided. This saves the report as a JavaServer Page (JSP). Since JSP is the primary technology Reports Builder uses to publish reports to the Web, saving your report as a JSP prepares your report for the later chapters in the tutorial.

    3. Choose File > Close.

    2.4 Summary

    Congratulations! You have now created a data model that will allow you to include a report on your 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 Web Report". Otherwise, skip to Chapter 4, "Creating a Report Block for the Web Report" to continue building your report.

    For more information on using the Report Wizard, columns, groups, and fields, see the Reports Builder online help. You can also find more information about why JavaServer Pages are useful by visiting Getting Started with Oracle Reports on the Oracle Technology Network (http://otn.oracle.com/products/reports/).