Beyond Excel: VBA and Database Manipulation

Oct 17 2009   4:15PM GMT

Pivots and Charts



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
vba
So far, we have simplified listing data in XL.  For some XL users, that might be enough.  But for most users, this is only slightly better than printing data on green bar paper.  To really wow them, we need to leverage two of XL’s built in functions, Pivot Tables and Charts. 

Charts
Data is easier to understand when presented visually, like this: 

Chart

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.

PivotTable

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” 

Drill Down

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 on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: