Developing Reports with Advanced Functionality using Discoverer Plus

Parameters

Creating Parameters

Parameters are workbook items that allow Discoverer users to analyze worksheets by entering dynamic input values.

In this training module, parameters for Term Description, College Description, Department Description and Course will be created for a report that retrieves data for the schedule of classes.

Select ‘Edit worksheet’. Delete conditions, if any, using the item to be used to create a parameter.

Select ‘Parameters’ tab. Click ‘New’ to create a new parameter.

Name the parameter and select an item to base the parameter on.

The default operator is ‘=’ but can be changed according to the report’s requirements. The other operators are ‘<>’, ‘>’, ‘<’, ‘>=’, ‘<=’, ‘LIKE’, ‘IN’, ‘NOT LIKE’, ‘NOT IN’, ‘!=’, ‘^=’.

Enter a prompt that will appear for users when they run the workbook. A description of the parameter can be entered, for users’ convenience, like, for a parameter that requires a term desc to be entered, an example of a term code can be entered, ‘e.g. Spring 2007’.

If the same parameter value is to be used for all sheets, select ‘Allow only one set of parameter values for all worksheets’. If different parameter values are required for each sheet, select ‘Allow different values for each worksheet’. The parameters can be optional or required, multiple or single by selecting the appropriate parameter properties. To set a default value, type in the value in the text box. The default option ‘Show all available values’ will retrieve the entire list of values.

To filter the list of values, select ‘Filter the list of values based on the selected conditions’. Check the required conditions. Click ‘OK’ to create the parameter.

Create other parameters.

Select ‘Select Items’ tab. Select ‘Conditions’ tab. A new condition referencing the parameter is created. Click ‘OK’.

Refresh the worksheet.

The prompt to enter parameter values will appear. Click on the search button (flashlight) to retrieve the list of values.

Only a few values are displayed. To view the rest, click the little blue arrow at the bottom of the list.

Select value(s) from the ‘Displayed values’ box. Click ‘Ok’ once the value(s) have been selected.

The report will run with the selected parameter value(s). The results are displayed. To run the worksheet with different values, refresh the sheet again.

The title of the sheet can be edited to include the parameter values. Edit title, and then insert the ‘Parameters’.

The values entered as parameters will show up in the title.

This ‘Select Values’ box appears for parameters which have been filtered using selected conditions. In this box, parameter values are selected from the ‘Displayed values’ box and copied over to the ‘Selected values’ box.

List of Values Retrieval Error

If the list of values takes a long time to be retrieved, Discoverer will give the following error message:

‘Retrieving the list of values has stopped because it is taking longer than permitted. To change this limit, go to the Tools menu and choose Options, then alter the “Cancel list-of-values retrieval” setting on the Query Governor tab.’

To change settings for list of values retrieval, go to Tools-Options. This can also be done before building the parameters.

Select ‘Query Governor’ tab and change the time permitted to retrieve the list of values to 1min. Click ‘Ok’.

Manage Links (user defined drills)

Creating manage links

A user defined drill link is a way to access (or drill out to) another worksheet or Internet page from a particular worksheet cell value. Drill links enable you to navigate from one worksheet to another (in any workbook that you have access to) and from a worksheet to an Internet URL. Manage links are different from drills as drills in discoverer use drill hierarchies. A drill hierarchy, created by the Discoverer manager, is a set of related items that provide a predefined path to help Discoverer users navigate worksheets. In this training module, a ‘manage link’ will be created on the field ‘Course’, to drill to the details of that particular course.

Right click on the column on which the drill is to be created. Select ‘Manage Links’.

The ‘Manage Links’ wizard appears with the name of the selected column in the ‘Item’ box. Click on ‘New Link’.

Enter the name of the link, such as ‘Course Details’ if it is a drill to details. To create a link to a different worksheet in the same workbook, select ‘Worksheet’ and the default workbook title in the ‘Destination Workbook’ box.

To link to a different workbook, click browse.

From the list of workbooks that appears, select the workbook and click on the plus sign next to it. It will open the list of sheets that are in the workbook. Select the worksheet that is required.

To drill to a worksheet in the current workbook, select the worksheet from the list of ‘Destination Worksheet’. Click on ‘Parameters’ to select the appropriate parameters to be used for the drill.

Click on the arrow next to the parameter box to select the appropriate fields from sheet1. The values from these fields will be passed on to the parameters of sheet 2 to execute the query.

Select the appropriate fields to be referenced by the parameters.

Click ‘Ok’ to complete the process of creating drills.

Click on the arrow next to the field values to drill to the detail sheet. Click on the name of the drill when it appears.

Going back from Detail sheet to the first sheet, only one value will appear in sheet 1, ‘All Courses’.

To change this, one of the parameter properties will need to be changed.

Edit parameter. Select option, ‘Allow different values for each worksheet’. This will not allow the same value to be used while navigating from second sheet to primary sheet.

Click ‘Ok’ and refresh sheet.

Run the sheet with only one value, term desc. This will retrieve all data. Drill to second sheet and then go back to primary sheet. All the rows of the primary sheet will be there.

Special Character Error

For a field value with special characters, like an apostrophe (‘), the following error will occur and the drill will not work. E.g. the drill for value ‘Bioinformatics&Comput’nal Biol’ will not work because of the special characters in the parameter ‘Dept Desc’.

Totals

Creating Totals

In this training module, a total of the seats available for the courses offered in each department will be calculated.

For the totals, change page item ‘Department’ to a column, which will be used for subtotals.

Select Tools – Totals.

Select ‘New Total’.

Select the column name to be used for the total.

Select function, ‘SUM’, from the drop down list.

For count distinct, select ‘Count Distinct’.

For a grand total, that will appear at the end of the report, select ‘Grand total at bottom’. Select ‘Subtotal at each change in:’ to calculate subtotals. These totals will show after each change in the value of the column. Also select the column name.

This will sum all ‘Fsect Seats Avail’ for each ‘Department’ and display the total at the end of each department.

To give the subtotal a name of your choice, uncheck ‘Generate label automatically’.

Type in a label name in the text box or click on the arrow to select the label name. ‘Insert Value’ will insert the name of each department before the subtotal amount.

Click ‘Ok’ and a new total is calculated.

Click ‘Ok’ and the query will run. Scroll down to view the subtotal.

Count Distinct is to count the section titles for each department.

To change the format of the labels, edit total and click on ‘Format Data…’.

Calculations

Case statement

In this training module, we will create a calculation using a CASE statement to show if a course is crosslisted.

Add the field on which the calculation will be based. Go to ‘Select Items’ tab and select ‘Calculations’.

Select ‘New Calculation’.

Give the calculation a name which will appear as the column name and select item from the ‘Selected Items’. Type in the following calculation using the item selected.

CASE WHEN Crse Section Dim.Dcrse Xlst Group IS NULL THEN ‘N’ ELSE ‘Y’ END

(For Crse Section Dim.Dcrse Xlst Group null value, calculation Xlst will be N else, it will be Y.)