Lesson 05 - Advanced

Creating reports with multiple tables in Clear Impact Scorecard

Lesson 5-1 Creating a Report from multiple Tables

Goal - The goal of this lesson is to create a report that has calculated fields from multiple tables to display the number of scorecard objects by Scorecard name.

1. Click Reports on the main menu
2. Click on the New Report menu item
3. On the "Data Sources" tab. Select the table checkboxs in the following order Scorecards, Scorecard_Programs, Scorecard_Results, Results, Result_Indicators, Programs, Programs_PerfMeasures, Indicators, PerfMeasures.
1. The tables with an underscore and two object names like "Result_Indicators" are called "Junction Tables" and define how you connect one object to another. To learn more about Junction Tables check out this article.
4. Click on the "Fields" tab.
5. For the first field select the "Title" field from the Scorecards table
6. Next select the ID field from the Results Table.
7. Rename the description for the Result ID to be "Results"
8. From the function dropdown for the Result field select "Count Distinct"
9. Repeat steps 6-8 for Indicators, Programs, and Performance Measures, remembering to select the ID from the respective table and rename the description accordingly.
10. Click on the "Preview" tab so you can view the report.
11. You may notice a blank row without a scorecard title, these are objects that are not linked to a scorecard. To filter this row out select the Filters tab.
1. This blank row only displays if you have "Allow Nulls" checked on the data sources tab. This will do an outer join which will make sure all items are displayed regardless of their relationships.
12. For the filter field select "Title" from the scorecards table and set the operator to "Isn't Blank"
13. Now let's add a title- click on the "Misc" tab.
14. In the "Title" text box enter "Number of Objects by Scorecard"
15. Click the "Preview" tab. Now the report is titled. Let's Save it.
16. Click on the "Save as" tool bar button, name your report, for example "Lesson 5-1", and enter the category you want the report to be shown under, for example "Training", click ok. The custom report is saved.

Figure 11. Screen shot from report made in Lesson 5-1

Lesson 5-2 Adding Some Statistics to the Report

Goal - The goal of this lesson is to modify the report created in Lesson 5-1 by adding a subtotal and showing the percentage of the group.

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. Let's add a field that shows the percentage of Results that are on each Scorecard. Click on the "Fields" tab.
3. In the bottom most row under the "Field" label, select "ID" from the Results table. After selecting the field we need to count the number of products in each category and show them as a percentage.
4. Select the "Count Distinct" function for the "Function" in the field we just added. Also select the "% of Group (with rounding)" from the Format field. Now let's put a new description for this field.
5. In the description field "% Results". Now let's sum up the columns.
6. Add a subtotal by selecting "Add Subtotals" checkbox at the bottom of the Fields tab.
7. Click the "Preview" tab. The percentage of group statistics and subtotals has been added to the report. Let's Save it.
8. Click on the "Save as" tool bar button, name your report, for example "Lesson 5-2", and enter the category you want the report to be shown under, for example "Training", click ok. The custom report is saved.

Figure 12. Screen shot from report made in Lesson 5-2