Part I: Pivot and Chart in the Query Builder
That's right, kids! Now you can view your query results in a pivot or chart without leaving the context of your Query Builder. (Also, though I'm using the Query Builder in this example, the same features exist for the SQL Editor. "Code Jockey's, rejoice!!") What's nice about this is that you can quickly slice & dice your data to quickly identify information. Additionally, since the pivot and chart are tied to the query, you can make changes in your query, re-run it and then switch back to the pivot & chart tab to see the refreshed data!
Let's run through a quick example. Suppose I want to create a query of my customer data to see how many customers are in certain zip codes in the US. First, I'll build my query in the Query Builder and specify a WHERE condition to filter my result set for only those customers that reside in the US. (Of couse, if you already have an existing query, you can skip this part.)
Figure 1: building the query
Notice that I added three tables to the Query Builder and since they were related in my database, my JOINs were automatically drawn for me. All that was left for me to do was to select the fields I want to use in my query and specify any WHERE conditions (like only records fom the US, in this example). Also, since I only want to filter by COUNTRY but not actualy show that data in my result set, I can un-check the "Visible" checkbox at the bottom of the COUNTRY column. Now that I've built the query, I can run it and see the raw records it produces:
Figure 2: query results
Now here's where the fun begins. Take notice of the new "Pivot & Chart" tab a the top of the window. When you click on it you'll be presented with a blank pivot and chart with a little "Pivot Grid Field List" window to help you create your pivot.
Figure 3: the blank pivot window
At this point, you're probably fine on your own, but we'll step through this regardless. To build out your pivot and chart, begin dragging the fields from the "Pivot Grid Field List" onto the various data regions of the pivot table. In this example, I want to use the STATE field as a filter, the POSTAL_CODE field for my rows and the CUSTOMER_ID field as my data which will be sumed by default. (As with all Toad products, don't forget to use the right-click button on your mouse to find additional options!) After arranging the fields, I can click on the filter icon on the STATE field to select one or more states to filter on. I've chosen California for this example.
Figure 4: my filtered, pivoted data
Not bad... this is a great way to quickly delve into my database, grab a set of records and then fine-tune my results through the use of pivots. Now that I've got this data it occurs to me: I forgot that I need data about male customers only! Not to worry... let's just switch back to the "Diagram" tab (at the bottom of the window) to see the query we were working on in the Query Builder. In order to get the data I'm looking for, I need to add the SEX field to the query and specify a WHERE condition for 'M' (more info on using the Query Builder can be found in earlier blog posts). I'll un-check the "Visible" checkbox for this field as well, since I don't want it in my query result set either. Once I've got that in place I can re-run the query and switch back to the Pivot & Chart tab to see my pivot and chart automaticaly updated with fresh data!
Figure 5: refeshed pivot data
There you have it! With just a few mouse-clicks I was able to quickly build a query, refine it with the pivot table, alter the query to get more specific data and see my pivot and chart updated automatically. It's a huge time-saver and a nice new feature to Toad for Data Analysts v2.5. Look for the release sometime around September or join the beta program to get in on the action ahead of schedule. You can find a link to the beta release here on ToadWorld.com under the "Products" menu.
-- Daniel