Pivot table report is very powerful and useful tools provided by Microsoft within Ms-Excel. By using pivot table report we can quickly analyze data from different aspects or dimensions. We can dig through the data from different angles to find the useful information. Once data is extracted you just need to drag and drop the data fields into Pivot Table report.
If your data is stored in excel itself or some external file then its ok, but if it is stored in some DBMS (Oracle, Ms-Server, Access etc) you need a little bit knowledge of SQL Language before using Pivot Table reports. If you are lucky DBA within your organization can do (can create query to extract data and store somewhere) it for you. In such case you just need to know where that query is actually stored by DBA.
Suppose you are working in big store and your manager is interested to know whether they have enough stock to full fill the needs of customers? So your main task is to compare the ordered quantity (for different items) and quantity in the stock. To make your job easier you can extract the required information from database by using SQL query. The following query will extract the necessary information from the database.
SELECT Order_Details.ProductID, Sum(Order_Details.Quantity) AS Total_Required, Products.UnitsInStock,
(Products.UnitsInStock-Total_Required) AS Total_Difference, Orders.ShippedDate
FROM Products INNER JOIN (Orders INNER JOIN Order_Details ON Orders.OrderID=Order_Details.OrderID) ON Products.ProductID=Order_Details.ProductID
WHERE IsNull(Orders.ShippedDate)
GROUP BY Order_Details.ProductID, Products.UnitsInStock, Orders.ShippedDate;
In the above query we extracted only those records which are still not shipped. That means still need to be packed. In some databases “Order Status” field can help to indentify such records which still need to pack. So once you designed your query in Ms-Access (or any other database) you can check is it running properly. Now you can represent this information in more meaningful way by creating pivot table report. As you can see in the following figure we used only four fields (product id, units in stock, required quantity and quantity difference), we no need to show shipped date information into our report because it is for our understanding to differentiate shipped and unpack orders. The red figures in the following report are critical, where quantity required is less then the stock quantity. So these items you need to order from your supplier to full fill your customers need.
