How to: Create a Pivot Grid Using the JS Grid Control

SharePoint 2010

Last modified: June 23, 2011

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.

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 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;
    }
    
    
    

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;
    }
    
    

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));
    

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.

Show:
© 2014 Microsoft