`<rollup>`

Start with an arbitrary amount of data and tabulate it by grouping like values in a column or multiple columns.

Description

<rollup> specifies that a tabulation should contain multiple levels of aggregation.

For example, if the breaks attribute specifies a single break column, then a tabulation with <rollup> will contain one row for each unique value in the specified column, plus one row for a grand total. If there are two break columns, ColA and ColB, then the rollup tabulation will have one row for each unique value of ColA and ColB combined, plus one for each unique value of ColA (with ColB "rolled up"), plus one row for a grand total. If type="cube" were specified in <rollup>, then there would also be one row for each unique value of ColB.

Syntax

<tabu label="[TABULATION_TITLE]" breaks="[GROUPING_COLUMN]">
      <rollup cols="[COL_NAME_1], [COL_NAME_2], ...[COL_NAME_N]"
              sets="[SET_1];[SET_2];...[SET_N]"/>
      <tcol source="[AGGREGATION_COLUMN]" fun="[FUNCTION]"
            label="[COLUMN_HEADING]"/>
</tabu>

Attributes

type

The default is hier.

Valid Values for the type attribute:

hier

The tabulation will contain rows for:

col1...coln
col1...coln-1 (with coln rolled up)
col1...coln-2 (with coln-1 and coln-2 rolled up)

and so on through

col1 (with all other columns rolled up)

and finally

all rows rolled up (grand total)

cube

For type="cube", the tabulation will contain all 2^n combinations of the break columns, including a grand total with all columns rolled up.

Note: The maximum number of column breaks for a cubed rollup is 5.

cols

Specifies a comma-separated list of one more columns, which must be a subset of the tabulation's break columns.

Only the specified columns participate in the rollup. The break columns not specified in cols are never rolled up in the final tabulation.

If cols is omitted, the default is for all the break columns in the tabulation to participate in the rollup.

sets

Specifies one or more sets, where each set is a different comma-separated list of columns. Multiple sets are separated by semi-colons.

The columns in each of the sets must be among the break columns or within the subset of columns specified by the cols attribute.

(Available as of version 10.19)

ind

Specifies the name of a column, which is created by the tabulation and adjoined to the results, that contains an encoded value indicating the level of the rollup at each row (i.e., which break columns are rolled up vs. not rolled up).

The column name specified by ind may not conflict with any of the break column names nor any of the tcol column names (including automatically-generated ti names).

If ind is omitted, no indicator column is created.

Note: <rollup> may not be used in a cross-tabulation (i.e., if cbreaks is present in the <tabu> tag).

Example

In this example, the base table is pub.demo.retail.item, a transactional sales table. The tabulation will group results by the store column and return total sales for each store number. This example also specifies <rollup cols="account"/>, which will show an aggregation for every unique combination of values for a store and a customer.

<base table="pub.demo.retail.item"/>
<tabu label="Tabulation on Sales Item Detail" breaks="store,account">
    <break col="store" sort="up"/>
    <rollup cols="account"/>
    <tcol source="sales" fun="sum" label="Sum of`Sales"/>
</tabu>