4 Using Advanced Report Techniques

Create and Populate the Database Objects

Before you begin creating the application, you need to create the appropriate sample objects within your workspace and load them with demonstration data.

To install the Issue Tracker sample objects:

1. Create the Issue Tracker data objects. Follow instructions outlined in "Create and Run a Script to Build Database Objects" .

2. Load objects with demonstration data. Follow instructions described in "Loading Demonstration Data" .

   These sample objects are copies of the Issue Tracker application objects. For a complete description of the Issue Tracker application see Chapter 14, "How to Design an Issue Tracking Application" and Chapter 15, "How to Build and Deploy an Issue Tracking Application".

Create the Application

Now, you create an application using the Create Application Wizard.

To create an application with a Home page and an Issues Report page:

1. On the Workspace home page, click the Application Builder icon.

2. Click Create.

3. For Method, select Create Application and click Next.

4. For Name, make these changes:

   1. Name - Enter Advanced Reports.

   2. Create Application - Select From scratch.

   3. Schema - Select schema containing loaded IT data objects.

   4. Click Next.

5. Under Add Page, make these changes:

   1. Select Page Type - Select Blank.

   2. Page Name - Enter Home.

   3. Click Add Page.

6. Under Add Page, make these changes:

   1. Select Page Type - Select Report.

   2. Subordinate to Page - Select Home (1).

   3. Page Source - Select Table.

   4. Table Name - Select IT_ISSUES.

   5. Implementation - Select Interactive.

   6. Click Add Page.

7. Under Create Application, click the It_Issues link.

   The New Page Definition window appears.

8. Under Page Definition, for Page Name, enter Issues.

9. Under Report Columns, for Show select No for each of the following columns then click Apply Changes:

   • ISSUE_DESCRIPTION

   • TARGET_RESOLUTION_DATE

   • PROGRESS

   • ACTUAL_RESOLUTION_DATE

   • RESOLUTION_SUMMARY

   • CRETATED_ON

   • CREATED_BY

   • MODIFIED_ON

   • MODIFIED_BY

10. Click Next.

11. For Tabs, select No Tabs and click Next.

12. For Shared Components, accept the default, No, and click Next.

13. For Attributes, accept all defaults and click Next.

14. For User Interface, select Theme 20 and click Next.

15. Review application attributes and click Create.

Run the Application

Now, run the Advanced Reports application and view the Home page, Issues Reports page and Single Row View.

To run the application:

1. Click the Run Application icon.

2. When prompted, enter your workspace user name, your password and click Login.

   This authentication is part of the default security of any newly created application. See "About Application Authentication".

   As shown in Figure 4-1, the home page appears.

   Figure 4-1 Initial Home Page

   
   Description of "Figure 4-1 Initial Home Page"
   
   

3. Click the Issues link.

   The Issue report page displays as shown in Figure 4-2, "Initial Issues Report".

   Figure 4-2 Initial Issues Report

   
   Description of "Figure 4-2 Initial Issues Report"
   
   

4. Locate the Link Column icon for Issue Id 1 as shown in Figure 4-3, "Single Row View Icon".

   Figure 4-3 Single Row View Icon

   
   Description of "Figure 4-3 Single Row View Icon"
   
   

5. Click the Link Column icon for Issue Id 1.

   The Single Row View for the first issue appears as shown in Figure 4-4, "Single Row View of the First Issue".

   Figure 4-4 Single Row View of the First Issue

   
   Description of "Figure 4-4 Single Row View of the First Issue"
   
   

   The Create Application Wizard configured the Issues interactive report Link Column to display the Single Row View. This is the default when the wizard creates a page type of Report.

   Tip:

   The Link Column can be reconfigured by going to the Report Attributes page and changing the Link Column setting. See Introducing Interactive Report Components.

6. Click the Application ID link on the Developer toolbar at the bottom of the page to return to the Application home page.

   Notice that the Create Application Wizard also created a Login page.

Change the Query to Retrieve Values from Other Tables

There are four columns in the Issues report that display Ids rather than person and project names. In this section, you replace three of those columns, the Issues Id column remains, with columns containing actual person and project names.

In order to accomplish this, you edit the query to include a join of the IT_PEOPLE and IT_PROJECTS tables. The following Id columns are replaced with name value columns as described here:

• Identified By Person Id is replaced with Identified By

• Related Project Id is replaced with Project Name

• Assigned to Person Id is replaced with Assigned To

To change the SQL query:

1. Select 2 - Issues page.

2. Under Regions, select Issues.

3. Scroll down to Source and replace Region Source with this SQL:

   SELECT "IT_ISSUES"."ISSUE_SUMMARY" as "ISSUE_SUMMARY",
       "IT_PEOPLE"."PERSON_NAME" as "IDENTIFIED_BY",
       "IT_ISSUES"."IDENTIFIED_DATE" as "IDENTIFIED_DATE",
       "IT_PROJECTS"."PROJECT_NAME" as "PROJECT_NAME",
       decode("IT_PEOPLE_1"."PERSON_NAME",NULL,'Unassigned',
           "IT_PEOPLE_1"."PERSON_NAME") 
           as "ASSIGNED_TO",
       "IT_ISSUES"."STATUS" as "STATUS",
       "IT_ISSUES"."PRIORITY" as "PRIORITY",
       "IT_ISSUES"."TARGET_RESOLUTION_DATE" as "TARGET_RESOLUTION_DATE",
       "IT_ISSUES"."PROGRESS" as "PROGRESS",
       "IT_ISSUES"."ACTUAL_RESOLUTION_DATE" as "ACTUAL_RESOLUTION_DATE",
       "IT_ISSUES"."ISSUE_ID" as "ISSUE_ID",
       "IT_ISSUES"."RELATED_PROJECT_ID" as "PROJECT_ID"
   FROM "IT_PEOPLE" "IT_PEOPLE_1",
       "IT_PROJECTS" "IT_PROJECTS",
       "IT_PEOPLE" "IT_PEOPLE",
       "IT_ISSUES" "IT_ISSUES"
   WHERE "IT_ISSUES"."IDENTIFIED_BY_PERSON_ID"="IT_PEOPLE"."PERSON_ID"
   AND "IT_ISSUES"."ASSIGNED_TO_PERSON_ID"="IT_PEOPLE_1"."PERSON_ID"(+)
   AND "IT_ISSUES"."RELATED_PROJECT_ID"="IT_PROJECTS"."PROJECT_ID"
   

4. Click Apply Changes.

5. Click Apply Changes to confirm.

Add Name Value Columns to the Display

Next, run the Issues interactive report page and use the Select Columns action to include the three additional name value columns to the display.

To add columns to the display:

1. Click the Run Page 2 icon at the top right corner of the page.

2. Click the Actions menu to the right of the Go button as shown in Figure 4-5.

   Figure 4-5 Action Icon

   
   Description of "Figure 4-5 Action Icon"
   
   

3. Select the Select Columns action.

   Your page should look similar to Figure 4-6.

   Figure 4-6 Select Columns Action Options Page

   
   Description of "Figure 4-6 Select Columns Action Options Page"
   
   

4. Highlight Identified By in the Do Not Display box and click the Move icon (>).

5. Repeat the previous step for Project Name, and Assigned To.

   The only column left in the Do Not Display box is Project Id. All other columns appear in the Display in Report list.

6. Click Apply at the bottom right of the Select Columns area.

   Your Issues page should now look like Figure 4-7.

   Figure 4-7 Issues Report with Person and Project Names

   
   Description of "Figure 4-7 Issues Report with Person and Project Names"
   
   

Create a Dashboard Page

First, you add a blank page to the application and name it Dashboard.

To add the Dashboard page:

1. Click the Application ID link on the Developer Toolbar.

2. Click Create Page.

3. Select Blank Page and click Next.

4. For Page Number, enter 3 and click Next.

5. For Create Page, make these changes:

   1. Name - Enter Dashboard.

   2. Title - Enter Dashboard.

   3. Breadcrumb - Select Breadcrumb.

   4. Entry Name - Enter Dashboard.

   5. Parent Entry - Select the Home link.

   6. Click Next.

6. For Tabs, select No and click Next.

7. Review selections and click Finish.

Add a Link on the Home Page to the Dashboard

Next, you add a link on the Home page to take users to the Dashboard. This link appears in the Navigation region on the left side of the Home page.

To add a link on the Home page to the Dashboard:

1. Navigate to the Page Definition for the Home page, page 1:

   1. Click the Application home breadcrumb.

   2. On the Application home page, click 1 - Home.

2. Under Regions, click the List link (next to Navigation).

3. On the List Entries page, click the Create List Entry button on the right side of the page.

4. On the Create/Edit List Entry page, edit the following:

   1. Sequence - Enter 20.

   2. List Entry Label - Enter Dashboard.

   3. Page - Enter 3.

5. Click Create.

6. Click the Application breadcrumb.

7. Click Run Application.

   Figure 4-9 Navigation Links

   
   Description of "Figure 4-9 Navigation Links"
   
   

   Notice that your Home page now includes a link to Dashboard.

8. Click Dashboard to test the link.

   The blank Dashboard page appears.

Add an Overdue Issues Report Region to the Dashboard

Next, you add a report region to the Dashboard page that displays overdue issues and then add a link from the report to the Issues interactive report.

Add an Open Issues by Project Pie Chart

Next, add a pie chart that displays Open Issues by Project and links to the Issues report. The pie chart query gets the number of open issues for each project name from the Issues interactive report. The project names, displayed on the pie chart, link to the Issues report where all issues for the selected project appear.