Report Designer/Grid

Grid is a built-in type of report part that displays data in a tabular format. It currently supports 4 different vertical, horizontal, pivot and drill-down styles.

Configure Report Part Properties for Grid

Fig. 149 Report Designer - Grid Properties

1. Select the grid in Report Body (See Manage Report Parts for how to add a grid).

2. Click the expand icon (<) on the right to open the Properties boxes if needed.

3. Select the vertical Report Part Properties box.

4. The properties are listed in Report Part Properties box in 7 sections. (Fig. 149)

   • General Info

   • Table

   • Columns

   • Rows

   • Headers

   • Grouping

   • View
     

User can define the properties and see changes reflected in Preview pane:

• Select a style in Grid Style drop-down.

  • Vertical: data is displayed vertically.

  • Horizontal: data is displayed horizontally.

  • Pivot (cross tabulation): data is displayed in a matrix format.

  • Drill Down: data is displayed in collapsible groups of values to see the detailed or summarized numbers.

• 

  Fig. 150 Report Designer - Border Settings

  Define table border settings.

  1. Click the gear icon (⚙) to open Border Settings pop-up.

  2. Select pre-defined border styles: None (default), Outside, Inside or All,

     

     Fig. 151 Pre-defined Grid Border Style

     or click specific border style buttons in Preview section to apply.

  3. Select a line pattern: Solid (default), Dot or Dash.

  4. Select a border color.

  5. Select the border thickness (in pixels).

  6. Click OK to close the Border Settings pop-up.
     

• 

  Fig. 152 Report Designer - Background Color

  Set the background color.
  

• 

  Fig. 153 Report Designer - Alternative Background Color

  Set the alternative background by selecting one of four options:

  • None: Do not apply alternative background color. When selecting this option, the Alternative Background Color does not display.

  • Rows: Apply alternative background color for rows.

  • Columns: Apply alternative background color for columns.

  • Rows and Columns: Apply alternative background color for both rows and columns.
    

• 

  Fig. 154 Report Designer - Columns Setting

  Set the column width in pixels and turn on/off column word wrap.
  

• 

  Fig. 155 Report Designer - Headers Setting

  Choose a header font face and font size.
  

• Choose header text effects bold, italic and underlined.

• Choose header text color and cell color.

• Choose header text alignment left, center, right or justify and top, middle or bottom.

• Turn on or off word wrap option.

• Choose Freeze Headers.

• Notes:

  • The Freeze Header setting will apply for the report part in report designer, report viewer and dashboard.

  • If a sub-report has Freeze Header setting, the setting will apply for Link or Pop-up type but NOT for Embedded type.

• Optionally hides the header in export.

• Select to use Separators, and select a display format for multiple-field Separator.

  The Separators option displays multiple grids according to each unique value of the field(s) defined in Separators box.

  

  Fig. 156 Northwind Orders with ShipCountry, ShipRegion Separators

  For example, to display multiple grids, each one for each country and region in Northwind Orders table:
  

  1. Tick Use Separator check-box in Grouping in Report Part Properties to see Separators box inside the grid configuration.

  2. Add [ShipCountry] and [ShipRegion] to Separators box, they will show up as Group(ShipCountry) and Group(ShipRegion).

  3. Add [ShipCity] to Columns box, then choose Group as the Function, it should show up as Group(ShipCity).

  4. Add [OrderID] to Columns box, then choose Count as the Function, it should show up as Count(OrderID).

  5. The result should be multiple grids, each for a specific country and region (Fig. 156).
     

  6. Select a different Separator Style if needed. (Fig. 157 is to display both the labels and values in multiple levels.)

     

     Fig. 157 Multi Level With Label Style

  7. Optionally choose to print each grid in a new page by checking Page Break After Separator in Printing group.

• 

  Fig. 158 Report Designer - Data Refresh Interval

  Configure how often data is refreshed when report is being viewed.

  1. Click the gear icon (⚙) to open Data Refresh Interval pop-up.

  2. Choose to have data refreshed automatically or manually.

  3. Enter an interval between each refresh (in seconds).

  4. Choose to view all data or enter a number to view that specific number of latest records only.
     

• Optionally display a long report in multiple pages.

• Screenshot for Pivot columns per exported page to be added.

Choose a Grid Style

• 

  Fig. 159 Order Count by OrderYear, ShipCountry and ShipCity

  The pivot grid styles must used when all the label values depend on the actual data (not predetermined). For example, this report with all the header values calculated from the data (OrderDate) must use the pivot style.
  

• 

  Fig. 160 Supplier Count by Country, Region and City

  The drill-down grid style must be used for the need to expand and collapse groups of values to see the detailed or summarized numbers. For example, this report with the number of suppliers per city then per country must use the drill-down style.
  

• 

  Fig. 161 List of US States and Territories by Population

  The vertical and horizontal styles are used when some label values are already determined at design time. And horizontal style should be used when the list of label values is expectedly longer than the number of columns. For example: the list of US States and Territories by Population in Fig. 161.
  

Define a Vertical or Horizontal Grid Content

A newly-added grid will have the default vertical style. User only needs to enter the title, description and define the columns to get it working.

1. Optionally enter a title for the report.

2. Optionally enter a description.

3. Drag data source fields from middle panel into Columns text box to add them to the report.

A horizontal grid is defined in the same way as the vertical except that data source fields are added to Rows text box.

Define a Pivot Grid Content

Fig. 162 Order Count and Freight Sum by OrderYear, ShipCountry and ShipCity

To define this sample report:

1. Select “Pivot” as the Grid Style in General Info group.

2. Drag OrderDate field in Orders table from Middle Panel into Columns box.

3. The field will be given the alias “Group(OrderDate)” (Group function is used as expected).

4. Select the field in Columns box to open the Field Properties.

5. Check to confirm that in Data Formatting group, “Year” is selected for the format.

6. Drag ShipCountry field in Orders table from Middle Panel into Rows box.

7. The field will be given the alias “Group(ShipCountry)” (Group function is used as expected).

8. Drag ShipCity field in Orders table from Middle Panel into Rows box.

9. The field will be given the alias “Group(ShipCity)” (Group function is used as expected).

10. Drag OrderID field in Orders table from Middle Panel into Values box.

11. The field will be given the alias “Sum(OrderID)” (not the expected Count function).

12. Select the field in Values box to open the Field Properties.

13. In Data Formatting, select “Count” as the Function.

14. Drag Freight field in Orders table from Middle Panel into Values box.

15. The field will be given the alias “Sum(Freight)” (Sum function is used as expected).
    

Side Total

In a Pivot Grid report, Side Total values will provide the sum of all values for each field in Values box across each row.

To set up Side Total for a Pivot Grid report, check on the “Add Side Total” checkbox under Columns section in Configuration Section in report part backside.

Fig. 163 Side Total for Count(OrderID) and Sum(Freight) for each ShipCity through the years.

Define a Drill Down Grid Content

Fig. 164 Supplier Count by Country, Region and City

To define this sample report:

1. Select Suppliers table in report Data Source.

2. Add a grid to report body.

3. Select “Drill Down” as the Grid Style in General Info group.

4. Drag Country field in Suppliers table from Middle Panel into Groups box.

5. The field will be given the alias “Group(Country)” (Group function is used as expected).

6. Drag Region field in Suppliers table from Middle Panel into Groups box.

7. The field will be given the alias “Group(Region)” (Group function is used as expected).

8. Drag City field in Suppliers table from Middle Panel into Groups box.

9. The field will be given the alias “Group(City)” (Group function is used as expected).

10. Drag SupplierID field in Suppliers table from Middle Panel into Values box.

11. The field will be given the alias “Sum(SupplierID)” (not the expected Count function).

12. Select the field in Columns box to open the Field Properties.

13. In Data Formatting, select “Count” as the Function.

14. The Field Name Alias can be renamed to be more user-friendly (“Suppl Cnt”).