In this tutorial you will get to know the Pivot Grid component by building and testing various features.
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.
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.
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®.
SELECT 1 AS [ColumnId], 'Column label' AS [ColumnLabel];
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®.
SELECT 1 AS [RowId], 'Row label' AS [RowLabel];
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];
The result is a grid with one column, one row and a value in the single cell, as shown in the picture below:
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.
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.
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.
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.
-- 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.
-- 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;
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.
-- 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.
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.
-- 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:
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:
Here is the stored procedure where the examples are taken from: SalesReport_Pivot.sql
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.
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.
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!