Sample pivot table reports

Genero Studio provides two sample pivot table reports: a static pivot table and a dynamic pivot table.

The Reports.4pw project contains the sample pivot table reports. Find this project in the My Genero Files/samples/Reports directory. Open the project and find the pivot table reports under OrderReport > Designs.

Static pivot table sample report

The report name is StaticPivotTable.4rp.

This sample report produces a table of customer data, grouped by customers and orders. The input is presorted. The dimension columns, the userid and orderid, are populated accordingly.

Dynamic pivot table sample report

The report name is DynamicPivotTable.4rp.

When this report is selected, a second dialog opens. From this dialog, you select the dimensions and measures included in the report, along with how to sort the measures.

The form pivotdialog.4fd defines the dialog. The module pivotdialog.4gl contains the Genero code driving the dialog, making use of the pivot table library libpivot.4gl.

A new record of type "PivotControlBlock" is shipped in the FIRST PAGE HEADER of the report. The structure of the record:

TYPE PivotControlBlock RECORD
  title STRING,
  drawAs STRING,
  dimensionsDisplaySelection STRING,
  measuresDisplaySelection STRING,
  outputOrder STRING,
  topN INTEGER,
  displayFactRows BOOLEAN,
  displayRecurringValues BOOLEAN,
  computeAggregatesOnInnermostDimension BOOLEAN,
  computeTotal BOOLEAN,
  computeCount BOOLEAN,
  computeDistinctCount BOOLEAN,
  computeAverage BOOLEAN,
  computeMinimum BOOLEAN,
  computeMaximum BOOLEAN 
END RECORD

The definition of the record is located in the file globals.4gl.

The record is populated by a call to the dialog function promptForPivotDialogIfAny (from pivotdialog.4gl) in OrderReport.4gl in the sample application. This is the code fragment:

CALL promptForPivotDialogIfAny(filename) RETURNING retval, controlBlock.*

IF NOT retval THEN
  RETURN NULL
END IF

IF NOT fgl_report_loadCurrentSettings(filename) THEN
  EXIT PROGRAM
END IF

The function inspects the 4rp file. If it finds exactly one dynamically configurable pivot table, it will prompt the user to configure it (see pivotdialog.4gl for details).

The last step lies in DynamicPivotTable.4rp where the pivot table properties are defined as RTL expressions that initialize from the field values in control record. For example, the title property is initialized to "controlBlock.title".

This figure shows the Properties view for the pivot table element, showing the various controlBlock values. — Figure: Properties of the Pivot Table element

This figure displays the values of the properties for the pivot table element.