Displaying hierarchical grids

Training course

You can use the Group By option to display query results in a hierarchical grid of summary rows and detail rows. Grouped columns are displayed as high-level items. Expanding one of these items displays the detailed (non-grouped) items.

Viewing Run tab results

Example: Displaying donations by city

Do the following to create a query that uses the Group By option:

  1. Go to RiSE > Intelligent Query Architect > New > Query.
  2. Name the query Donations by City.
  3. (Sources tab) Add CsDonations and CsContactBasic.
  4. (Display tab) Select the following properties to be displayed:
    • [CsContactBasic] City
    • [CsContactBasic] iMIS Id
    • [CsContactBasic] Full Name
    • [CsContactBasic] Member Type
    • [CsDonations] Amount
  5. Select the Group By option for [CsContactBasic] City.
  6. Run the query.

    7. The results are displayed in a hierarchical grid. You can click to expand or collapse the rows. The summary rows list the city (because it is grouped) and the donation amount (automatically because it is numeric and decimal). All other properties are displayed in the second-level detail rows.

Notes and tips about the Group By option

The following are tips for creating a query displayed in a hierarchical grid.

Grouping not compatible with certain properties

The Group By option is not compatible with the following:

  • Custom SQL Expressions
  • The Group By option cannot reliably be used for Float columns or DateTime columns that include times. If you want to use Float or DateTime columns, a SQL Expression must be used to cast the column to another type. For example, CAST([vBoMyTable].[MyDateTimeField] AS DATE).

Using Group By with numeric properties

  • Any properties that are not grouped are displayed in the detail rows only, unless they are numeric properties.
  • When any property in a query is grouped, then numeric, decimal properties (such as CsDonations.Amount, CsOrders.Balance, and CsDuesHistory.Amount) are automatically summed and displayed in the summary rows and also displayed in the detail rows. To display these properties in the summary rows only, select the Group By option.
  • Non-decimal numeric properties such as CsContactBasic.Date of Birth, CsContactBasic.iMIS Id, and CsContactBasic.Home Phone are not automatically summed.

Other notes about the Group By option

  • If you select the Group By option for all properties in a query, the results will not be displayed in a hierarchical grid.
  • To display a non-numeric property in the summary rows, select the Group By option.
  • If a property has Group By enabled with a Hyperlink defined and that link is referencing another property in the query, then the linked property must also have Group By enabled. Not enabling Group By on both properties will result in a query error.