How do I create a custom report?
All Versions, Analytics
This answer addresses steps for creating a simple report that displays data without grouping or drill-down levels.
Reports are interface specific. If you add a new report while logged in to an interface, you can only access that report from that same interface. When you copy a report, you can save the copy to a different interface. In order to edit the copied report, though, you must access it from the console for the interface the copy is saved to.
Copying reports: All releases come with many standard reports. To copy a standard report, right-click on the report in the Reports Explorer and select Copy. If you have created a custom report that is close to the report you would like, you can copy that report to provide a solid starting point for your new report.
Report Builder: Creating a basic custom report is all done from within the Report Builder. You can customize reports based on your organization’s needs using the Report Design Center Ribbon, Data Dictionary, and Design Surface.
|Ribbon: The report design center’s ribbon includes eight tabs from which you can access options to create or edit a report. The tabs on the ribbon change depending on which view you use and what is selected on the design surface.|
|Data Dictionary: Lists the available database tables and fields that you can use in custom reports.
|Design Surface: this is where you design the report layout, including output columns, headings, and charts.|
Overview for Adding a New Report
The high-level steps in creating a new report are listed below.
- Add the new report.
- Define filters and search criteria for the report.
- Select the output columns and layout of your report.
- Determine who can run and/or edit your report.
- Save your report.
Each of these steps is described in more detail below.
Step 1: Add a New Report
When adding a new report, you must select which type of report to add: grid, standard, or standard with chart. Grid reports are most commonly used for use within the console to list multiple records which can then be opened for editing. After selecting the report type, the Report Builder opens to allow you to define your report.
To add a new report:
- Click the "File" button in the upper left of console, select Report
- You can also add a new report from the Reports Explorer, using the steps below:
- Click the Analytics button on the navigation pane.
- Double-click Reports to open the Reports Explorer.
- Click New Report on the ribbon
- Choose the type of report you want to build; a Grid report, a Standard report, or a Standard report with Chart.
Step 2: Define the Search Criteria (Filters) Used for the Report
Filters are used to restrict and define which records should be returned in your report. This allows you to run a report that returns a manageable amount of data or return records that are of specific interest to your business operations. If the record matches your filter definitions, then that record is included in the report.
For example, a Support manager is interested in reviewing unresolved incidents assigned to her group. Two report filters to define are:
Incident status type equal Unresolved
Incident Assigned Group equals Support
When configuring a filter, you must include a database field, an operator, and a value or values of interest.
Filter types: When setting up a report, you can set two types of filters: fixed and run-time selectable filters.
|Fixed filters are set at the time the report is configured and cannot be changed when the report is run.
|Run-time selectable filters can be changed each time the report is run. When configuring the report, you specify the default value(s) to use, but when you actually run the report, you can change the value for that search criteria.|
For example, you can configure a report that lists all of your public answers. Then you decide that you would like a report to list all of your proposed answer. Instead of copying the report, you can simply include the Answer Status field as a run-time selectable filter in your existing report. That way, when you run the report, you can select the specific status to use for the report output. This allows you to have one report to maintain, but you have the added flexibility of generating the report based on values you specify at the time the report is run.
Adding filters to your report:
- From the Home tab, click Add Filter. Or click the Filters button and from the pop-up window, click Add Filter.
- In the pop-up window, enter the following information:
Name: (optional) displays under the Filters icon. If this is left blank, the Expression is used in the Filters section.
Type: Enable the top check box to set the filter as a run-time filter and if you wish, enter custom text to use as the label for that filter when the user performs the search. Also, enable the Required check box if the user must select a field when performing a search.
Definition: Right click in the Expression field to select the database table and field to use for your filter. Or if you know the name, simply type in the table name and field in the format table_name.field_name. As you type, you can select from the matching tables and fields. For example, type "in" and you will be able to select the incidents table.
Next, select the operator. The operator options change depending on the type of field you select. For example, date fields include a "between" operator to configure a date range. Menu fields include the "in list" and "not in list" operators to select multiple menu items.
Finally, enter the value used for comparison, such as the date range of interest or the status of interest.
- Click OK. The window closes and your filter displays under the Filters icon in your Report Outline.
- If you have multiple filters, by default, the logical expression joins the filters using an AND connector. i.e. filter 1 AND filter 2 AND filter 3. If you need to modify the logical expression, right click on the Filters icon and select Edit Logical Expression. For more information on using logical expressions, refer to Answer ID 1041: Using Logical Expressions.
Step 3: Define the Report Output and Format
In a basic report, each record is displayed as a row in your report. You define the columns of the report by dragging and dropping database fields onto the design surface. You can also right-click in the design surface and select Add Column.
The Data Dictionary section allows you to easily select fields to include in your report. The options below are available when working with the Data Dictionary. To view a description of a table, right click on it and choose View Data Definition.
|Customize: To the right of the Data Dictionary heading, the Customize link allows you to narrow the list of tables by enabling specific types of tables. For example to easily review tables associated with your Service module, click Customize and deselect everything except Service and click OK. Now when you select All Tables, you only see the database tables associated with Service (including incident and answer-related tables).
|Current Dataset: This option displays all of the tables that are currently used in your report. The tables you used in defining your filters are listed. As you add output columns to your report, additional tables may be added to the current dataset if you include fields from other tables.
|All Tables: This option allows you to select from the entire list of Common, Service, Outreach, Opportunity, and Feedback database tables (as determined by which modules are enabled in your application.
|Functions: When creating more advanced reports, the functions allow you to summarize a column of data. For more information on functions, refer to Answer ID 1884: Functions Used in Reports.|
When you click on a table name, the fields for that table display alphabetically in the Fields section. Drag the fields for your report to the design area. You can reorder your columns by dragging and dropping the fields relative to one another. You can also reorder your columns by right-clicking on the column and selecting Move Column Right or Move Column Left.
Editing options: You can further format each column by right clicking the column and selecting from the edit options below:
|Edit Definition: Allows you to customize the column heading, add a description of the field, or select a different field for that column.
|Edit Formatting: Allows you to customize the layout of the contents in that column including numerical displays, column width, text alignment, and font style. Specific formatting features depend on the type of field displayed. That is, formatting options differ for date fields than for text fields.
|Edit Calculations: Allows you to add summary information at the bottom of each column, such as a maximum, minimum, average, or total.|
Sorting the output:
If you are only using one field to sort the records in your report, you can right click on that output column and select either Sort Ascending or Sort Descending. For example, in a list of answers, you could sort by the Answer ID field. Simply, right click on the Answer ID column and select Sort Ascending.
You can define up to four levels of sorting for your report.
- From the Home tab, click the Sort button.
In the pop-up window define the primary, secondary and additional sort criteria and indicate whether the sort is to be ascending or descending for each level.
Step 4: Define Who Can Use and Edit the Report
You can grant permission based on staff profiles to allow other staff members the ability to either run and/or edit your report.
In order for other staff to be able to access the report, you must save the report in a Public Reports folder. Reports saved in the My Reports folders are only accessible to the staff member who created and saved the report.
Staff members whose profiles have the Analytics - Administer permissions enabled are automatically granted permission to open and edit all reports saved in the public folders.
To grant access for other staff to run or edit your report, use the steps below:
- From the Home tab, click the Permissions button.
- Determine which profiles should have access to run your report and enable the check box in the Open column.
- Determine which profiles should be able to edit the view and enable the appropriate check boxes in the Edit column.
Step 5: Save and Run Your Report
To save your custom report:
- Click the Save icon in the upper-left corner of your design surface and select Save, Save As, or Save and Close. For new reports, the Save and Save As options are identical.
- In the pop-up window, highlight the appropriate folder (which fills out the Folder field) and enter the name of the report in the Name field.
- Click OK. If you created your report from the Reports Explorer, your report will be listed in the appropriate folder.
To generate your report: You can open the report from the Reports Explorer.
- Click the Analytics button in your navigation pane and double click Reports Explorer.
- In the right pane, navigate to the folder containing your report.
- Either double-click on the report or right click and select Open.