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 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_ventes"
lds.SetTransObject(SQLCA)
lds.Retrieve()

// 1. Hand over the data: fields are derived from the columns
uo_croise.of_set_data(lds)

// 2. Distribute the fields across the areas
uo_croise.of_add_row_field(/*field*/ "region")
uo_croise.of_add_col_field(/*field*/ "annee")
uo_croise.of_add_value_field(/*field*/ "montant", /*aggregate*/ uo_croise.AGG_SUM)

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

// ue_cell_double_clicked event of uo_croise: (string as_row_tuple_json, string as_col_tuple_json, double ad_value)
// The user wants the detail behind a figure: open the matching list.
of_ouvrir_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`

The aggregate constants are read on the component: uo_croise.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)
`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)
`ib_field_list`	boolean	`true`	Shows the field panel, where the user rearranges the table with the mouse
`ib_row_subtotals`	boolean	`true`	Shows a subtotal for each row group
`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_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	`fluent`	Visual style of the component (`THEME_STYLE_*` constants)
`is_theme_mode`	string	`light`	Light or dark variant (`THEME_MODE_*` constants)
`il_theme_accent`	long	`-1`	Accent color of this component (`-1` = the theme accent)
`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	`""`	Readable caption for the field (`"montant"` → `"Revenue"`)
`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) or `CF_BARS` (bars inside the cell)

The values of is_show and is_conditional_formatting are also available as constants on the handle (SHOW_PCT_COL, CF_SCALE…).

n_pbt_crosstab_field lnv_champ

lnv_champ = uo_croise.of_field(/*field*/ "montant")
lnv_champ.is_label = "Revenue"
lnv_champ.is_conditional_formatting = lnv_champ.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_set_data (datastore ads_data)`	Hands over the data set: fields are derived from the DataStore columns, their captions from the header text. 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). Returns `0` once applied, `-2` if the component is not created
`of_add_col_field (string as_field)`	Adds a field to the Columns area. Returns `0` once applied, `-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. Returns `0` once applied, `-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, `-2` if the component is not created

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), AGG_AVG, AGG_MIN, AGG_MAX.

Totals and subtotals #

Method	Purpose

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`). Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_clear_member_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-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: `"top"` (the top `ad_a`), `"gt"`, `"lt"`, `"between"`. `ai_measure_index` designates the measure concerned (the first one = `1`). Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_clear_value_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-2` if the component is not created
`of_set_label_filter (string as_field, string as_type, double ad_a, double ad_b)`	Filters on the value of the field itself: `"gt"`, `"lt"`, `"between"`. Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_clear_label_filter (string as_field)`	Removes that filter. Returns `0` once applied, `-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 (`"locale"`, `"space"`, `"none"`), symbol — after the number by default (`1 234 EUR`), BEFORE it when `ab_symbol_before` is `true` (`$1,234`). Returns `0` once applied, `-5` on an invalid argument, `-2` if the component is not created
`of_clear_value_format (string as_field)`	Back to the default format. Returns `0` once applied, `-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` on an invalid argument, `-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 column: `"year"`, `"quarter"` or `"month"`. It joins the field list and is used like any other. Returns `0` once applied, `-5` on an invalid argument, `-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 (`"[montant] * 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, `-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. 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, `-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 row group. 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` on an invalid argument, `-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 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, formatting included; `ue_xlsx_saved` confirms. 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, collapsed a group…)
`ue_cell_double_clicked (string as_row_tuple_json, string as_col_tuple_json, double ad_value)`	Double-click on a cell: the first two arguments describe the intersection, the third the displayed value. 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
`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.

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.

Examples #

A complete sales report #

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

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

// Cells: the total amount
uo_croise.of_add_value_field(/*field*/ "montant", /*aggregate*/ uo_croise.AGG_SUM)

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

// Color the cells to spot the big amounts at a glance
uo_croise.of_field(/*field*/ "montant").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_set_data.

uo_croise.of_set_data(lds)

uo_croise.of_field("region").is_label    = "Region"
uo_croise.of_field("ville").is_label     = "City"
uo_croise.of_field("categorie").is_label = "Category"
uo_croise.of_field("annee").is_label     = "Year"
uo_croise.of_field("montant").is_label   = "Revenue"
uo_croise.of_field("quantite").is_label  = "Quantity"

Analyzing shares rather than amounts #

n_pbt_crosstab_field lnv_montant

uo_croise.of_clear_layout()
uo_croise.of_add_row_field(/*field*/ "categorie")
uo_croise.of_add_col_field(/*field*/ "annee")
uo_croise.of_add_value_field(/*field*/ "montant", /*aggregate*/ uo_croise.AGG_SUM)

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

lnv_montant = uo_croise.of_field(/*field*/ "montant")

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

// A small bar in each cell to compare the shares at a glance
lnv_montant.is_conditional_formatting = lnv_montant.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.

uo_croise.of_clear_layout()
uo_croise.of_add_row_field(/*field*/ "region")

// The two totals the calculation will be based on
uo_croise.of_add_value_field(/*field*/ "montant",  /*aggregate*/ uo_croise.AGG_SUM)
uo_croise.of_add_value_field(/*field*/ "quantite", /*aggregate*/ uo_croise.AGG_SUM)

// Average price = total amount divided by total quantity
uo_croise.of_add_calc_measure(/*name*/ "prix_moyen", /*caption*/ "Average price", &
                              /*formula*/ "[montant] / [quantite]")
uo_croise.of_set_value_format(/*field*/ "prix_moyen", /*decimals*/ 2, /*thousands*/ "locale", /*symbol*/ "$", /*symbol_before*/ true)

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

// ue_calc_field_error event of uo_croise: (string as_field, string as_message)
// Invalid formula: warn without breaking anything, the table stays on screen.
uo_statut.of_item("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 date_vente column
uo_croise.of_group_date_field(/*field*/ "date_vente", /*level*/ "year")
uo_croise.of_group_date_field(/*field*/ "date_vente", /*level*/ "quarter")
uo_croise.of_group_date_field(/*field*/ "date_vente", /*level*/ "month")

// Then cross-tab them like any other field: year in columns, quarter underneath
uo_croise.of_add_col_field("date_vente__year")
uo_croise.of_add_col_field("date_vente__quarter")

The top ten regions #

// Keep only the 10 regions with the highest total on the first measure
uo_croise.of_set_value_filter(/*field*/ "region", /*type*/ "top", &
                              /*a*/ 10, /*b*/ 0, /*measure*/ 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_croise.of_export_xlsx("C:\temp\ventes.xlsx")

// ue_xlsx_saved event of uo_croise: (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_croise.of_export_csv(/*path*/ "C:\exports\ventes.csv")

Offering saved views #

string ls_vue

// Save the current view : of_get_layout answers straight away
ls_vue = uo_croise.of_get_layout()

// Keep the content AS IS: it replays without any transformation.
of_enregistrer_vue(is_vue_courante, ls_vue)

// Later on: replay a saved view
uo_croise.of_set_layout(of_lire_vue("Ventes par region"))

Drilling down behind a figure #

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

OpenWithParm(lw_detail, as_row_tuple_json + "|" + as_col_tuple_json)

Best practices #

Call of_set_data 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_set_data: 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.