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

../_images/Report_Designer_Grid_Properties.png

Fig. 225 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. 225)
    • 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.
  • ../_images/Report_Designer_Border_Settings_Pop-up.png

    Fig. 226 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,

      ../_images/Grid_Pre-defined_Border_Styles.png

      Fig. 227 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.

  • ../_images/Report_Designer_Background_Color.png

    Fig. 228 Report Designer - Background Color

    Set the background color.

  • ../_images/Report_Designer_Alternative_Background_Color.png

    Fig. 229 Report Designer - Alternative Background Color

    Set the alternative background (New in version 2.9.0) 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.
  • ../_images/Report_Designer_Columns_Width_Setting.png

    Fig. 230 Report Designer - Columns Setting

    Set the column width in pixels and turn on/off column word wrap (column word wrap is available from version 2.10.0).

  • ../_images/Report_Designer_Headers_Setting.png

    Fig. 231 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. Vertical alignment is available from version 2.10.0.

  • Turn on or off word wrap option.

  • Choose Freeze Headers (available from version 2.10.0).

  • 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.

    ../_images/NW_Orders_Grid_Separators_ShipCountry,_ShipRegion_Group_by_ShipCity_Count_OrderID.png

    Fig. 232 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. 232).

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

      ../_images/NW_Orders_Separators_Multi_Level_With_Label.png

      Fig. 233 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.

  • ../_images/Report_Designer_Data_Refresh_Interval.png

    Fig. 234 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

  • ../_images/NW_Orders_Order_Count_by_OrderYear_ShipCountry_ShipCity.png

    Fig. 235 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.

  • ../_images/NW_Suppliers_Drill-down_Preview.png

    Fig. 236 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.

  • ../_images/List_of_US_States_and_Territories_by_Population.png

    Fig. 237 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. 237.

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

../_images/NW_Orders_Order_Count_by_OrderYear_ShipCountry_ShipCity.png

Fig. 238 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.

../_images/NW_Side_Total_Example.png

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


Define a Drill Down Grid Content

../_images/NW_Suppliers_Drill-down_Preview.png

Fig. 240 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”).