How to: Create a Pivot Grid Using the JS Grid Control
Applies to: SharePoint Foundation 2010
This how-to topic shows how to create a pivot grid by using the JS Grid control and Microsoft Visual Studio 2010. The topic starts where How to: Create a Basic JS Grid Control ends.
The completed pivot grid appears in a pane to the right of the basic grid pane. It shows a pivoted view of the data, with each department displaying quarterly results for two fiscal years. To create the pivot grid, you must provide the pivot grid data, the pivot column definitions, and the grid utility logic to properly construct the grid. The final step is to enable the pivoted grid pane.
Prerequisites
Microsoft SharePoint Foundation 2010
Visual Studio 2010
SharePoint development tools in Microsoft Visual Studio 2010
Completion of How to: Create a Basic JS Grid Control
To add the pivot data to the grid data file
Running as administrator, start Visual Studio 2010 and open the JSGrid solution that you created in How to: Create a Basic JS Grid Control.
Tip
Make a copy of the JSGrid solution before you edit it. This enables you to keep a version of the original solution, which is the starting point for other follow-up scenarios.
Expand the GridUtils folder, and open GridData.cs.
The pivot grid requires quarterly data for each department. Sample data for the basic grid is already in GridData.cs. This procedure adds the quarterly data to that data file.
In the Data method, change the return statement as follows.
return buildPivotedView(data);To add a method that processes quarterly data for the pivoted grid, paste the following code into GridData.cs.
private DataTable buildPivotedView(DataTable data) { // quarter 1 columns data.Columns.Add(new DataColumn("costq 1", typeof(int))); data.Columns.Add(new DataColumn("costq 2", typeof(int))); data.Columns.Add(new DataColumn("costq 3", typeof(int))); data.Columns.Add(new DataColumn("costq 4", typeof(int))); // quarter 2 columns data.Columns.Add(new DataColumn("costq 5", typeof(int))); data.Columns.Add(new DataColumn("costq 6", typeof(int))); data.Columns.Add(new DataColumn("costq 7", typeof(int))); data.Columns.Add(new DataColumn("costq 8", typeof(int))); // build column headers data.Columns.Add(new DataColumn("Quarter 1", typeof(string))); data.Columns.Add(new DataColumn("Quarter 2", typeof(string))); data.Columns.Add(new DataColumn("Quarter 3", typeof(string))); data.Columns.Add(new DataColumn("Quarter 4", typeof(string))); int i = 0; foreach (DataRow dr in data.Rows) { // pivoted view dr["costq 1"] = _rand.Next(1000000) + 30000; dr["costq 2"] = _rand.Next(1000000) + 30000; dr["costq 3"] = _rand.Next(1000000) + 30000; dr["costq 4"] = _rand.Next(1000000) + 30000; dr["costq 5"] = _rand.Next(1000000) + 30000; dr["costq 6"] = _rand.Next(1000000) + 30000; dr["costq 7"] = _rand.Next(1000000) + 30000; dr["costq 8"] = _rand.Next(1000000) + 30000; dr["FY 2009 Estimate"] = ((int)dr["costq 1"] + (int)dr["costq 2"] + (int)dr["costq 3"] + (int)dr["costq 4"]) / 4; dr["FY 2010 Estimate"] = ((int)dr["costq 5"] + (int)dr["costq 6"] + (int)dr["costq 7"] + (int)dr["costq 8"]) / 4; dr["Yearly Estimate"] = ((int)dr["FY 2009 Estimate"] + (int)dr["FY 2010 Estimate"]) / 2; i++; dr["Quarter 1"] = "Quarter 1"; dr["Quarter 2"] = "Quarter 2"; dr["Quarter 3"] = "Quarter 3"; dr["Quarter 4"] = "Quarter 4"; } return data; }Private Function buildPivotedView(ByVal data As DataTable) As DataTable ' quarter 1 columns data.Columns.Add(New DataColumn("costq 1", GetType(Integer))) data.Columns.Add(New DataColumn("costq 2", GetType(Integer))) data.Columns.Add(New DataColumn("costq 3", GetType(Integer))) data.Columns.Add(New DataColumn("costq 4", GetType(Integer))) ' quarter 2 columns data.Columns.Add(New DataColumn("costq 5", GetType(Integer))) data.Columns.Add(New DataColumn("costq 6", GetType(Integer))) data.Columns.Add(New DataColumn("costq 7", GetType(Integer))) data.Columns.Add(New DataColumn("costq 8", GetType(Integer))) ' build column headers data.Columns.Add(New DataColumn("Quarter 1", GetType(String))) data.Columns.Add(New DataColumn("Quarter 2", GetType(String))) data.Columns.Add(New DataColumn("Quarter 3", GetType(String))) data.Columns.Add(New DataColumn("Quarter 4", GetType(String))) Dim i As Integer = 0 For Each dr As DataRow In data.Rows ' pivoted view dr("costq 1") = _rand.Next(1000000) + 30000 dr("costq 2") = _rand.Next(1000000) + 30000 dr("costq 3") = _rand.Next(1000000) + 30000 dr("costq 4") = _rand.Next(1000000) + 30000 dr("costq 5") = _rand.Next(1000000) + 30000 dr("costq 6") = _rand.Next(1000000) + 30000 dr("costq 7") = _rand.Next(1000000) + 30000 dr("costq 8") = _rand.Next(1000000) + 30000 dr("FY 2009 Estimate") = (CInt(Fix(dr("costq 1"))) + CInt(Fix(dr("costq 2"))) + CInt(Fix(dr("costq 3"))) + CInt(Fix(dr("costq 4")))) / 4 dr("FY 2010 Estimate") = (CInt(Fix(dr("costq 5"))) + CInt(Fix(dr("costq 6"))) + CInt(Fix(dr("costq 7"))) + CInt(Fix(dr("costq 8")))) / 4 dr("Yearly Estimate") = (CInt(Fix(dr("FY 2009 Estimate"))) + CInt(Fix(dr("FY 2010 Estimate")))) / 2 i += 1 dr("Quarter 1") = "Quarter 1" dr("Quarter 2") = "Quarter 2" dr("Quarter 3") = "Quarter 3" dr("Quarter 4") = "Quarter 4" Next Return data End Function
To add the pivot grid code to the grid utilities file
From the GridUtils folder, open GridUtilities.cs.
To add a method that parses the pivoted grid data, paste the following code into GridUtilities.cs.
public static IList<PivotedGridColumn> GetPivotedGridColumns(DataTable table) { List<PivotedGridColumn> r = new List<PivotedGridColumn>(); // Create the Pivoted "Header" Column PivotedGridColumn col = new PivotedGridColumn(); col.ColumnKey = "header"; col.FieldKeys = new String[] { "Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4" }; col.Name = "Quarter"; col.Width = 100; r.Add(col); // display col = new PivotedGridColumn(); col.ColumnKey = "tests1"; col.FieldKeys = new String[] { "costq 1", "costq 2", "costq 3", "costq 4" }; col.Name = "Fiscal 2009"; col.Width = 100; r.Add(col); col = new PivotedGridColumn(); col.ColumnKey = "tests2"; col.FieldKeys = new String[] { "costq 5", "costq 6", "costq 7", "costq 8" }; col.Name = "Fiscal 2010"; col.Width = 100; r.Add(col); return r; }Public Shared Function GetPivotedGridColumns(ByVal table As DataTable) As IList(Of PivotedGridColumn) Dim r As New List(Of PivotedGridColumn)() ' Create the Pivoted "Header" Column Dim col As New PivotedGridColumn() col.ColumnKey = "header" col.FieldKeys = New String() {"Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4"} col.Name = "Quarter" col.Width = 100 r.Add(col) ' display col = New PivotedGridColumn() col.ColumnKey = "tests1" col.FieldKeys = New String() {"costq 1", "costq 2", "costq 3", "costq 4"} col.Name = "Fiscal 2009" col.Width = 100 r.Add(col) col = New PivotedGridColumn() col.ColumnKey = "tests2" col.FieldKeys = New String() {"costq 5", "costq 6", "costq 7", "costq 8"} col.Name = "Fiscal 2010" col.Width = 100 r.Add(col) Return r End Function
To enable the pivoted grid pane
Open JSGridWebPartUserControl.ascx.cs.
In the Page_Load method, immediately after the GridSerializer instance (named "gds") was created, add the following code.
gds.EnablePivotedGridPane(GridUtilities.GetPivotedGridColumns(data));gds.EnablePivotedGridPane(GridUtilities.GetPivotedGridColumns(data))
To test the pivot grid Web Part
- In Visual Studio, press F5 to run the project. The Web Part is automatically added to the SharePoint Web Part Gallery. You can add the Web Part to any Web Parts page.