AX1686

Using upstream grouping columns as row dimensions in web reports

Under normal circumstances, row dimension columns can be columns on the primary table for the data context, or columns on lookup dimension tables. However, when the primary table for the web report is a reference table, you can also optionally use columns from related tables as row dimensions. This type of configuration is referred to as upstream grouping columns.

Upstream grouping columns can be useful for reporting in certain Axiom products that hold important data in reference tables. For example, the Enterprise Decision Support (EDS) product needs to report on data in the Encounter table, which is a reference table. For some reports, they want to group this data using a related table such as the EncounterPayor table, yet still bring in columns from other related tables that look up to the Encounter table (such as CostDetail). Columns from the EncounterPayor table are considered upstream grouping columns because EncounterPayor looks up to Encounter instead of the other way around.

Identifying upstream grouping columns

A row dimension column is considered an upstream grouping column if both of the following are true:

  • The primary table for the data context is a reference table. Reference tables are a particular type of table classification in Axiom that can only have one key column, and can serve as the lookup source for a validated column. Reference tables are also often referred to as dimension tables, as many reference tables are used to define dimensional data such as department, account, or entity.

  • The row dimension column is from a related table instead of from a dimension table or the primary table.

Design considerations and limitations when using upstream grouping columns

When upstream grouping columns are used in a report, the data query uses different syntax than when using standard row dimensions. This special syntax causes the following design considerations and limitations:

  • Aggregations: Average and Distinct Count aggregation types cannot be used in the report.

  • Data impact: When rendering the report, any data from the primary table that is not referenced by the upstream grouping column is omitted from the report. For example, imagine that the primary table is Encounter, and you are grouping by a column in the upstream EncounterPayor table. If there are records in the Encounter table that the EncounterPayor table does not reference, those records are omitted from the report.

  • Total row: When the current row dimension is an upstream grouping column, the totals shown in the total row may not match the sum of all the displayed rows. This is because multiple values in the upstream grouping column may reference the same record in the primary table, causing that record to be included multiple times. The total row displays the total as if each record from the primary table is only included once. When this situation occurs, a warning icon displays on the total row, with an explanation of this effect in the tooltip.

    For example, imagine that the primary table is Encounter, and you are grouping by a column in the upstream EncounterPayor table. Payor A and Payor B both reference Encounter 100, so values associated with Encounter 100 are included in both payor rows in the report. Rather than double-count the values from Encounter 100, the total row only counts the values once.