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 #

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.

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
// Local variables
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(/*ads*/ 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_field(/*field*/ "amount").is_format = "$#,##0"
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 #

The aggregate, filter, date and separator constants are read on the component: uo_crosstab.AGG_SUM. Those of a field or a value (SHOW_*, CF_*) are read on the class of its handle: n_pbt_crosstab_field.CF_SCALE, n_pbt_crosstab_value.SHOW_PCT_GRAND. AGG_COUNT counts the non-empty values, like Excel's Count; AGG_COUNT_ROWS counts the rows, empty ones included.

Properties #

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.

The values of is_show and is_conditional_formatting are also available as constants on the handle (SHOW_PCT_COL, CF_SCALE…). Set on the field, is_show, is_conditional_formatting and is_format reach every value of the field in the Values area and the ones that enter it later — set before the field is in Values, they are kept, read back as set and applied when it enters. For one value alone (the same field twice: the amount, and its share of the total), use the value handle, below.

// 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.

Value properties #

of_value (integer ai_position) returns the handle of one value of the Values area, by its position (1 = the first). A field can sit there twice — the amount, and its share of the grand total — and each value has its own settings, which take precedence over those of its field. The handle designates a position: after of_remove_value, of_move_value, of_remove_field, of_remove_calc_measure, of_clear_layout or of_set_layout it is released — ask of_value again. Its SHOW_* and CF_* constants are those of n_pbt_crosstab_field.

// Local variables
n_pbt_crosstab_value lnv_share, lnv_amount

// The amount twice in Values : the sum, then its share of the grand total
uo_crosstab.of_add_value_field(/*field*/ "amount", /*agg*/ uo_crosstab.AGG_SUM)
uo_crosstab.of_add_value_field(/*field*/ "amount", /*agg*/ uo_crosstab.AGG_SUM)

// The second value only : its share of the grand total, under its own caption
lnv_share = uo_crosstab.of_value(/*position*/ 2)
lnv_share.is_show = n_pbt_crosstab_value.SHOW_PCT_GRAND
lnv_share.is_label = "Share of sales"

// The first value : two decimals, negative amounts in red
lnv_amount = uo_crosstab.of_value(/*position*/ 1)
lnv_amount.is_format = "#,##0.00"
lnv_amount.ib_negative_red = true

Methods #

Feeding and naming #

Building the table #

The aggregates accepted by of_add_value_field are carried by the component as constants: AGG_SUM (default), AGG_COUNT (the number of non-empty values, like Excel's Count), AGG_COUNT_ROWS (the number of rows, empty ones included), AGG_DISTINCT_COUNT (distinct value count, empty cells ignored as in Excel), AGG_AVG, AGG_MIN, AGG_MAX. A sum with no value at all is an empty cell, not 0; a "difference from the previous one" where either cell is empty is empty too. Percentages are written the way the display language writes them (57.9% in English, 57,9 % in French).

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. The Options menu of the grid (the gear) offers the same four switches, one entry per axis: Grand total row, Grand total column, Row subtotals, Column subtotals.

Filtering #

The values of_set_member_filter and of_set_member_order expect are those the grid keeps, not always those it shows: on a field derived from a date, a month is written "01"…"12", a quarter "1"…"4", a year "2024"; a date member that is not grouped is written yyyy-mm-dd. The user, for his part, finds the label filter in the chip menu of every field — text, number or date.

Formatting #

The format of a value is set through a property: of_field("amount").is_format = "$#,##0.00" for every value of a field, of_value(2).is_format for one alone (see Field properties and Value properties). It is a PowerBuilder display format, read by the mask engine of the library; empty, the value takes the Format of the DataWindow column. Negatives in red: of_value(n).ib_negative_red = true. The thousands separator of the values with no format stays is_thousands, the currency of the chip menu is_currency_symbol.

Dates and calculated fields #

A formula accepts the + - * / ( ) operators, numbers (12, 0.5, .5, 1e3, always with a point) and field names in square brackets; ^ is not an operator. An invalid formula raises ue_calc_field_error — nothing crashes.

Expanding, saving, exporting #

Shared #

Events #

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, copying aside (it raises ue_copy), 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. The table is built without a mouse — chips, menus, collapsing and sorting: 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. Selecting cells and copying them (Ctrl+C) need the mouse today.

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_field(/*field*/ "amount").is_format = "$#,##0"
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(/*ads*/ 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_field(/*field*/ "avg_price").is_format = "$#,##0.00"

// 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*/ uo_crosstab.DATE_YEAR)
uo_crosstab.of_group_date_field(/*field*/ "sale_date", /*part*/ uo_crosstab.DATE_QUARTER)
uo_crosstab.of_group_date_field(/*field*/ "sale_date", /*part*/ uo_crosstab.DATE_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*/ uo_crosstab.VALUEFILTER_TOP, /*a*/ 10, /*b*/ 0, /*measure_index*/ 1)

On a field placed under another one, the ranking is made inside each parent: the best city of each region, not the best city of the country. Ties all stay.

// The cities under the regions
uo_crosstab.of_add_row_field(/*field*/ "region")
uo_crosstab.of_add_row_field(/*field*/ "city")

// The best city of each region, on the first measure
uo_crosstab.of_set_value_filter(/*field*/ "city", /*type*/ uo_crosstab.VALUEFILTER_TOP, /*a*/ 1, /*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)
// Local variables
long ll_rows[], ll_count, ll_i

// The rows of the DataStore behind the double-clicked figure
ll_count = uo_crosstab.of_get_cell_rows(/*row_tuple_json*/ as_row_tuple_json, /*col_tuple_json*/ as_col_tuple_json, /*rows*/ ll_rows)

// Nothing to show : no row, or a cell that cannot be read now
if ll_count <= 0 then return

// Copy them into the detail list : ids_sales is the DataStore given to of_from_datastore
dw_detail.Reset()
for ll_i = 1 to ll_count
	ids_sales.RowsCopy(ll_rows[ll_i], ll_rows[ll_i], Primary!, dw_detail, dw_detail.RowCount() + 1, Primary!)
next

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.

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.

← Component reference · Guide contents