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.
Advanced Mode
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
Fields Tab
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.
Field Selection
Select fields from the drop-down menu or use the Quick Add button
Description
Displays a new title for the Field
Sort
Sorts A - Z
VG
Visually Groups the Field
A
Arithmetic – Performs basic calculations (+, -, x, %) and concatenation of text.
Function
Displays functions available based on the type of data being accessed.
Format
Displays the formats available to be displayed based on the data type.
Other buttons
On the far right you can delete, insert, move, or set advanced properties.
Records
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
Add Sub-total
Adds a sub-total to the report under each numeric column
Quick Add
Lets you quickly add multiple fields to the report at one time
Add Pivot
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
Average the values in a column
Count
Counts the number of values in a column
Count Distinct
Counts the number of distinct values in a column
Maximum
Takes Maximum value of a field
Minimum
Takes Minimum value of a field
Sum
Sums the values in a column
Sum Distinct
Sums the rows with distinct values in a column.
Group
Groups field values together for aggregating
Group(Day)
23; day of month
Group(Month)
3; instead of March
Group(Year)
2012
Group(Month Name)
July
Group(Date)
7/4/2012
Group(Day of Week)
Sun or Mon
Group(Year & Month)
2012 -07
Group(Year & MN)
2012 - Jul
Group(Week)
Jul 01 – Jul 07 (Sunday to Saturday of Week)
Group(Year & Quarter)
2012–Q3
Days Old
342 (Number of Days from Today's Date)
Sum(Days Old)
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
the database
500
0,000
Displays as a whole number
500
0,000.00
Displays with two decimal places
500.00
$0.00
Displays as currency with two
decimal places
$500.00
$/100
Displays the Number / 100 In
currency format.
$5.00
0.0
Displays with one decimal place
500.0
0.00
Displays with two decimal places
500.00
0.000
Displays with three decimal places
500.000
0%
Displays the number as a percentage
50000%
% 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.
100%
Gauge
Shows value in a Linear Gauge instead of a numerical value.
Gauge (variable)
Shows values in a Linear Gauge which changes format due to values.
Dash Gauge
Shows values in a Gauge which changes format due to values.
Summary Tab
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
Chart Tab
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:
- Trend
- Pie
- Plot
- Bar
- Funnel
You can further customize the charts you create using the Advanced Properties button.
Misc Tab
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.
Style Tab
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.
Filters Tab
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.
Preview Tab
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.