Report Designer

How to build custom reports in Clear Impact Scorecard

The Report Designer lets you create new reports and modify existing reports.

Report Designer

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


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.

Other buttons

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

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 the values in a column


Counts the number of values in a column

Count Distinct

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

Sum Distinct

Sums the rows with distinct values in a column.


Groups field values together for aggregating


23; day of month


3; instead of March



Group(Month Name)




Group(Day of Week)

Sun or Mon

Group(Year & Month)

2012 -07

Group(Year & MN)

2012 - Jul


Jul 01 – Jul 07 (Sunday to Saturday of Week)

Group(Year & Quarter)


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



Displays as a whole number



Displays with two decimal places



Displays as currency with two
decimal places



Displays the Number / 100 In
currency format.



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.

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.