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. The pivot grid show users as rows and groups as columns. If a user belongs to a group, there is "1" in the corresponding pivot cell. Note that this example needs QPR Reporting Expression datasource installed.

Create a view and add an 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'))

Then use 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>