Difference between revisions of "HTML Presentation Object: PivotGrid"

From Mea Wiki
Jump to navigation Jump to search
Line 90: Line 90:
 
</pre>
 
</pre>
  
Define the following HTML code.
+
Then use the following HTML code:
 
<pre>
 
<pre>
 
<div id="pivot<#uniqueId>"></div>
 
<div id="pivot<#uniqueId>"></div>

Revision as of 12:03, 6 December 2017

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:

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>