Table Of Contents:

Overview:

Creating Analyses, a Sub-query, and a Dashboard

In order to implement the use of a sub-query, two Analyses will first need to be made. The analyses will use the 'Class Registration', 'Class', 'Student Academic Program', and 'Student Enrolled' tables within the Census Subject Area. With the use of a sub-query, the second analysis created will be able to use the results of the first analysis as a filter while displayed on a dashboard.
Please see the previous training documents if assistance is needed in building the Analysis.

Build the First Analysis

The first analysis is the first piece of the training that will need to be completed. Open a new Analysis, use the Census Subject Area, and find the Class Registration and Class tables. Select the following columns to be queried:

1. SNAPSHOT from Class Registration
2. TERMCODE
3. PERSON ID
4. CLASS NUMBER
5. Course Grade Official
6. Enrolled Indicator
7. Student Enrollment Status
8. Enrollment Status Reason
9. Audit Indicator
10. Campus Code from Class
11. Subject Catalog Number
12. CLASS SECTION CODE

Next, select the PERSON ID column, and sort by Sort Ascending.

Once completed, add the following Filters to the query:

1. SNAPSHOT is equal to/is in eot
2. TERM CODE is equal to/is in 2137
3. Subject Catalog Number is equal to/is in COM 295
4. Audit Indicator is not equal to/is not in 0
5. CLASS SECTION CODE does not contain L
6. Campus Code is equal to/is in PULLM
7. CLASS SECTION CODE is prompted

Build the Second Analysis

The second analysis is the next step of the training that will need to be completed. Open a new Analysis, use the Census Subject Area, and find the Student Academic Program Desc and Student Enrolled tables. Select the following columns to be queried:

1. PERSON ID from Student Academic Program
2. Academic Program Desc
3. Cumulative GPA from Student Enrolled
4. TERM CODE

Once completed, add the following Filter to the query:

1. TERM CODE is equal to/is in 2137

When the TERM CODE filter has been set, 'Delete' TERM CODE from the Selected Columns area.

Once completed, it is now time to create a sub-query in this analysis that uses the results of the first analysis as a filter.

Creating a Sub-Query

As stated above, the sub-query's function is to use the results of another report as a filter. In this training, the sub-query created will use the PERSON ID in the first analysis as a filter. Therefore, the second analysis will reflect each PERSON ID displayed in the first analysis. The steps below will create this sub-query.

1. In the second analysis, open up the New Filter window for PERSON ID.
   
2. With the New Filter window open, click the 'Operator' drop down and select 'is based on results of another analysis'.
    
3. Next, click 'Browse…' and select the file that the first analysis is saved under.
   
4. Lastly, click the 'Use values in Column' drop down and select PERSON ID in order to complete the sub-query.
   
   Remember to Save the Analysis. 

Assembling the Dashboard

Next, select Dashboard from the 'New' dropdown in the menu bar (Depending on role privileges, you may have to select 'Edit' for the 'My Dashboard' located in the Catalog under 'My Folders'). In the pop-up, type the name, description, and save location. (Clicking SaveAs once in the Editor will bring up a similar pop-up)

Below is what the editor looks like, first we will go through the layout, and then we will continue with the example.
 

Editor Toolbar

• The 'page 1' tab is the name of the dashboard page that you are building (this can be renamed under Dashboard Properties)
• The blank page icon with the green plus or X are used to add or remove a dashboard page.
•  is the icon for 'Tools' and will present the following dropdown. These options will control printing properties, links that are including in the dashboard, and allowing customization by users. To rename the pages, go to the Dashboard Properties.
  

• The preview and run buttons will show either a quick display of the dashboard or let you run it live.
• Then there are the Save & Save As buttons
• On the far right is the Help button

Dashboard Objects & Catalog

The dashboard objects are all of the different items that can be placed into a dashboard.

• Column: Defines the layout of the dashboard
• Section: Defines an area within the dashboard in which a prompt/analysis/etc. can be placed
• Alert Section: Adds a section that displays alerts from software agents
• Action Link: Runs an associated action, which can perform things like starting an external process or navigating to another location
• Action Link Menu: Adds a menu of links that are selectable by the user
• Link or Image: Inserts an image or text web links to the page
• Embedded Content: Enables the display of a web page inside the dashboard
• Text: Static text or HTML
• Folder: Adds a folder to the dashboard page

The 'Catalog' function is the same as in all the other areas of 11g and provides access to saved shared or personal dashboards.

Building the Layout

On the left-hand side of the Dashboard editor page, under the Catalog section, locate the two analyses that were just created. Select the first analysis (Training 4_1) and drag it into the 'Drop Content Here' area. Notice that this action automatically creates a new section within a column.

Next, select the second analysis created under the Catalog section, and place it to the right of the first analysis under Section 1.

Save the Dashboard, and click the 'Run' button to see the sub-query in action. Your dashboard should look similar to this:


Notice how the PERSON ID's in the second analysis (the report on the right) reflect those in the first analysis. This is because of the sub-query that was created in the second analysis.