Difference between revisions of "HTML Presentation Object: PivotGrid"

From Mea Wiki
Jump to navigation Jump to search
(Created page with "In addition to the PivotGrid presentation object, pivot can be implemented more custom and flexible way using HTML Properties|HTML presentation obj...")
 
m (Ollvihe moved page PivotGrid using HTML Presentation Object to HTML Presentation Object: PivotGrid without leaving a redirect)
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
In addition to the [[Pivot Grid Properties|PivotGrid presentation object]], pivot can be implemented more custom and flexible way using [[HTML Properties|HTML presentation object]].
+
In addition to the [[Pivot Grid Properties|PivotGrid presentation object]], pivot can be implemented more custom and flexible way using [[HTML Properties|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
  
Following example code draws a PivotGrid using data that is embedded in the code.
+
== Static data example: Sales data ==
 +
Following example code draws a PivotGrid using data that is embedded in HTML the code.
 
<pre>
 
<pre>
 
<div id="pivot<#uniqueId>"></div>
 
<div id="pivot<#uniqueId>"></div>
Line 31: Line 35:
 
     { Amount: 300, Country: "United States", Date: "FY 2007", Product: "Van", Quantity: 4, State: "South Carolina" }
 
     { Amount: 300, Country: "United States", Date: "FY 2007", Product: "Van", Quantity: 4, State: "South Carolina" }
 
];
 
];
var initialized<#uniqueId> = false;
 
  
function <#contextChangeFunction>(changedContextVariables) {
+
function <#resizeFunction>(newWidth, newHeight) {
   if (initialized<#uniqueId>) return;
+
   $("#pivot<#uniqueId>").width(newWidth - 3);
   initialized<#uniqueId> = true;
+
  $("#pivot<#uniqueId>").height(newHeight - 3);
 +
   var gridObj = $("#pivot<#uniqueId>").data("ejPivotGrid");
 +
  if (gridObj != null) gridObj.destroy();
 
   $("#pivot<#uniqueId>").ejPivotGrid({
 
   $("#pivot<#uniqueId>").ejPivotGrid({
 +
    frozenHeaderSettings: {
 +
      enableFrozenColumnHeaders: true,
 +
      enableFrozenRowHeaders: true
 +
    },
 
     dataSource: {
 
     dataSource: {
 
       data: dataset<#uniqueId>,
 
       data: dataset<#uniqueId>,
Line 70: Line 79:
 
</script>
 
</script>
 
</pre>
 
</pre>
 +
 +
== 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":
 +
<pre>
 +
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'))
 +
</pre>
 +
 +
Then use the following HTML code:
 +
<pre>
 +
<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>
 +
[[Category: QPR UI]]

Latest revision as of 16:46, 6 February 2018

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>