- How to?
- Filtering the row or column headers
- Using a filter procedure
Filtering the headers and the content of a Pivot Table
By default, the content of a pivot table is calculated from all the data found in the data files taken into account by the Pivot Table control.
To filter the data displayed in the Pivot Table control, you can:
These elements must be defined BEFORE calculating the content of the Pivot Table control.
Filtering the row or column headers
To filter the values displayed by the row or column headers, use the MinValue and MaxValue properties.
For example, a Pivot Table control displays the sales of products by country and by year. To filter the countries and to display the countries starting with a letter included between A and F, the corresponding code is:
COL_Country.MinValue = "A"
COL_Country.MaxValue = "F"
For the same example, to display the sales included between 2010 and 2012, the corresponding code is:
COL_OrderDate_Year.MinValue = "2010"
COL_OrderDate_Year.MaxValue = "2012"
Using a filter procedure
A filter procedure can be defined when describing the row or column headers. This procedure is mainly used to speed up the calculation of the pivot table by reducing the volume to process.
To define a filter procedure:
- Display the description window of the row or column header to filter.
- In the details of the header, click "None" next to "Filter".
- A window is displayed, allowing you to select the filter procedure:
- If this procedure already exists in your project, select the requested procedure.
- If this procedure does not exist, click the "Create a procedure" button. In this case:
- A window is displayed, allowing you to specify the name of the local procedure to create. Validate.
- A window is displayed, allowing you to select the items whose value will be automatically assigned to the parameters of the procedure:
- Click "Add a parameter".
- Select the element to process in the procedure. This element comes from the source of values for the header.
- Repeat these operations if necessary to add new parameters.
- Click (if necessary) the "Fill the procedure" button to enter the code of the procedure in the code editor. The code editor is displayed
- Validate the control description window.
Content of the filter procedure
The filter procedure is used to filter the column header according to the specified parameters.
In order for the current record to be taken into account in the pivot table, the procedure must use the following code line:
In order for the current record to be ignored in the pivot table, the procedure must use the following code line:
Example: Selecting some countries in a pivot table representing the sales by country and by year:
IF Upper(pParam_Country) IN ("FRANCE", "ITALY", "GERMANY", "SPAIN", ...
"GREECE", "PORTUGAL", "SWITZERLAND", "BELGIUM") THEN
: You also have the ability to associate a filter procedure with a row or column header via FilterProcedure