How to build custom reports in Clear Impact Scorecard
The Report Designer lets you create new reports and modify existing reports.
Data Sources TabTo join tables in check boxes mode you just check the tables you want to join one after another. When you check the first table, all tables that cannot be joined to it will automatically be disabled. After you will select the next table to join, the list of available checkable tables will be refreshed.Understanding database tables and joins is essential when selecting tables (data sources). Check out these helpful resources if you are unfamiliar.
Selecting the "Allow Nulls" checkbox on the data sources tab will perform an outer join while leaving it unchecked will perform an inner join.
The "Distinct" checkbox will filter out repeat entries from join tables and return just one object when multiple references to the same object exist.
Note - Advanced mode is intended for users with explicit training in SQL and report writing. Once advanced mode is selected you cannot go back to basic mode on a report.
In Advanced Mode, you will have to select the relationships that exist between the tables or views yourself. You will start with a drop-down menu that lists the various Tables and Views that are available.
To join two tables and/or views, they must have fields with identical entries. The Join Field dropdown menu is a list of the fields contained in the table/view selected in the Table dropdown menu to the left. Select the field that has identical entries as the table/view that it needs to be joined with.
The Foreign Table dropdown menu is a list of the tables/views that have been selected in the Table dropdown menu, other than the one in that row. Select the table/view to join the table/view in that row.
The Field dropdown menu is a list of fields in the table/view that are selected in the Foreign Table dropdown menu to the left. Select the field to join with the table/view in that row.
The Join Type dropdown menu (below) is a list of the available joining methods. The "Inner" method discards any objects from the joint fields that do not have an identical match. The "Left" method still displays those without an identical match
After selecting the data sources you want to display, as in the previous step, continue to the Fields tab. In the Fields tab, you will choose which fields you want to display and be able to apply various formatting options.
Listed below is a brief description of the properties available in the main Fields tab screen.
Select fields from the drop-down menu or use the Quick Add button
Displays a new title for the Field
Sorts A - Z
Visually Groups the Field
Arithmetic – Performs basic calculations (+, -, x, %) and concatenation of text.
Displays functions available based on the type of data being accessed.
Displays the formats available to be displayed based on the data type.
On the far right you can delete, insert, move, or set advanced properties.
Sets the number of records to display, which is used many times to add a Top
to the query result, such as Top 10 Customers by Revenue
Adds a sub-total to the report under each numeric column
Lets you quickly add multiple fields to the report at one time
Displays the pivot inputs to add to the report
Continue to Summary
Continues to the next tab in the report design process, the Summary tab.
Functions Dropdown Menu
Functions allow you to perform calculations on the data and can be chosen from the dropdown menu based on the data type of the selected Field.
. . .
No function used
Average the values in a column
Counts the number of values in a column
Counts the number of distinct values in a column
Takes Maximum value of a field
Takes Minimum value of a field
Sums the values in a column
Sums the rows with distinct values in a column.
Groups field values together for aggregating
23; day of month
3; instead of March
Group(Day of Week)
Sun or Mon
Group(Year & Month)
Group(Year & MN)
2012 - Jul
Jul 01 – Jul 07 (Sunday to Saturday of Week)
Group(Year & Quarter)
342 (Number of Days from Today's Date)
782 (Sums the Number of Days from Today's Date)
Format Dropdown Menu
The Format dropdown menu is a list of formats for the entries of the field selected in the Field dropdown menu directly to the left to appear in. Depending on the Field data type and the function, you will have differing options for formatting. The main two formats used will be available for all numbers and dates.
. . .
Displays the number as it is stored in
Displays as a whole number
Displays with two decimal places
Displays as currency with two
Displays the Number / 100 In
Displays with one decimal place
Displays with two decimal places
Displays with three decimal places
Displays the number as a percentage
% of Group (with rounding)
Same as % of Group but rounds to closest tenth (ex. 1.11547% would be 1.1%).
100.0% (only one selected value, 500)
% of Group
Calculates the percentage of the row value to the entire group of values.
Shows value in a Linear Gauge instead of a numerical value.
Shows values in a Linear Gauge which changes format due to values.
Shows values in a Gauge which changes format due to values.
In the Summary tab (below), a table can be created that summarizes the chosen fields using different functions (Ex: Average, Count, Maximum, and so on). You can also make a group of fields by choosing the Group option from the Function dropdown menu.
Most actions and properties available in the Summary Tab are available in the Fields Tab. The only difference is the removal of the Quick Add and Pivot capabilities, and addition of the Add deltas checkbox
In the Chart tab, a chart can be created using the data in the table/view selected on the Data Sources tab. The Charts Tab lets you create different variations of charts, including:
You can further customize the charts you create using the Advanced Properties button.
In the Miscellaneous Tab (Misc Tab), you can add a title, description, header and footer to the report, all with left, middle, or right justification.
In the Style tab, you can customize the look and feel of the report that will be displayed. You can change the color of the border, header, and rows, as well as the order the report items. When you change the properties of the various colors within the report, the sample grid can be used to see the differences.
The colors which can be changed are: Border color, Header color, Header foreground color, Item color, Item foreground color, and Alternating item color.
In the Filters tab the fields of the table/view chosen in the Data Sources tab can be filtered so that only pertinent entries appear in the report. The Filter Field dropdown menu is a list of the available fields in the table/view that have been selected or created in the Data Sources tab. Select the fields to filter.
In the Preview tab (see image below), a preliminary version of the created table can be viewed, along with its Summary table, Chart(s), Map, Gauges, along with any other properties that have been set. Go back and change selections in the previous tabs and see how they affect the table by returning to it in the Preview tab.