Pivot Grid
Displays a pivot grid from a resultset.
Supports row, column and cell links.
To define a column link use colid={0} in passing fields.
To define a row link use rowid={2} in passing fields.
To define a cell link use rowid={2}&colid={0} in passing fields.
Cell links are only visible from the navigator. The grid can be dynamically sorted by columns and/or rows by the user.
New to the component?
There's a tutorial.
Appearance
Component settings
Enable sorting
Whether the user can sort the grid.
Excel export format
The format of the generated Excel file. The default value is taken from the ExcelExportDefaultDocType application variable.
| Possible value | Description |
|---|---|
| xlsx | The document is generated as a Microsoft Excel 2007 (*.xlsx) document. InfoSql and style information will be rendered. |
| text | The document is generated as tab separated text. InfoSql and style information will not be rendered. This mode causes a warning when opened in Microsoft Excel 2007 and later versions. |
Number of visible column links
The maximum number of links to show per column. Any additional links are made available in the Navigator.
Number of visible row links
The maximum number of links to show per row. Any additional links are made available in the Navigator.
SQL
SQL Call: Retrieve grid data (mandatory)
SQL that returns data for the pivot grid. The original sorting order of the grid will be the order of appearance of rows and columns in this result set.
May modify database:
No
Resultset: Grid data
Deprecated. Use the resultsets column data, row data and cell data instead.
Resultset that is displayed in the pivot grid.
Resultset that is displayed in the pivot grid.
Table count:
repeated exactly once
Row count:
one or more rows
Columns
<column with ordinal 1> mandatory string
Column id value.
<column with ordinal 2> mandatory string
Column label. Must be the same for all instances of the column, i.e. where column id is the same.
<column with ordinal 3> mandatory string
Row id value.
<column with ordinal 4> mandatory string
Row label. Must be the same for all instances of the row, i.e. where row id is the same.
<column with ordinal 5> mandatory any
Data to display in cell.
<colname>_html optional string
Where <colname> refers to the name of column 5. HTML formatting to use in cell. Use <#colname#> to refer to the HTML-escaped data in <column with ordinal 5>. This formatting is ignored when exporting to Excel.
Uses the "Layouted HTML" HTML Level.
Uses the "Layouted HTML" HTML Level.
<colname>_style optional string
Where <colname> refers to the name of column 5. CSS style to add to cell in pivot. This formatting is kept when exporting to Excel. Never use a string from a user as part of this column for security reasons.
<colname>_tooltip optional string
Where <colname> refers to the name of column 5. Tooltip to show for pivot cell.
<xxx> optional string
Extra information which can be referred from 'depends on' and 'passing fields'. If these are referred from row or column links the information must be the same for all instances of the row or column, i.e. where row/column id is the same, otherwise the result is undefined.
Resultset: Column groups (optional)
Retrieves the column groups that can be referenced by the column data result set. May only be used together with the column data, row data and cell data resultsets.
Table count:
repeated zero or one time
Row count:
zero or more rows
Columns
ColumnGroupId mandatory int
The id of the column group.
Label mandatory string
The label of the column group.
Resultset: Column data
Resultset containing the column ids and labels that is displayed in the pivot grid. Must be used together with the row data and cell data resultsets.
Table count:
repeated exactly once
Row count:
one or more rows
Columns
<column with ordinal 1> mandatory string
Column id value.
ColumnLabel mandatory string
Column label value.
ColumnGroupId optional int
The column group to display the column in.
<xxx> optional string
Extra information which can be referred from 'depends on' and 'passing fields' in column links.
Resultset: Row data
Resultset containing the row ids and labels that is displayed in the pivot grid. Must be used together with the column data and cell data resultsets.
Table count:
repeated exactly once
Row count:
zero or more rows
Columns
<column with ordinal 1> mandatory string
Row id value.
RowLabel mandatory string
Row label value.
<xxx> optional string
Extra information which can be referred from 'depends on' and 'passing fields' in row links.
Resultset: Cell data
Resultset containing the cell data that is displayed in the pivot grid. Must be used together with the column data and row data resultsets.
Table count:
repeated exactly once
Row count:
zero or more rows
Columns
<column with ordinal 1> mandatory string
Column id value. Must have the same label as ordinal 1 in column data table.
<column with ordinal 2> mandatory string
Row id value. Must have the same label as ordinal 1 in row data table.
<column with ordinal 3> mandatory string
Data to display in cell.
<colname>_html optional string
Where <colname> refers to the name of column 3. HTML formatting to use in cell. Use <#colname#> to refer to the HTML-escaped data in <column with ordinal 3>. This formatting is ignored when exporting to Excel.
Uses the "Layouted HTML" HTML Level.
Uses the "Layouted HTML" HTML Level.
<colname>_style optional string
Where <colname> refers to the name of column 3. CSS style to add to cell in pivot. This formatting is kept when exporting to Excel. Never use a string from a user as part of this column for security reasons.
<colname>_tooltip optional string
Where <colname> refers to the name of column 3. Tooltip to show for pivot cell.
<xxx> optional string
Extra information which can be referred from 'depends on' and 'passing fields' in pivot cell links.
SQL Call: Validate parameters
Allows you to validate the SQL parameters before any other SQL is run in the component. This call is only made if the SQL is a stored procedure and Validate parameters is checked.
May modify database:
No
Parameters
@Force
bit
Set to 1 if the last call to validate parameters used admin_force and the user clicked OK in the OK/Cancel dialog.
@ValidateParams
bit
Set to 1 when this call is made.
Resultset: Messages (optional)
Table count:
repeated zero or one time
Row count:
zero or one row
Columns
ADMIN_Force optional string
Message asking the end user to confirm their parameters.
ADMIN_Message optional string
Message explaining why the parameters are rejected.
InfoSQL
See the InfoSQL documentation for details.
Custom access control and logging
SQL Call: Custom access control and logging
Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.
Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.
The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.
If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.
May modify database:
Yes
Resultset: Access permissions
Return whether the user is allowed to visit the menu item with the current parameters.
Table count:
repeated exactly once
Row count:
exactly one row
Columns
GrantAccess mandatory bit
1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.
If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.
If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.
Querystring parameters
menuitemheading
optional
String that replaces the menu item name when the menu item is loaded. It does not replace the name before that (like for example in the navigator). It is ignored if the menu item is displayed as a part in a multipart in which case the name can be set from the multipart procedure.
Examples
Many-to-many visualization
Example of how a many-to-many-relationship can be displayed in a pivot grid by first defining rows and columns followed by the actual data we want to show.
CREATE PROCEDURE dbo.Pivot_Test
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON
SELECT
D.DayId,
D.DayName AS ColumnLabel
FROM
(
VALUES -- Alla possible columns
( 1, 'Monday' ),
( 2, 'Tuesday' ),
( 3, 'Wednesday' ),
( 4, 'Thursday' ),
( 5, 'Friday' )
) D (DayId, DayName);
SELECT
C.ColorId,
C.ColorName AS RowLabel
FROM
(
VALUES -- All possible rows
(1, 'White'),
(2, 'Blue'),
(3, 'Pink'),
(4, 'Yellow')
) C (ColorId, ColorName);
SELECT -- All cells that should have a "Yes"
CW.DayId,
CW.ColorId,
CASE WHEN CW.ColorId IS NOT NULL THEN 'Yes' ELSE NULL END [CellValue]
FROM
(
VALUES
(1, 1), -- White shirt on Monday or Thirsday
(1, 4),
(2, 1), -- Blue shirt on Monday or Tuesday
(2, 2),
(4, 4), -- Yellow shirts on Wednesday or Thursdays
(4, 3),
(3, 5) -- Pink shirt och Fridays
) CW (ColorId, DayId);
END