Differences

This shows you the differences between two versions of the page.

--- wiki:pivotexample [2014/02/17 13:28]
tony created
+++ wiki:pivotexample [2014/02/17 15:30]
tony
@@ Line 6: / Line 6: @@
 Let’s say that we have a table with  sales data with the following fields:
-productname
+  * category name
-categoryname
+  * product name
-country
+  * country
-totalprice
+  * totalprice
-quantity.
+  * quantity
 This can be seen on the picture below:
 {{:wiki:pivotsource.png|}}
+This grid view is achieved with jqGrid with the following  code:
+<code javascript>
+jQuery(document).ready(function(){
+	jQuery("#grid").jqGrid(
+	{
+		url : "data.json",
+		loadonce: true,
+		colModel : [
+			{ name: "CategoryName"},
+			{ name: "ProductName" },
+			{ name: "Country"},
+			{ name: "Price", "formatter": 'number', align: "right"},
+			{ name: "Quantity", formatter:'integer', align:"right"}
+		],
+		datatype:"json",
+		width: 700,
+		rowNum : 10,
+		pager: "#pager",
+		caption: "Grid"
+	});
+});
+</code>
+The data is obtained from Northwind database with the following SQL:
+<code sql>
+SELECT
+    c.CategoryName,
+    b.ProductName,
+    e.Country,
+    SUM( a.Quantity * a.UnitPrice ) AS Price,
+    SUM(a.Quantity) AS Quantity
+FROM
+    order_details a,
+    products b,
+    categories c,
+    orders d,
+    customers e
+WHERE
+    a.ProductID = b.ProductID
+    AND b.CategoryID = c.CategoryID
+    AND a.OrderID = d.OrderID
+    AND d.CustomerID = e.CustomerID
+    AND (e.Country = 'UK' OR e.Country = 'USA')
+GROUP BY
+    a.ProductID,
+    e.Country
+</code>
+The JSON response from the server obtained via ajax ( the option url:"data.json") has the following structuure:
+<code javascript>
+{"rows":[
+{"CategoryName":"Beverages","ProductName":"Steeleye Stout","Country":"UK","Price":"1008.0000","Quantity":"65"},
+{"CategoryName":"Beverages","ProductName":"Laughing Lumberjack Lager","Country":"USA","Price":"140.0000","Quantity":"10"},
+{"CategoryName":"Beverages","ProductName":"Lakkalik","Country":"USA","Price":"2160.0000","Quantity":"120"},
+...
+]}
+</code>
+Let say that your boss want to know the sales for the categories and products for the given countries.\\
+Of course this can be achieved with another SQL query, but ... your provider does not allow you to add new query or to chenge the existing one. What to do?\\
+You can use the jqPivotGrid for this purpose without to make changes to the server side responses. Let see how to do this.
+===== Options settings =====
+Looking into the boss task we can see that the categories and products are fields which will be used in our grid. This data is "static" and we should tell jqPivotGrid this. This is achieved with xDimension option:
+<code javascript>
+...
+xDimension : [
+   {
+       dataName: 'CategoryName',
+       label : 'Category',
+       width : 90
+   }, {
+       dataName: 'ProductName',
+       label: 'Product',
+       width: 90
+   }
+]
+</code>
+With this setting we tell jqPivotGrid that the data will be grouped first by Category and then by Product. \\
+These fields should be taken from the JSON response which names are CategoryName and ProductName (see the JSON  response above). \\
+Next we should tell jqPivotGrid which field is the country field. With this setting we will generate dynamic columns (yDimension).\\
+Seen into the source data this is the Country field. Now the settings should be:
+<code javascript>
+...
+yDimension : [
+   {
+      dataName: 'Country'
+   }
+]
+</code>
+Now the missed part is which fields we should actually summarize? This is achieved with the aggregates option using the fields Price and Quantity.
+<code javascript>
+aggregates : [
+	{
+           member : 'Price',
+           aggregator : 'sum',
+           width:50,
+           label:'Sum Price'
+        }, {
+           member : 'Quantity',
+           aggregator : 'sum',
+           width:50,
+           label:'Sum Qty'
+        }
+]
+</code>
+The missed settings are how we can make sumaries on columns and rows. This of cource is achieved with two line of code - \\
+rowTotal : true and \\
+colTotal: true
+The final code with jqGrid setting is:
+<code>
+<script type="text/javascript">
+jQuery(document).ready(function(){
+	jQuery("#grid").jqGrid('jqPivot',
+	"data1.json",
+	// pivot options
+	{
+		xDimension : [
+                   {dataName: 'CategoryName', label : 'Category', width: 90},
+                   {dataName: 'ProductName', label : 'Product', width:90}
+                ],
+		yDimension : [
+                   {dataName: 'Country'}
+                ],
+		aggregates : [
+			{member : 'Price', aggregator : 'sum', width:50, label:'Sum Price'},
+			{member : 'Quantity', aggregator : 'sum', width:50, label:'Sum Qty'}
+		],
+		rowTotals: true
+		colTotals : true
+	},
+	// grid options
+	{
+		width: 700,
+		rowNum : 10,
+		pager: "#pager",
+		caption: "Pivot Grid"
+	});
+});
+</script>
+  </head>
+  <body>
+...
+     <table id='grid'></table>
+     <div id='pager'></div>
+...
+   </body>
+</html>
+</code>

Differences

Navigation

Search

Toolbox

QR Code