crosstab — `u_pbt_crosstab` #

← Component reference · Guide contents

A complete crosstab table: row / column / value areas fed from a DataStore, aggregates, filters, conditional formatting, date grouping, calculated measures, and CSV / Excel exports.

▶ See it live — Demo application, Crosstab tile: the preview, the code behind it and this page, side by side.

At a glance #

Userobject	`u_pbt_crosstab`
Item class	— (fields are placed through methods)
Used for	Giving your users a cross-tab analysis of your data that they rearrange themselves, without writing SQL or exporting to Excel
Demo mode limit	500 source rows processed; CSV and Excel exports and copying (Ctrl+C) disabled — see demo mode

How it works #

You hand the component a flat data set — a DataStore, so any query already written in your application. The crosstab takes care of the rest: it derives the field list, and you distribute those fields across four areas.

Area	What it holds	Effect
Rows	Grouping fields	One row level per field, collapsible
Columns	Grouping fields	One column header level per field
Values	Numeric fields and their aggregate	What gets calculated in the cells
Filters	Selection fields	A filter above the table, applied to everything

All the computing happens inside the component: once the data has been handed over, rearranging the table triggers no round trip to the database or to PowerBuilder.

Quick start #

// window open event
datastore lds
lds = create datastore
lds.dataobject = "d_sales"
lds.SetTransObject(SQLCA)
lds.Retrieve()

// 1. Hand over the data: fields are derived from the columns
uo_crosstab.of_from_datastore(/*data*/ lds)

// 2. Distribute the fields across the areas
uo_crosstab.of_add_row_field(/*field*/ "region")
uo_crosstab.of_add_col_field(/*field*/ "year")
uo_crosstab.of_add_value_field(/*field*/ "amount", /*agg*/ uo_crosstab.AGG_SUM)

// 3. Present: amount format and grand totals
uo_crosstab.of_set_value_format(/*field*/ "amount", /*decimals*/ 0, /*thousands*/ "locale", /*suffix*/ "$", /*symbol_before*/ true)
uo_crosstab.ib_row_grand_total = true
uo_crosstab.ib_col_grand_total = true

// ue_cell_double_clicked event of uo_crosstab: (string as_row_tuple_json, string as_col_tuple_json, integer ai_measure, double ad_value)
// The user wants the detail behind a figure: open the matching list.
of_open_detail(as_row_tuple_json, as_col_tuple_json)

Constants #

Constant	Value	For
`TOTALS_BOTTOM` · `TOTALS_TOP`	`"bottom"` `"top"`	`is_totals_position`
`VALUES_COLS` · `VALUES_ROWS`	`"cols"` `"rows"`	`is_values_axis`
`AGG_SUM` · `AGG_COUNT` · `AGG_DISTINCT_COUNT`	`"sum"` `"count"` `"dcount"`	`of_add_value_field`
`AGG_AVG` · `AGG_MIN` · `AGG_MAX`	`"avg"` `"min"` `"max"`	`of_add_value_field`
`LABEL_GT` · `LABEL_LT` · `LABEL_BETWEEN`	`"gt"` `"lt"` `"between"`	`of_set_label_filter`
`LABEL_CONTAINS` · `LABEL_BEGINS` · `LABEL_ENDS`	`"contains"` `"begins"` `"ends"`	`of_set_label_filter`

The aggregate constants are read on the component: uo_crosstab.AGG_SUM. Those of a field (SHOW_*, CF_*) are read on the field handle.

Properties #

Property	Type	Default	Purpose
`is_totals_position`	string	`"bottom"`	Where the grand total row sits: `TOTALS_BOTTOM` (at the foot, the default) or `TOTALS_TOP` (at the top, right below the headers). Only the grand total moves: the subtotal of a group stays on the group's own line
`is_values_axis`	string	`"cols"`	How measures are laid out when there is more than one: `VALUES_COLS` (side by side in columns, the default) or `VALUES_ROWS` (stacked in rows)
`is_currency_symbol`	string	`""`	The currency the Number format menu of a value chip offers, next to No symbol and %. Empty = the display language's (`$` in English, `€` elsewhere)
`is_thousands`	string	`"locale"`	Thousands separator of the measures that set none (`of_set_value_format` with an empty separator) — the same setting as the Options menu of the grid: `THOUSANDS_LOCALE` (the display language's, the default), `THOUSANDS_SPACE`, `THOUSANDS_NONE`, or the separator itself (`","`, `"."`, `" "`)
`ib_field_list`	boolean	`true`	Shows the field panel, where the user rearranges the table with the mouse
`ib_row_subtotals`	boolean	`true`	Shows the subtotal of each row group, written on the line of the group itself, above its members; off, that line keeps its label and shows no figure
`ib_col_subtotals`	boolean	`true`	Shows a subtotal for each column group
`ib_row_grand_total`	boolean	`true`	Shows the grand-total row under the table (the twin of `ib_col_grand_total`)
`ib_col_grand_total`	boolean	`true`	Shows the grand-total column after the table (the twin of `ib_row_grand_total`)
`ib_copy_headers`	boolean	`true`	Ctrl+C copies the headers with the selected cells: the column names on a first line (the measure is named there only when the table shows it: several values, or no field in Columns) and the member of each row — its whole path, "Nord / Lille" — in a first column, whose corner carries the names of the row fields ("region / ville"). A paste in a spreadsheet thus says what the figures are. `false` copies the figures alone. Read live, set back to `true` by `of_reset`
`ib_enabled`	boolean	`true`	Greyed out: the grid still shows its figures — an empty crosstab is a different thing from one the application has switched off — but it stops answering the pointer
`is_theme_style`	string	`""`	Visual style of the component (`THEME_STYLE_*` constants); empty = the application's, followed at every change
`is_theme_mode`	string	`""`	Light or dark variant (`THEME_MODE_*` constants); empty = the application's, followed at every change
`il_theme_accent`	long	`-1`	Accent colour of this component (`-1` = the application accent, or the theme's)
`is_tooltip`	string	`""`	Simple tooltip shown when hovering the component
`is_super_tooltip_title`	string	`""`	Title of the rich tooltip (takes precedence over `is_tooltip`)
`is_super_tooltip_text`	string	`""`	Text of the rich tooltip (rich markup accepted)
`is_super_tooltip_image`	string	`""`	Image of the rich tooltip

Field properties #

of_field (string as_field) returns the handle of a field: you fetch it once, then you drive the field through its properties. The handle is created on the first call and reused afterwards.

Property	Type	Default	Purpose
`is_label`	string	DataWindow header	Readable caption for the field (`"amount"` → `"Revenue"`). By default the header text of the column in the DataWindow, else its name; an empty string gives it back. Set by your code, it survives `of_from_datastore`
`is_show`	string	`"normal"`	What the cell displays: `"normal"`, `"pctGrand"` (% of the grand total), `"pctRow"` (% of the row), `"pctCol"` (% of the column), `"running"` (running total), `"diff"` (difference from the previous one)
`is_conditional_formatting`	string	`"none"`	Conditional formatting: `CF_NONE`, `CF_SCALE` (color scale), `CF_BARS` (bars inside the cell) or `CF_ICONS` (an arrow by third: up, level, down)

The values of is_show and is_conditional_formatting are also available as constants on the handle (SHOW_PCT_COL, CF_SCALE…). Set before the field is in Values, they are kept, read back as set and applied when it enters.

// Local variables
n_pbt_crosstab_field lnv_field

// The amount field : its caption and a colour scale
lnv_field = uo_crosstab.of_field(/*field*/ "amount")
lnv_field.is_label = "Revenue"
lnv_field.is_conditional_formatting = lnv_field.CF_SCALE

⚠️ Breaking change. This property used to be called is_cf: the abbreviation said nothing at the call site. The old name no longer exists — code that uses it does not compile. The replacement is mechanical: is_cf → is_conditional_formatting, with no change in values or behaviour.

Methods #

Feeding and naming #

Method Purpose

of_from_datastore (datastore ads_data) Hands over the data set: fields are derived from the DataStore columns, their captions from the header text. A text that holds a tab or a line break stays in its row; an empty value (NULL, empty text) is one (blank) member, sorted last; the captions set by is_label survive a reload. Returns 0 once the data is loaded, -5 if the DataStore is not valid or has no column, -2 if the component is not created

of_field (string as_field) Returns the handle of a field, to caption or format it (see Field properties)

Method	Purpose
`of_from_datastore (datastore ads_data)`	Hands over the data set: fields are derived from the DataStore columns, their captions from the header text. A text that holds a tab or a line break stays in its row; an empty value (NULL, empty text) is one (blank) member, sorted last; the captions set by `is_label` survive a reload. Returns `0` once the data is loaded, `-5` if the DataStore is not valid or has no column, `-2` if the component is not created
`of_field (string as_field)`	Returns the handle of a field, to caption or format it (see Field properties)

Building the table #

Method	Purpose
`of_clear_layout ( )`	Empties all four areas: the table goes back to blank, the data stays loaded. Returns `0` once applied, `-2` if the component is not created
`of_add_row_field (string as_field)`	Adds a field to the Rows area (the call order sets the level order). A field already in Values stays there: it can be in both (a distinct count per member). Returns `0` once applied, `-5` for a field the data does not hold or for a calculated measure (it goes in Values only), `-2` if the component is not created
`of_add_col_field (string as_field)`	Adds a field to the Columns area. A field already in Values stays there: it can be in both (a distinct count per member). Returns `0` once applied, `-5` for a field the data does not hold or for a calculated measure (it goes in Values only), `-2` if the component is not created
`of_add_value_field (string as_field, string as_agg)`	Adds a measure to the Values area, along with its aggregate (`AGG_*`, empty = `AGG_SUM`). The field keeps its place in Rows, Columns or Filters. Returns `0` once applied, `-5` for a field the data does not hold or another aggregate, `-2` if the component is not created
`of_add_filter_field (string as_field)`	Adds a field to the Filters area, above the table. Returns `0` once applied, `-5` for a field the data does not hold or for a calculated measure (it goes in Values only), `-2` if the component is not created
`of_remove_field (string as_field)`	Takes a field out of the table: out of every zone it sits in (Rows, Columns, Filters, and each of its values), with its filters — like "Remove" from the field list. The rest of the layout stays as it is, and `ue_layout_changed` tells the new one. Returns `0` once applied, `-4` when the field sits in no zone, `-5` for a field the data does not hold

The aggregates accepted by of_add_value_field are carried by the component as constants: AGG_SUM (default), AGG_COUNT, AGG_DISTINCT_COUNT (distinct value count, empty cells ignored as in Excel), AGG_AVG, AGG_MIN, AGG_MAX.

Totals and subtotals #

Totals are set through properties, described above: ib_row_grand_total and ib_col_grand_total for the grand totals, ib_row_subtotals and ib_col_subtotals for the subtotals, is_totals_position for where the grand-total row sits.

Filtering #

Method	Purpose
`of_set_member_filter (string as_field, string as_values_tab)`	Keeps only the listed values of a field. Values are separated by tabs (`~t`); an empty list removes the filter, like `of_set_member_order`. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created
`of_clear_member_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created
`of_set_value_filter (string as_field, string as_type, double ad_a, double ad_b, integer ai_measure_index)`	Filters on the total of measure number `ai_measure_index` (the first one = `1`): `VALUEFILTER_TOP` or `VALUEFILTER_BOTTOM` (the first or last `ad_a`), `VALUEFILTER_GT`, `VALUEFILTER_LT` (above, under `ad_a`), `VALUEFILTER_BETWEEN` (between `ad_a` and `ad_b`). Returns `0` once applied, `-5` for a field the data does not hold, another type or a position with no measure in Values, `-2` if the component is not created
`of_clear_value_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created
`of_set_label_filter (string as_field, string as_type, double ad_a, double ad_b)`	Numeric filter on the value of the field itself: `LABEL_GT`, `LABEL_LT`, `LABEL_BETWEEN`. Returns `0` once applied, `-5` for a field the data does not hold or another type, `-2` if the component is not created
`of_set_label_filter (string as_field, string as_type, string as_a, string as_b)`	Text filter on the value of the field: `LABEL_CONTAINS`, `LABEL_BEGINS`, `LABEL_ENDS` (`as_a`, case ignored; `as_b` is unused). Returns `0` once applied, `-5` for a field the data does not hold, another type or an empty text, `-2` if the component is not created
`of_clear_label_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created

Formatting #

Method	Purpose
`of_set_value_format (string as_field, integer ai_decimals, string as_thousands, string as_suffix { , boolean ab_symbol_before })`	Format of a measure: number of decimals (0 to 6), thousands separator (`","`, `"."`, `" "`: the separator itself, `","` going with a decimal point and `"."` with a decimal comma; `"space"` thin space, `"none"`, `"locale"` the display language's; `""` leaves the measure on `is_thousands`, the default of the Options menu), symbol — after the number by default (`1 234 EUR`), BEFORE it when `ab_symbol_before` is `true` (`$1,234`). The three settings belong to THIS measure; set before the field is in Values, they are kept and applied when it enters. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created
`of_clear_value_format (string as_field)`	Back to the default format. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created
`of_set_member_order (string as_field, string as_values_tab)`	Display order forced on the values of a field (separated by `~t`); an empty string restores the natural order. Returns `0` once applied, `-5` for a field the data does not hold, `-2` if the component is not created

Dates and calculated fields #

Method	Purpose
`of_group_date_field (string as_field, string as_part)`	Creates a field derived from a date, datetime or timestamp column (a time alone has no date to group): `DATE_YEAR`, `DATE_QUARTER` or `DATE_MONTH`, named `<column>__<part>`. It joins the field list and is used like any other. Returns `0` once applied, `-5` for a field the data does not hold or another part, `-2` if the component is not created
`of_add_calc_field (string as_name, string as_label, string as_formula)`	Calculated field, evaluated row by row (`"[amount] * 0.8"` = the net of each sale, then summed like any column), usable in any area. Not for a ratio of totals (an average price): that is `of_add_calc_measure`. Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_remove_calc_field (string as_name)`	Removes a calculated field. Returns `0` once applied, `-5` when no calculated field has this name, `-2` if the component is not created
`of_add_calc_measure (string as_name, string as_label, string as_formula)`	Calculated measure, evaluated cell by cell, on the totals (`"[marge] / [ca]"` = overall margin rate). Goes in the Values area only. Its formula names data fields only: another calculated measure is refused (`ue_calc_field_error`). Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_remove_calc_measure (string as_name)`	Removes a calculated measure. Returns `0` once applied, `-5` when no calculated measure has this name, `-2` if the component is not created

A formula accepts the + - * / ( ) operators, numbers, and field names in square brackets. An invalid formula raises ue_calc_field_error — nothing crashes.

Expanding, saving, exporting #

Method	Purpose
`of_expand_all ( )` · `of_collapse_all ( )`	Expands or collapses every group, rows AND columns. Returns `0` once applied, `-2` if the component is not created
`of_expand_to_level (integer ai_level)`	Expands down to a given level (`1` = first level only). Returns `0` once applied, `-2` if the component is not created
`of_get_layout ( )` → string	Returns the full state of the table — store it as-is, then replay it through `of_set_layout`
`of_set_layout (string as_state_json)`	Restores a state obtained earlier. Returns `0` once applied, `-5` for an empty text or one that is not JSON, `-2` if the component is not created
`of_get_cell_value (string as_row_tuple_json, string as_col_tuple_json, integer ai_measure, ref double ad_value)`	Reads the value of one cell, the one the table shows (0.8 under 80.0 %, the running total under a running total). Its row and its column are the tuples `ue_cell_double_clicked` gives — `{"region":"Nord","city":"Lille"}`, an empty member `null`, `{}` (or an empty string) for the total — and `ai_measure` is the position of the value in the Values area, from 1. A tuple names the fields of its zone from the first one down: `{"region":"Nord"}` is the subtotal of Nord. The answer does not depend on the display: a collapsed branch or hidden subtotals are read all the same. Returns `0` with the value in `ad_value` (`NULL` for a cell with no value), `-4` when the table has no such cell (a member, or a field, it does not show there), `-5` for a malformed tuple or a position with no measure, `-2` if the component is not created
`of_export_csv (string as_path)`	Writes a CSV file of the current view (UTF-8 with a BOM, semicolon separated); `ue_csv_saved` confirms. The numbers take the decimal separator of the display language (a comma in French, German, Italian, Spanish, Portuguese); a label that would read as a formula (`=` `+` `-` `@`) is written with a leading apostrophe. The twin of `of_export_xlsx`. Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_export_xlsx (string as_path)`	Writes an Excel file of the table as it is shown, formatting included (negatives in red too); `ue_xlsx_saved` confirms — or refuses with its reason past 16,384 columns or 1,048,576 rows, the limits of an Excel sheet. Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created

Shared #

Method	Purpose
`of_reset ( )`	Returns the component to its brand-new state. Returns `0` once applied, `-2` if the component is not created
`of_set_redraw (boolean)`	Batches a burst of changes into a single render. Returns `0` once applied, `-2` if the component is not created
`of_save_as_png (string)` · `of_save_as_jpg (string)`	Exports the rendering as an image. Returns `0`, `-2` if the component is not created, `-4` if the capture fails, `-5` on an empty path

Events #

Event	Raised when
`ue_layout_changed (string as_layout_json)`	The user has rearranged the table (moved a field, changed an aggregate, sorted a column, collapsed a group…): everything `of_get_layout` saves
`ue_cell_double_clicked (string as_row_tuple_json, string as_col_tuple_json, integer ai_measure, double ad_value)`	Double-click on a cell: the first two arguments describe the intersection (`{"region":"Nord","city":"Lille"}`, an empty member is `null` there, `{}` for the total), `ai_measure` the position of the double-clicked value in the Values area, from 1, and `ad_value` the value the cell shows — `0.8` under "80.0 %", the running total under a running total — or `NULL` for an empty cell. The two tuples and `ai_measure` make the address `of_get_cell_value` reads back. This is the entry point for a drill-down
`ue_csv_saved (string as_path, boolean ab_ok, string as_error)`	The CSV file has been written — or not, and `as_error` says why
`ue_xlsx_saved (string as_path, boolean ab_ok, string as_error)`	The Excel file has been written — or not, and `as_error` says why
`ue_calc_field_error (string as_field, string as_message)`	A calculated field or measure formula is invalid
`ue_copy (string as_tsv)`	The user has copied a cell selection (Ctrl+C): it is up to you to put it on the clipboard. `as_tsv` is a tab-separated text that carries the headers by default — the column names on a first line, the member of each row (its whole path, "Nord / Lille") in a first column — or the figures alone with `ib_copy_headers = false`. In demo mode copying is an export: refused, and the grid says so
`ue_ready ( )`	The component has finished loading; everything sent beforehand has been replayed
`ue_runtime_missing ( )`	The WebView2 runtime is missing: the component stays empty
`ue_bg_color (long al_color)`	The component has computed its theme background color; the userobject has already adopted it (`backcolor`)

What the user can do without a single line of code #

The table is alive: that is the whole point of the component. With the field panel shown (ib_field_list = true), the user can:

drag a field from one area to another, and rearrange the cross-tab at will;
change the aggregate of a measure (sum, average, count…);
filter the values of a field through a check list;
collapse or expand a row or column group;
sort on a header;
select then copy a block of cells, with its headers so a paste in a spreadsheet says what the figures are (ib_copy_headers).

Each of these actions comes back in ue_layout_changed: combined with of_get_layout / of_set_layout, this lets you offer "saved views" to your users.

From the keyboard. Every one of those gestures is reachable without a mouse: Tab lands on a field, a collapse triangle or a sortable header, Enter or Space triggers it. On a field it opens its menu — the one carrying Add to rows / columns / values / filters and Remove: the whole build of the table goes through it. Shift+F10 or the Menu key open that menu too, and the focus follows the field just moved, even to another zone.

Examples #

A complete sales report #

// Rows: region, then city within each region
uo_crosstab.of_clear_layout()
uo_crosstab.of_add_row_field(/*field*/ "region")
uo_crosstab.of_add_row_field(/*field*/ "city")

// Columns: one per year
uo_crosstab.of_add_col_field(/*field*/ "year")

// Cells: the total amount
uo_crosstab.of_add_value_field(/*field*/ "amount", /*agg*/ uo_crosstab.AGG_SUM)

// Presentation: readable amounts, subtotals and grand totals
uo_crosstab.of_set_value_format(/*field*/ "amount", /*decimals*/ 0, /*thousands*/ "locale", /*suffix*/ "$", /*symbol_before*/ true)
uo_crosstab.ib_row_subtotals = true
uo_crosstab.ib_row_grand_total = true
uo_crosstab.ib_col_grand_total = true

// Color the cells to spot the big amounts at a glance
uo_crosstab.of_field(/*field*/ "amount").is_conditional_formatting = n_pbt_crosstab_field.CF_SCALE

Readable captions #

Your columns are often named mt_ht or cd_reg. Rename them once and for all, right after of_from_datastore.

// The data first : the fields take the names of the columns
uo_crosstab.of_from_datastore(/*data*/ lds)

// Then a readable caption for each field
uo_crosstab.of_field(/*field*/ "region").is_label    = "Region"
uo_crosstab.of_field(/*field*/ "city").is_label     = "City"
uo_crosstab.of_field(/*field*/ "category").is_label = "Category"
uo_crosstab.of_field(/*field*/ "year").is_label     = "Year"
uo_crosstab.of_field(/*field*/ "amount").is_label   = "Revenue"
uo_crosstab.of_field(/*field*/ "quantity").is_label  = "Quantity"

Analyzing shares rather than amounts #

// Local variables
n_pbt_crosstab_field lnv_amount

// Start from empty areas, then place the fields
uo_crosstab.of_clear_layout()
uo_crosstab.of_add_row_field(/*field*/ "category")
uo_crosstab.of_add_col_field(/*field*/ "year")
uo_crosstab.of_add_value_field(/*field*/ "amount", /*agg*/ uo_crosstab.AGG_SUM)

// Keep only two categories on screen (values separated by a tab)
uo_crosstab.of_set_member_filter(/*field*/ "category", /*values_tab*/ "Informatique~tMobilier")

// The amount field, to set how it is shown
lnv_amount = uo_crosstab.of_field(/*field*/ "amount")

// Show the share of each cell in the total of its column
lnv_amount.is_show = lnv_amount.SHOW_PCT_COL

// A small bar in each cell to compare the shares at a glance
lnv_amount.is_conditional_formatting = lnv_amount.CF_BARS

A measure of your own: the average price #

A calculated measure is evaluated on the totals of each cell, not row by row: that is what makes a ratio correct.

// Start from empty areas, then place the fields
uo_crosstab.of_clear_layout()
uo_crosstab.of_add_row_field(/*field*/ "region")

// The two totals the calculation will be based on
uo_crosstab.of_add_value_field(/*field*/ "amount",  /*agg*/ uo_crosstab.AGG_SUM)
uo_crosstab.of_add_value_field(/*field*/ "quantity", /*agg*/ uo_crosstab.AGG_SUM)

// Average price = total amount divided by total quantity
uo_crosstab.of_add_calc_measure(/*name*/ "avg_price", /*label*/ "Average price", /*formula*/ "[amount] / [quantity]")
uo_crosstab.of_set_value_format(/*field*/ "avg_price", /*decimals*/ 2, /*thousands*/ "locale", /*suffix*/ "$", /*symbol_before*/ true)

// Then place it among the values like any field (the aggregate does not matter: a measure is computed)
uo_crosstab.of_add_value_field(/*field*/ "avg_price", /*agg*/ uo_crosstab.AGG_SUM)

// ue_calc_field_error event of uo_crosstab: (string as_field, string as_message)
// Invalid formula: warn without breaking anything, the table stays on screen.
uo_status.of_panel(/*key*/ "main").is_text = "Formula " + as_field + ": " + as_message

Analyzing by month, quarter or year #

A date column cannot be cross-tabbed as is — every single day would get its own row. Derive the level you want first.

// Create three fields derived from the sale_date column
uo_crosstab.of_group_date_field(/*field*/ "sale_date", /*part*/ "year")
uo_crosstab.of_group_date_field(/*field*/ "sale_date", /*part*/ "quarter")
uo_crosstab.of_group_date_field(/*field*/ "sale_date", /*part*/ "month")

// Then cross-tab them like any other field: year in columns, quarter underneath
uo_crosstab.of_add_col_field(/*field*/ "sale_date__year")
uo_crosstab.of_add_col_field(/*field*/ "sale_date__quarter")

The top ten regions #

// Keep only the 10 regions with the highest total on the first measure
uo_crosstab.of_set_value_filter(/*field*/ "region", /*type*/ "top", /*a*/ 10, /*b*/ 0, /*measure_index*/ 1)

Exporting #

The export reproduces the current view exactly: same filters, same totals, same formatting.

// To Excel: the file is written straight to the given path
uo_crosstab.of_export_xlsx(/*path*/ "C:\temp\ventes.xlsx")

// ue_xlsx_saved event of uo_crosstab: (string as_path, boolean ab_ok, string as_error)
// inv_notif = an n_pbt_toaster declared as an instance variable of the window
if ab_ok then
    inv_notif.is_title = "Export complete"
    inv_notif.is_text  = as_path
    inv_notif.is_kind  = inv_notif.KIND_SUCCESS
else
    inv_notif.is_title = "Export failed"
    inv_notif.is_text  = as_error
    inv_notif.is_kind  = inv_notif.KIND_ERROR
end if
inv_notif.of_show()

// To CSV: a file, like for Excel; ue_csv_saved confirms
uo_crosstab.of_export_csv(/*path*/ "C:\exports\ventes.csv")

Offering saved views #

// Local variables
string ls_view

// Save the current view : of_get_layout answers straight away
ls_view = uo_crosstab.of_get_layout()

// Keep the content AS IS: it replays without any transformation.
of_save_view(is_current_view, ls_view)

// Later on: replay a saved view
uo_crosstab.of_set_layout(/*state_json*/ of_read_view("Ventes par region"))

Drilling down behind a figure #

// ue_cell_double_clicked event of uo_crosstab: (string as_row_tuple_json, string as_col_tuple_json, integer ai_measure, double ad_value)
// The first two arguments describe the intersection (which row values,
// which column values): enough to rebuild a detail query.
w_sales_detail lw_detail

// Open the detail window with the intersection as its parameter
OpenWithParm(lw_detail, as_row_tuple_json + "|" + as_col_tuple_json)

The two tuples and ai_measure make the address of the cell: of_get_cell_value reads it back later, as the table shows it at that moment.

// ue_cell_double_clicked event of uo_crosstab : the address of the cell, read again later
// Local variables
double ld_value

// Read the cell again from its address : 0 when it exists
if uo_crosstab.of_get_cell_value(/*row_tuple_json*/ as_row_tuple_json, /*col_tuple_json*/ as_col_tuple_json, /*measure*/ ai_measure, /*value*/ ld_value) = 0 then

	// ld_value is what the cell shows ; NULL for an empty cell
end if

A cell can also be read without a click, through its members: a partial tuple names a subtotal. And of_remove_field takes a field out of all its zones at once, filters included.

// The sales of Nord in 2025, first measure of Values
// Local variables
double ld_north

// A partial address : the region and the year, no other member
uo_crosstab.of_get_cell_value(/*row_tuple_json*/ '{"region":"Nord"}', /*col_tuple_json*/ '{"year":"2025"}', /*measure*/ 1, /*value*/ ld_north)

// Take the year out of the table, filters included
uo_crosstab.of_remove_field(/*field*/ "year")

Best practices #

Call of_from_datastore only once per data set: rearranging the table afterwards costs nothing, handing the data over again is expensive.
Set the captions (of_field("...").is_label) right after of_from_datastore: they follow the field everywhere, including in the field panel and in the exports.
Filter on the SQL side whatever is not meant to be analyzed: the crosstab is fast, but a DataStore half the size opens twice as fast.
of_clear_layout() empties the areas without handing the data over again: that is the right call for offering several analyses on the same source.
A date field is always cross-tabbed through of_group_date_field, never directly.
Leave the field panel visible on analysis screens, hide it on fixed dashboards.

Inherited from the common base #

These members exist on every visual component — they are not specific to this one. They are detailed once, in the transverse chapters; this table only says where to read them.

Members	Role	Detailed in
`of_count` · `of_keys_at` · `of_has`	Walk what the component holds	3.2 Items
`of_reset`	Put the component back to zero	3.6 Resetting a component: `of_reset()`
`of_set_property` · `of_get_property` · `of_component_name`	Driving a property by its name	3.1 The property engine
`of_register_shortcut` · `of_clear_shortcuts`	The component's keyboard chords	3.5 Keyboard shortcuts
`of_is_created` · `of_is_ready` · `of_get_last_error`	Whether it was born, whether it is ready, what failed	3.7 Diagnostics
`of_save_as_png` · `of_save_as_jpg`	Export the rendering as an image	3.8 Exporting the rendering as an image
`of_set_redraw`	Group changes into a single repaint	3.10 Best practices
`of_preload_icons`	Icons shown with no delay	Instant display: `of_icon`
`of_set_translation`	Translate one of the component's labels	5.2 Adapting a label: `of_set_translation`
`of_focus_webview`	Give the component the focus	6.4 Keyboard and focus
`of_print` · `of_print_to_pdf`	Print, or write a PDF	6.9 Printing

Two helpers are not inherited: of_icon and of_escape_markup live on n_pbt_utils. Declare one — n_pbt_utils lnv_utils, nothing to create — and call them on it.