version

Component tutorial - Pivot Grid

In this tutorial you will get to know the Pivot Grid component by building and testing various features.

Introduction

The Pivot Grid is mainly used to aggregate data into different groups. It's like the Grid component but with dynamic columns as well as rows.

How the component works

The Pivot Grid component expects three resultsets. The first resultset defines the columns. The second resultset defines the rows and the third resultset selects the cell data.

In the most basic example we define one column, one row and select cell data for our single cell.

Column definition

For the first resultset, which defines the columns, the first column in the resultset is the column id value which must be unique. There must also be a column with alias "ColumnLabel", the value in this column will be displayed in Softadmin®.

Example:

SELECT
	1 AS [ColumnId],
	'Column label' AS [ColumnLabel];

Row definition

The second resultset, which defines the rows, is like the first resultset. The first column in the resultset is the row id value which must be unique and there must be a column with alias "RowLabel", the value in this column will be displayed in Softadmin®.

Example:

SELECT
	1 AS [RowId],
	'Row label' AS [RowLabel];

Cell data

In the third resultset we select the cellvalues. Every combination of columns and rows will result in a cell that will be displayed in Softadmin® and we can refer to all these combinations in our third resultset. If a cell doesn't receive a value from the thirds resultset it will be blank in Softadmin®.

SELECT
	1 AS [ColumnId],
	1 AS [RowId],
	'A cell value' AS [CellValue];

Result

The result is a grid with one column, one row and a value in the single cell, as shown in the picture below:

Task

In this task you will create a sales report where each column will represent a day, week, month, quarter, or year through parameter choice by the user.

Each row will represent a sales department. Sales department are also connected to a company and the user should be able to choose whether to display a sum row per company or not, and if a total sum row should be displayed.

Every sale is connected to a sales department and has a date and time when the sale was done along with a value. The cells in the report should summarize the sales value for the rows sales department during the columns time period. The user should also be able to filter between which dates the report show data.

Preparation

For the task we need a few tables with some data. Here PivotGridTutorialCreateSchemaAndTables.sql is a .SQL-script which you can run in a database of your choice. For example, in a new empty system on Sandbox.

The script will take a few minutes to run due to the random generation of sales data.

After the task is completed another script to remove the schema and its content will be provided.

Implementation

Create a menu item and set the component to "Pivot Grid". The stored procedure can for example be named "PivotGridComponentTutorial.SalesReport_Pivot".

Next, we need parameters. The user should be able to choose time unit for the columns, specify a date span and choose whether company sum and total sum should be displayed or not.

Create the parameters by adding them in the menu item:

Below is an example of how the parameters for the menu item can look:

For the dropdown "Time unit", you can enter the following constants (or make a stored procedure for them):

Then add the parameters as input to the stored procedure:

CREATE OR ALTER PROCEDURE PivotGridComponentTutorial.SalesReport_Pivot
	@TimeUnit varchar(30),
	@DateFrom date = NULL,
	@DateTo date = NULL,
	@DoIncludeCompanySum bit,
	@DoIncludeTotalSum bit

Validate that @DateFrom is not after @DateTo and give the user an error message if that's the case. A stored procedure containing all the examples will be provided at the end of the task.

Define columns and rows

The first resultset will define the columns as described in the basic example. The function SoftadminUtil.DatePeriod_Intervals which is available in Softadmin® can be used to get all date intervals for a specified time unit. The columns will be sorted in the Pivot Grid in the same order as in the resultset that defines the columns.

Example:

-- Column definition
CREATE TABLE #ColumnData
(
	ColumnId int PRIMARY KEY NOT NULL,
	ColumnLabel varchar(MAX) NOT NULL,
	IntervalStart datetime2(2) NOT NULL,
	IntervalStop datetime2(2) NOT NULL
);

-- Saving the column information in a temp table so that it can be used
later when retrieving cell data.
INSERT INTO #ColumnData
(
	ColumnId,
	ColumnLabel,
	IntervalStart,
	IntervalStop
)
SELECT
	DPI.IntervalId,
	DPI.DescriptionShort,
	DPI.IntervalStart,
	DPI.IntervalStop
FROM
	SoftadminUtil.DatePeriod_Intervals(@TimeUnit, @DateFrom, @DateTo, @LanguageIdEnglish) DPI;

-- Send the column definition to the component.
SELECT
	CD.ColumnId,
	CD.ColumnLabel
FROM
	#ColumnData CD;

Now the component knows which columns to display. Next up is the row definition. All sales department should have one row each, and there should be a sum row for each company and a total sum row depending on what the user chose for those parameters.

Let's also make sure that sum rows are displayed below the rows they summarize.

The rows will be sorted in the Pivot Grid in the same order as in the resultset that defines the rows.

Example:

-- Row definition
CREATE TABLE #RowData
(
	RowId int PRIMARY KEY NOT NULL,
	CompanyId int NULL,
	SalesDepartmentId int NULL
);

-- Saving the row information in a temp table so that it can be used later when retrieving cell data.
INSERT INTO #RowData
(
	RowId,
	CompanyId,
	SalesDepartmentId
)
SELECT
	ROW_NUMBER() OVER(ORDER BY ISNULL(C.CompanyId, 999999), ISNULL(SD.SalesDepartmentId, 999999)) AS [RowId], -- Let's assume there will not be more than 999999 companies or sales departments. Making sure we have NULL-values last since they will be used for sum rows.
	C.CompanyId,
	SD.SalesDepartmentId
FROM
	PivotGridComponentTutorial.SalesDepartment SD
	JOIN PivotGridComponentTutorial.Company C ON
		C.CompanyId = SD.CompanyId
GROUP BY
	ROLLUP(C.CompanyId, SD.SalesDepartmentId); -- Will result in one row with SalesDepartmentId = NULL for each CompanyId and one row with both of them NULL, which can be used for sum rows.

-- If the user chose to not display total sum
IF @DoIncludeTotalSum = 0
BEGIN

	DELETE
		RD
	FROM
		#RowData RD
	WHERE
		RD.CompanyId IS NULL;

END;

-- If the user chose to not display company sums
IF @DoIncludeCompanySum = 0
BEGIN

	DELETE
		RD
	FROM
		#RowData RD
	WHERE
		RD.SalesDepartmentId IS NULL AND
		RD.CompanyId IS NOT NULL; -- The total sum row where both CompanyId and SalesDepartmentId is null is handled above.

END;

-- Send the row definition to the component
SELECT
	RD.RowId,
	CASE
		WHEN C.CompanyId IS NULL
			THEN '<b>Sum</b>' -- Let's make the total sum row bold
		WHEN SD.SalesDepartmentId IS NULL
			THEN CONCAT('<b><i>', C.CompanyName, ' - Sum</i></b>') -- Let's make the company sums bold and italic, since they will be mixed in with the sales departments rows.
		ELSE
			CONCAT(C.CompanyName, ' - ', SD.SalesDepartmentName)
	END AS [RowLabel]
FROM
	#RowData RD
	LEFT JOIN PivotGridComponentTutorial.Company C ON 
		C.CompanyId = RD.CompanyId
	LEFT JOIN PivotGridComponentTutorial.SalesDepartment SD ON 
		SD.SalesDepartmentId = RD.SalesDepartmentId;

Select cell data

It's time to fill the cells with data! Since we stored the column and row information in temp tables we can for example cross join them and then apply the sum for each cell.

The sort order for the columns and rows are defined already by the earlier resultsets that defined the columns and rows. So in the cell data resultset the order doesn't matter.

Example:

-- Cell data
SELECT
	CD.ColumnId, -- Which column the value will be displayed in, must be the first column
	RD.RowId, -- Which row the value will be displayed in, must be the second column
	CONVERT(int, CED.SumSalesValue) AS [SumSalesValue], -- The cell value, must be the third column
	IIF(
		RD.CompanyId IS NOT NULL AND
		RD.SalesDepartmentId IS NULL,
		'background-color:#D0D0D0', -- Making the company sum rows gray to make them stand out more
		''
	) AS [SumSalesValue_Style]
FROM
	#ColumnData CD
	CROSS JOIN #RowData RD
	OUTER APPLY (
		SELECT
			SUM(S.SalesValue) AS [SumSalesValue]
		FROM
			PivotGridComponentTutorial.Sale S
			JOIN PivotGridComponentTutorial.SalesDepartment SD ON 
				SD.SalesDepartmentId = S.SalesDepartmentId				
		WHERE
			S.DatetimeInsert BETWEEN CD.IntervalStart AND CD.IntervalStop AND -- The column temp table holds the start date and stop date for the columns interval, which now is used as a filter for the sales.
			(
				S.SalesDepartmentId = RD.SalesDepartmentId -- Specific sales department
				OR
				( -- Company sum
					SD.CompanyId = RD.CompanyId AND
					RD.SalesDepartmentId IS NULL
				)
				OR
				( -- Total sum
					RD.SalesDepartmentId IS NULL AND
					RD.CompanyId IS NULL
				)
			)
	) CED;

Try different combinations of parameter input and see how the component adds columns depending on the definition that the stored procedure sends to the component.

Column groups

Since our report can span over several years, let's add column groups to show which year a column belongs to.

Add a new resultset to send column group definitions to the component. Alter the resultset for the column definition so that each column states which column group it belongs to.

Example:

-- Column definition
CREATE TABLE #ColumnData
(
	ColumnId int PRIMARY KEY NOT NULL,
	ColumnLabel varchar(MAX) NOT NULL,
	IntervalStart datetime2(2) NOT NULL,
	IntervalStop datetime2(2) NOT NULL,
	ColumnGroupId int NOT NULL,
	ColumnGroupLabel varchar(30) NOT NULL
);

-- Saving the column information in a temp table so that it can be used later when retrieving cell data.
INSERT INTO #ColumnData
(
	ColumnId,
	ColumnLabel,
	IntervalStart,
	IntervalStop,
	ColumnGroupId,
	ColumnGroupLabel
)	
SELECT
	DPI.IntervalId,
	DPI.DescriptionShort,
	DPI.IntervalStart,
	DPI.IntervalStop,
	DENSE_RANK() OVER(ORDER BY YEAR(DPI.IntervalStart)),
	YEAR(DPI.IntervalStart)
FROM
	SoftadminUtil.DatePeriod_Intervals(@TimeUnit, @DateFrom, @DateTo, @LanguageIdEnglish) DPI;

-- Column group definition
SELECT
	CD.ColumnGroupId,
	CD.ColumnGroupLabel AS [Label]
FROM
	#ColumnData CD
GROUP BY
	CD.ColumnGroupId,
	CD.ColumnGroupLabel;

-- Send the column definition to the component.	
SELECT
	CD.ColumnId,
	CD.ColumnLabel,
	CD.ColumnGroupId -- Tell the component which column group the column should be placed in
FROM
	#ColumnData CD;

Try the report and see how the column group is displayed. It should look something like this:

Disable sorting

The pivot grid can be sorted both by columns and rows. When it's not desirable to allow the user to sort the result it can be disabled. Edit the menu item and disable sorting:

Example stored procedure

Here is the stored procedure where the examples are taken from: SalesReport_Pivot.sql

Finish

A few more things to mention are that the excel export format can be set to either xlsx (default), text or none and that the component supports row links as well as column links and cell links.

Clean up

Here PivotGridTutorialDropSPsTablesAndSchema.sql is a .SQL-script to drop the tables and the schema which was created before. This script also drops the stored procedure if it exist. If you made any other objects, you also need to remove them to be able to run the script fully since the schema wont be able to be dropped unless no objects reference it.

Feedback

Thank you for completing the tutorial for the Pivot Grid component. Please send us feedback by e-mail to KompetensagarePG@multisoft.se so that we can improve this tutorial!

Component tutorial - Pivot Grid

Introduction

How the component works

Column definition

Row definition

Cell data

Result

Task

Preparation

Implementation

Create a menu item and parameters

Define columns and rows

Select cell data

Column groups

Disable sorting

Example stored procedure

Finish

Clean up

Feedback