View data source fields

1. Open an existing report if not already open.

2. Click Design in the left menu.

   
   

3. Data sources will be displayed under Selected Data Source group in the Middle Panel, initially collapsed.

4. User-defined calculated fields are under Calculated Fields group, if available.

5. Click on a data source to expand its fields.

6. The data source fields are listed together with an icon representing their data types.

   
   

View data source fields’ properties

1. Select a data source field to view its properties.

2. Properties of the selected field is displayed in the Field Properties box.

   
   

Add Calculated Field

Calculated fields can also be added to a specific report.

A report-level calculated field. Note that in Expression box the connection name [Northwind] and schema name [dbo] are included since the fields can come from different connections.

Sample expressions:

Casewhen([northwind].[dbo].[Orders].[EmployeeID]=1)then'less'when([northwind].[dbo].[Orders].[EmployeeID]=3)then'mid'when([northwind].[dbo].[Orders].[EmployeeID]=4)then'high'else'not evaluated'endCasewhen(BETWEEN([northwind].[dbo].[Orders].[EmployeeID],1,3))then'less'when(BETWEEN([northwind].[dbo].[Orders].[EmployeeID],4,6))then'mid'when(BETWEEN([northwind].[dbo].[Orders].[EmployeeID],7,10))then'high'else'not evaluated'endIF([northwind].[dbo].[Orders].[EmployeeID]<3)then'Less'else(IF(BETWEEN([northwind].[dbo].[Orders].[EmployeeID],3,6))then'More'else'Most'END)ENDCount(DISTINCT([Northwind].[dbo].[Orders].[ShipCity]))DATEPART(yyyy,[Northwind].[dbo].[Orders].[OrderDate])DATEPART(m,[Northwind].[dbo].[Orders].[OrderDate])CASEwhen(DATEPART(yyyy,[Northwind].[dbo].[Orders].[OrderDate])=1996)then1else0endDATEADD(year,1,[Northwind].[dbo].[Orders].[OrderDate])CAST([Northwind].[dbo].[Orders].[OrderID]astext)

Add a report filter

From the data sources, user can add report filters to select only the rows that they need.

For example with Northwind database, to do a report on customers that are in Atlanta city:

1. User should have selected “Customers” table in Data Source page.

2. Click Fields in the left menu.

3. Expand Filter section if needed by clicking the > icon.

4. Click on Selected Data Source in Middle Panel to expand the list.

5. Click on “Customers” data source to expand its fields.

6. Either drag the field “City” into the Filter box or click Add Filter button and select “City” in the drop-down.

7. Click on the filter area outside of the drop-down (in darker color) to expand the Filter Properties box.

   

8. The properties are listed in Filter Properties box in 3 sections:

   • Source
   • Filter Operator
   • Filter Settings
   • Filter Formatting

9. Select Equivalence in Filter Operator section.

10. Select Equals (Manual Entry) in the next drop-down.

11. Type in “Atlanta”.

Note: In case data source is a stored procedure, its parameters are automatically added as report filters.

Configure filter properties in Filter Settings section

In this section, user can:

• Choose a filter alias.

  This alias will be the display text on report at runtime, if visible. The alias must be unique inside the whole report. Any duplicated value will result in an error message (Fig. 190). The alias can contain any characters except for “[” and “]”.

  

• Select the filter to be visible or not at runtime.

  A filter with a fixed condition (such as “City” always equals “Atlanta”) should be hidden at runtime.

• Select the filter to be required or not.

  A required filter will force end-user to enter filter values in order to run it. Contrary to a fixed condition, the example report above could be more flexible if the “City”-equals-“Atlanta” filter is visible and not required. Then the report still shows customers in Atlanta by default, but end-user can select another city or even empty the value to show all customers. Screenshot to be updated.

  Note

  For a stored procedure’s parameter, if the Required checkbox is not selected and the filter value is empty, the stored procedure will be executed with ‘null’ value. If your stored procedures do not return all results when null is passed, leaving this value as null will always return no results. This is a new behavior from version 2.12.0.

• Select the filter to be cascading or not.

  A cascading filter will constrain the available values in other filters behind it. For example, if a report has two filters: Country and City, and a value was selected for Country, then the only possible values that can be selected for City will be ones with a match for the current Country. Screenshot to be updated.

• Choose sorting order for filter values: unsorted, ascending or descending by repeatedly clicking the icon.

• Select the filter condition.

  1. Select the operator group Comparison, Equivalence, Field Comparison or String.

  2. Select the specific operator.

  3. Enter or select the fields or values depending on each operator.

      

     
     

Configure filter properties in Filter Formatting section

In this section, user can:

• Choose a font face and font size.
• Choose text effects bold, italic and underlined.
• Set text color and cell color.

Build complex filter logic

In most cases, user simply adds new filters when needed, and the report returns data that matches all of the filter conditions.

In some other cases, that simple match-all condition is not enough. For example, user needs a report of all customers that are in Atlanta city with either CompanyName or ContactName “John”. Such “either A or B” condition requires building a filter logic.

1. Add a filter for “City”, “Equivalence”, “Equals”, “Atlanta” (see Add a report filter).

2. Similarly, add a filter for “CompanyName”, “String”, “Like”, “John”.

3. Similarly, add a filter for “ContactName”, “String”, “Like”, “John”.

4. The filters are subsequently numbered 1, 2 and 3 in the filter box.

5. Enter the logic into Filter Logic box. The following rule of thumb may help:

6. For each “either A or B” condition, write an “or” condition using the filter numbers, and wrap it in parentheses - (2OR3) for this example.

7. Fill in other “and” conditions using the filter numbers - 1AND(2OR3) for this example.

8. Click Validate Syntax button to check the logic.

   

Apply Cross Filtering to Multiple Report Parts

Cross filtering allows user to drill up and drill down data in multiple report parts together. For each drilling action from user, it will filter related report parts automatically. Cross filtering is initalized on charts, gauges, or maps which have more than one x-axis field as the drill down feature of these items is what triggers the cross fitlering functionality for all other report parts.

1. Select related report parts to apply cross filtering. In this example Chart and Grid will be drilled up and down together while Map remains independent.

   

2. Set up report parts to have common data source fields. In this example Chart and Grid display aggregated data for ShipCountry and ShipCity.

3. Drill down on one report part by clicking on a data point.

   

4. The related report parts are filtered automatically, and the Cross Filtering breadcrumb tells which report part is being drilled down.

5. To reset, either drill up the exact report part, or remove the drill-down on the breadcrumb.

   

Manage Report Parts

There are many ways to display data in a report: bar chart, line chart, pie chart, map, data grid, etc, each is supported by a different report part. Built-in report parts include:

• Chart (Bar Chart, Line Chart, Pie Chart, etc.)
• Form
• Grid (Horizontal, Vertical, Pivot)
• Gauge
• Map

Any new report will include one default blank report part. Additional report parts of the same or different types can be added by:

• clicking Add Report Part button at the top.

• clicking the add icon (+) in any available background cell.

  

• clicking the copy icon in the configuration header.

  

A report part can be removed by clicking the delete icon in the configuration header.

Report parts can be resized, dragged to a new location, or switched position with each other in Preview Mode.

• To resize:

  1. Hover the cursor over borders and corners of a report part.
  2. When the cursor changes, click and drag to resize the report part.
  3. The color of the dragged report part remains purple if the new size is acceptable, and changes to orange if the new size overlaps with other report parts.
  4. Release the mouse when the color is purple to accept the new size.
  5. Release the mouse when the color is orange to cancel.

• To change location:

  
  1. Hover the cursor over the report part header.
  2. When the cursor changes, click and drag the report part to a new location.
  3. The shadow rectangle is where the report part will land.
  4. The color of the dragged report part remains purple if the new location is acceptable, and changes to orange if the new location overlaps with other report parts.
  5. Release the mouse when the color is purple to accept the new location.
  6. Release the mouse when the color is orange to cancel.
     

• To switch position with another report part:

  
  1. Hover the cursor over the report part header.
  2. When the cursor changes, click and drag the report part over another.
  3. The shadow rectangle is where the report part will land.
  4. Drag the report part so that the shadow rectangle completely covers or is completely covered by the other report part, and the color of the dragged report part remains purple.
  5. Release the mouse when the color is purple to accept the new location.
     

Configure report part properties

See:

• Report Designer/Chart
• Report Designer/Form
• Report Designer/Gauge
• Report Designer/Grid
• Report Designer/Map

Open Field Properties box for data source fields in report

1. Click on a data source field inside report body.
2. The properties are listed in Field Properties box in 4 sections:
   • Data Source
   • Data Formatting
   • Header Formatting
   • Drill Down

Note: Instead of trying to find a field inside report body and click on it, user can quickly select a report part then one of its fields using the two drop-downs on top of Field Properties box.

Configure field properties in Data Source section

In this section, user can:

• Choose an alias for the selected data source field.

  The alias must be unique inside the whole report. Any duplicated value will result in an error message. The alias can contain any characters except for “[” and “]”.

  
  

• Select visible or not for the field. A not visible field will not appear on the report screen at runtime.

Configure field properties in Data Formatting section

In this section, user can:

• Apply a function to the field.

  The list of available functions depends on the data type and includes two lists:

  • User-defined functions marked as Field Level in Data Model which require a single input parameter in a compatible data type.
  • System built-in functions for that specific data type.

• Choose a display format for the field.

The list of available formats depends on the data type of the field.

Note: New in version 2.6.0: Add new formats for Numeric and Money Data Types.

- % of Subtotal
- % of Subtotal (with rounding)
- % of Grandtotal
- % of Grandtotal (with rounding)
- % of Sidetotal
- % of Sidetotal (with rounding)

When a sub/grand total is not yet defined, it should default to the sum.

• Choose a font face and font size.

• Choose text effects bold, italic and underlined.

• Choose text alignment left, center, right or justify and top, middle or bottom. Vertical alignment is available for grid from version 2.10.0.

• Turn on or off word wrap option. (This option available from v2.10.0).

• Choose data sorting order unsorted, ascending or descending by repeatedly clicking the icon.

• Set text color and cell color for different ranges of value.

  1. Click either icon.
  2. Select Value, Range Value or Range Percentage in Text Color Settings or Cell Color Settings pop-up.
  3. Click Add Setting.

• Enter a value or a range of value then pick a color.
• Continue to click Add Setting to add more ranges and colors.
• Click OK to save the setting.



• Set alternative text for different ranges of value.

  1. Click the icon.
  2. Select Value, Range Value or Range Percentage in Alternative Text Settings pop-up.
  3. Click Add Setting.
  4. Enter a value or a range of value then type in an alternative text.
  5. Continue to click Add Setting to add more ranges and alternative texts.
  6. Click OK to save the setting.
     

• Set custom URL.

  1. Click the icon.
  2. Enter the url into the text box.
  3. Choose an option to open the url in a new window, a new tab or the current window.
  4. Click OK to save the setting.

• Write customized action in embedded JavaScript.

• Set Grand Total and Sub Total.

To set up both Grand Total and Sub Total for Freight costs in Northwind database’s Orders table as an example:

1. Choose Orders table as Data Source, add a Grid report part, add [ShipCountry] and [Freight] to the list of columns.

   
   

2. Select [ShipCountry] in the report part.

3. Choose Group in Function drop-down. Data will be grouped by each available value in [ShipCountry].

   
   

4. Select [Freight] in the report part.

5. Click the Grand Total icon to open the pop-up.

6. Enter the display label in Grand Total Label box (e.g. “Total Freight Costs”).

7. Select Sum in Grand Total Function drop-down.

8. The data type Money is automatically suggested in Data Type drop-down.

9. Select a format in Format drop-down.

   New in version 2.6.0: Add new formats:

   - % of Subtotal
   - % of Subtotal (with rounding)
   - % of Grandtotal
   - % of Grandtotal (with rounding)

10. Click OK to close the pop-up.

    

11. Click the Sub Total icon to open the pop-up.

12. Enter the display label in Subtotal Label box (e.g. “Freight Costs for this Country”).

13. Select Sum in Subtotal Function drop-down.

14. The data type Money is automatically suggested in Data Type drop-down.

15. Select a format in Format drop-down.

    New in version 2.6.0: Add new formats:

    - % of Subtotal
    - % of Subtotal (with rounding)
    - % of Grandtotal
    - % of Grandtotal (with rounding)

16. Click OK to close the pop-up.

17. Click OK to close the pop-up.

    

18. Click Save at the top.

Configure field properties in Header Formatting section

In this section, user can adjust formatting for the header row:

• Set column width.
• Choose a font face and font size.
• Choose text effects bold, italic and underlined.
• Choose text alignment left, center, right or justify and top, middle or bottom. Vertical alignment is available for grid from version 2.10.0.
• Choose text color and cell color (see Configure field properties in Data Formatting section).
• Turn on or off word wrap option.
• Set the “grouping key” for specific columns to stand next to each other. Screenshot to be updated.

Configure Subreports using field properties in Drill Down section

In this section, user can connect another report as subreport via the values in this parent report.

1. Click the icon to open Subreport Settings pop-up.

2. Select the subreport from the drop-down list.

3. Tick the checkbox in case subreport needs to filter in the same way as parent report.

4. Click Add Field Mapping button to insert a new mapping row.

5. Select a field in current report to use its values as filter

6. Select a field in subreport to be filtered by those values.

   

7. Continue to add more field mappings as needed.

8. Choose a display style for the subreport in the Style drop-down.

9. Click OK to save the setting.