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

To add the pivot data to the grid data file

  1. 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.

  2. 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.

  3. In the Data method, change the return statement as follows.

    return buildPivotedView(data);
    
  4. 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

  1. From the GridUtils folder, open GridUtilities.cs.

  2. 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

  1. Open JSGridWebPartUserControl.ascx.cs.

  2. 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.

See Also

Reference

Microsoft.SharePoint.JSGrid

PivotedGridColumn

Concepts

JS Grid Control