Lesson 07 - Pivot Tables

Creating Pivot Tables in Clear Impact Scorecard's custom reporting module

Lesson 7-1 - Creating a Pivot Table

Goal - The goal of this lesson is to create a report that displays multiple time periods of data for a measure in a pivot table.

1. Find the Performance Measure ID of a Performance Measure to report on.
2. Click Reports on the main menu
3. Click on New Report
4. Under the "Data Sources" tab - select the PerfMeasures, PerfMeasureValues, and TimePeriods tables.
5. Click Continue to Fields
6. Under the "Fields" tab - Click on the drop down arrow and choose "Title" from the PerfMeasures table. Change the description to "Performance Measure"
7. At the bottom click the "Add Pivot" button, this will add a new section of fields to fill out.
8. Select the Title field from the Time Periods table for the "Add pivot columns for" field.
9. Select the ActualValue from the PerfMeasureValues table for the "Calculate cell values on" field.
1. This will default the following field to "Sum" which can be changed to average for percentage measures.
10. Now let's choose which Measure the report should display data for using a filter, select the Filters tab.
11. Select ID from the PerfMeasure table for the filter field
12. Select Equals for the Operator
13. Enter the ID of the PerfMeasure you want to report on in the Values field.
14. Click on the "Preview" tab so you can view the report.
15. Click on "Save as" tool bar button, name your report, for example "Lesson 7-1", and enter the category you want the report to be shown under, for example "Training", click ok. The custom report is saved.

Figure 1. Screen shot from report made in Lesson 7-1

Lesson 7-2 - Using a pivot table to show values for multiple measures by scorecard

Goal - The Goal of the lesson is to add additional measures to the Lesson 7-1 report by scorecard.

1. If you saved the report from the previous lesson you will need to re-open it in the designer by clicking on the pencil icon on the report in the list.
2. Under the "Data Sources" tab - select the Programs_PerfMeasures, Programs, Scorecards_Programs, and Scorecards tables.
1. This will give us the relationship from Performance Measure all the way to Scorecard that we can filter on.
3. Now let's change our filters to display multiple measures, select the Filters tab.
4. Click the orange X icon next to any existing filters to remove them.
5. Select ID from the Scorecards table for the filter field
6. Select Equals for the Operator
7. Enter the ID of the Scorecard you want measures reported from.
1. This filter will now show all measures on this scorecard in a pivot table. If you are using multiple reporting frequencies on these measures you will need to restrict the report to just one or it will display months, alongside quarters, and years.
2. To filter for just one reporting frequency select the Calendars table from the Data Sources tab.
3. Then create a filter on the Title field of the Calendar table.
8. Click preview and you will see additional measures now appear if there is more than one on the scorecard.
9. Click on the "Save as" tool bar button, name your report, for example "Lesson 7-2", and enter the category you want the report to be shown under, for example "Training", click ok. The custom report is saved.

Figure 2. Screen shot from final report made in Lesson 7-2