HTML Presentation Object: PivotGrid

In addition to the PivotGrid presentation object, pivot can be implemented more custom and flexible way using HTML presentation object. QPR UI contains Syncfusion PivotGrid, which can be used in HTML presentation objects. More information about PivotGrid configuration:

• PivotGrid demos: http://js.syncfusion.com/demos/web/#!/flatlight/pivotgrid/relational/defaultfunctionalities
• PivotGrid documentation: https://help.syncfusion.com/js/pivotgrid/overview
• PivotGrid settings reference: https://help.syncfusion.com/api/js/ejpivotgrid

Static data example: Sales data

Following example code draws a PivotGrid using data that is embedded in HTML the code.

<div id="pivot<#uniqueId>"></div>
<script>
  var dataset<#uniqueId> = [
    { Amount: 100, Country: "Canada", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Alberta" },
    { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Van", Quantity: 3, State: "British Columbia" },
    { Amount: 300, Country: "Canada", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Brunswick" },
    { Amount: 150, Country: "Canada", Date: "FY 2008", Product: "Bike", Quantity: 3, State: "Manitoba" },
    { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Car", Quantity: 4, State: "Ontario" },
    { Amount: 100, Country: "Canada", Date: "FY 2007", Product: "Van", Quantity: 1, State: "Quebec" },
    { Amount: 200, Country: "France", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Charente-Maritime" },
    { Amount: 250, Country: "France", Date: "FY 2006", Product: "Van", Quantity: 4, State: "Essonne" },
    { Amount: 300, Country: "France", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Garonne (Haute)" },
    { Amount: 150, Country: "France", Date: "FY 2008", Product: "Van", Quantity: 2, State: "Gers" },
    { Amount: 200, Country: "Germany", Date: "FY 2006", Product: "Van", Quantity: 3, State: "Bayern" },
    { Amount: 250, Country: "Germany", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Brandenburg" },
    { Amount: 150, Country: "Germany", Date: "FY 2008", Product: "Car", Quantity: 4, State: "Hamburg" },
    { Amount: 200, Country: "Germany", Date: "FY 2008", Product: "Bike", Quantity: 4, State: "Hessen" },
    { Amount: 150, Country: "Germany", Date: "FY 2007", Product: "Van", Quantity: 3, State: "Nordrhein-Westfalen" },
    { Amount: 100, Country: "Germany", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Saarland" },
    { Amount: 150, Country: "United Kingdom", Date: "FY 2008", Product: "Bike", Quantity: 5, State: "England" },
    { Amount: 250, Country: "United States", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Alabama" },
    { Amount: 200, Country: "United States", Date: "FY 2005", Product: "Van", Quantity: 4, State: "California" },
    { Amount: 100, Country: "United States", Date: "FY 2006", Product: "Bike", Quantity: 2, State: "Colorado" },
    { Amount: 150, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "New Mexico" },
    { Amount: 200, Country: "United States", Date: "FY 2005", Product: "Bike", Quantity: 4, State: "New York" },
    { Amount: 250, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "North Carolina" },
    { Amount: 300, Country: "United States", Date: "FY 2007", Product: "Van", Quantity: 4, State: "South Carolina" }
];

function <#resizeFunction>(newWidth, newHeight) {
  $("#pivot<#uniqueId>").width(newWidth - 3);
  $("#pivot<#uniqueId>").height(newHeight - 3);
  var gridObj = $("#pivot<#uniqueId>").data("ejPivotGrid");
  if (gridObj != null) gridObj.destroy();
  $("#pivot<#uniqueId>").ejPivotGrid({
    frozenHeaderSettings: {
      enableFrozenColumnHeaders: true,
      enableFrozenRowHeaders: true
    },
    dataSource: {
      data: dataset<#uniqueId>,
      rows: [
        {
          fieldName: "Country",
          fieldCaption: "Country"
        },
        {
          fieldName: "State",
          fieldCaption: "State"
        }
      ],
      columns: [
        {
          fieldName: "Product",
          fieldCaption: "Product"
        }
      ],
      values: [
        {
          fieldName: "Amount",
          fieldCaption: "Amount"
        },
        {
          fieldName: "Quantity",
          fieldCaption: "Quantity"
        }
      ]
    }
  });
}
</script>

Dynamic data example: Users and groups

This example shows how to make a pivot grid that gets its data from QPR Suite. Note that this example needs QPR Reporting Expression datasource installed.

Create a view and add a HTML presentation object. Add the following dataset with name "userData":

users=RemoveColumns(AddColumn(From('[UM].users', '', '', 'name(as="username"),fullname,id(as="userid"),ingroups', ''), 'User', '[fullname] + \' (\' + [username] + \')\''), Array('username', 'fullname'))
groups=From('[UM].groups', '', '', 'name(as="Group"),id(as="groupid")', '')
joined=RemoveColumns(InnerJoin([users], [groups], 'Contains([ingroups], [groupid])'), Array('ingroups', 'groupid', 'userid'))

Define the following HTML code.

<div id="pivot<#uniqueId>"></div>

<script>
var poData<#uniqueId>;
var width<#uniqueId>;
var height<#uniqueId>;

function <#datasetChangeFunction>(datasetIdentifier, datasetChangeCallbackFunction) {
  if (datasetIdentifier == "userData") {
    datasetChangeCallbackFunction(myDatasetAvailable<#uniqueId>);
  }
}

function myDatasetAvailable<#uniqueId>(datasetData) {
  if (datasetData == null || datasetData.sheets == null || datasetData.sheets.length == 0 || datasetData.sheets[0] == null || datasetData.sheets[0].values == null || datasetData.sheets[0].values.length == 0) return;
  poData<#uniqueId> = [];
  for (var row = 1; row < datasetData.sheets[0].values[0].length; row++) {
    var rowObject = {};
    for (var column = 0; column < datasetData.sheets[0].values.length; column++) {
      rowObject[datasetData.sheets[0].values[column][0].attribute] = datasetData.sheets[0].values[column][row].value;
    }
    poData<#uniqueId>.push(rowObject);
  }
  drawPO<#uniqueId>();
}

function <#resizeFunction>(newWidth, newHeight) {
  width<#uniqueId> = newWidth;
  height<#uniqueId> = newHeight;
  drawPO<#uniqueId>();
}

function drawPO<#uniqueId>() {
  if (poData<#uniqueId> == null || width<#uniqueId> == null) return;
  $("#pivot<#uniqueId>").width(width<#uniqueId> );
  $("#pivot<#uniqueId>").height(height<#uniqueId> - 21);
  var gridObj = $("#pivot<#uniqueId>").data("ejPivotGrid");
  if (gridObj != null) gridObj.destroy();
  $("#pivot<#uniqueId>").ejPivotGrid({
    enableColumnGrandTotal: false,
    frozenHeaderSettings: {
      enableFrozenColumnHeaders: true,
      enableFrozenRowHeaders: true
    },
    enableColumnResizing: true,
    dataSource: {
      data: poData<#uniqueId>,
      rows: [
        {
          fieldName: "User",
          fieldCaption: "Group"
        }
      ],
      columns: [
        {
          fieldName: "Group",
          fieldCaption: "Group"
        }
      ],
      values: [
        {
          fieldName: "User",
          fieldCaption: "",
 summaryType: "count"
        }
      ]
    }
  });
}

</script>
<pre>