Pivots and Charts
Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
Charts
Data is easier to understand when presented visually, like this:
This chart shows the top 20 products (by quantity) sold by state. This is the kind of chart most sales organizations require. “But wait! There’s more!”
Drill Down
Once a sales organization sees their data, often they want to “drill down” to understand it better. XL’s Pivot Tables support drill down and they make great source data from which to create charts like the one above. Here is the supporting Pivot Table.
From this Pivot Table, users can “double click” on any value to “drill down” and see the detail entries. Below are the results of double clicking the “55” at the intersection of “OR” and “Northwind Traders Chocolate Biscuits Mix”
So rather than build the chart first, I always build a pivot table then create a pivot chart from it. Now before attempting to build this chart, we need to add a little more data to our Query Table. Below is the expanded SQL statement. Add it to your macro.
.CommandText = Array( _
"SELECT O.`Order ID`, O.`Customer ID`, O.`Order Date`, C.`First Name`, " & _
"O.`Ship State/Province`, D.Quantity, P.`Product Name` " & vbCr, _
"FROM Customers C, Orders O, `Order Details` D, Products P " & vbCr, _
"WHERE O.`Customer ID` = C.ID " & vbCr & _
" AND O.`Order ID` = D.`Order ID` " & vbCr & _
" AND D.`Product ID` = P.ID " & vbCr & _
" AND C.`State/Province` LIKE '" & s & "'")
Now, to add the same Pivot Table and Chart to your macro you could:
- Click the “easy” button to bring in the expanded data set
- Turn on the Macro recorder
- Insert > Pivot Table > Pivot Chart
- Put Quantity in the Data area; State in the columns; and Product Description in the rows
- Click the down triangle on the Product Description header: select More Sort Options > Descending (Z to A) by: > Sum of Quantity
- Click the down triangle again: select Value Filters > Top 10: and change 10 to 20
- Right click the chart: Move Chart > New Sheet
- Go to the chart tab and change the chart type to columns stacked
- Turn off the Macro recorder
- Go into the VBA editor
- Cut and paste the newly recorded code just before the last END IF of Macro1.
-OR-
You could use routines from my next posts to do the job and take care of some housekeeping problems you’ll discover when you try to rerun the macro you recorded.
Comment
RSS Feed
Email a friend
