RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Build a Generic Histogram Generator for SQL Server : Page 7

Histograms help people analyze large amounts of data, whether you display them as tables or as charts. This article shows you how to do both.


Expanding the Box: From T-SQL to Excel Charts
Working in a querying tool such as SQL Server Management Express is often adequate, particularly for data-savvy individuals such as developers. But in many cases, it's better to show graphical charts. This section describes a procedure for converting histograms to Excel charts. If you glance at the figures in this section, you will see that I cannot claim that you can "convert to a chart in just 3 easy steps!" or even 5 or 10 or… well, there are a lot of little dialogs you need to go through, but after you have done it a couple times it is not really that complicated.

Figure 6. Defining a Data Connection: Follow the sequence of screens to define a data connection to Excel.

Step 1: Create a Connection to Your Data Source
Before you can connect to a database in Excel, you need to define the connection in Windows to point to your database. In Windows XP, open the ODBC Data Source administrator (on the Administrative menu which you may need to enable). Figure 6 walks through configuring and testing an existing connection with it. If you need to create a new one, select Add instead of Configure.

Step 2: Connect a Database to Excel
With your database connection in place, create a new worksheet in Excel 2003. On a blank sheet, instantiate a new database query (see Figure 7). The wizard asks you first to select your connection, then to define the query, and finally where to place the results of that query. In the middle step you actually leave Excel and go into Microsoft's eponymous query program, and finally return to Excel. This venture into Microsoft Query is detailed in Figure 8.

Figure 7. Defining the Data Route: (1) Select the menu choice to instantiate a new database query. (2) Select the appropriate data source and press OK. (3) You'll see a Query Wizard that you can use directly for simple queries. (4) Cancel out of the wizard because it doesn't handle stored procedure calls. (5) You'll be prompted to go into Microsoft Query. (6) Define your query. (7) Specify where you want the result set to be displayed.
Figure 8. Generating a Histogram in Microsoft Query: When Excel launches Query, the Add Tables dialog is open. (1) Click Close—you do not need to pick a table. (2) Press the SQL button to open (3) the raw SQL text edit window. (4) Type in the histogram procedure call and press OK. (5) The tool is not sure what to make of your code so it prompts you. Click OK. Query displays the result of the query on its main canvas if you have typed it correctly. Press the return-my-result-set-to-Excel button (6) to complete the query.

Step 3A: Generate a Chart for a Summary Histogram

Figure 9. Dynamic Table and Chart: When your query is connected to Excel, you have a dynamic connection. When the data table is active, you have access to the refresh button on the External Data toolbar. Pressing refresh updates both the table and the chart.

After completing step 2 you'll have a data table in Excel (see Figure 9). To convert this into a chart you invoke Excel's Chart Wizard from the Insert menu. Using the wizard is very simple so I will not detail it here. Customizing your chart to be just the way you want it, however, may take some effort. When your chart is in place you'll have both a dynamic table and a dynamic chart. Notice in Figure 9 that one cell in the table is the active cell. Because you are in the data table, the External Data toolbar appears. You need simply press the refresh button in the middle of that toolbar (the button with the exclamation point icon) to refresh both the table and its linked chart.

Step 3B: Generate a Chart for a Detail Histogram
The query in Figure 9 returns a summary histogram. For this next example, assume you have a detail histogram that you want to link to an Excel table and chart. Figure 10 shows just the key pieces of this process; refer to Figures 7 and 8 for steps not shown here. After you return your result to Excel you'll have a standard two-dimensional Excel table. There is one Excel secret you need to know at this point: delete the column header on the first column (see frame 4 in Figure 10) before you invoke the chart wizard. If you do this, the chart wizard will naturally yield a chart where each row is rendered as a very thin histogram. If you don't, the chart wizard will mislead, befuddle, or frustrate you!

Figure 10. Charting a Detail Histogram: (1) Define your query. (2) From Microsoft Query return your results to Excel. (3) Delete the column header on the first column to allow the chart wizard to properly guide you. (4) Invoke the chart wizard. (5) Use the default column chart type. You could select Finish in step (5); (6) just lets you confirm that the series are in columns. (7) Your linked chart is rendered.

Step 4: Modify a Query
You've seen how to link dynamic data to your Excel chart and how to refresh the chart on demand. What if you actually need to change the query—does that mean you need to throw it out and start over? Not necessarily. As shown in Figure 11, you can start with an existing dynamic chart, and then return to Microsoft Query to edit the query, which results in a different number of rows. When you return to Excel everything gets updated automatically.

Figure 11. Modifying the Query: Changes that affect the number of records returned are automatically propagated to Excel. (1) Select the first button on the External Data toolbar to edit the configuration. (2) Press OK. (3) From the initial result set (4) edit the query and (5) change the number of rows in the result. (6) Return the data to Excel and observe the table and chart have rendered the changes.

Note that this holds true when you change the number of rows because the chart series are in columns. If instead of changing the number of rows, you change the number of columns by adjusting the upper or lower boundary date, you will get an undefined reference or a missing data error in the chart upon returning to Excel.

Other Outlets
This article uses Excel as a channel for converting data tables to charts because it's a convenient, standard application where you can produce the charts through a smart UI rather than by programming, but there are, of course, many other possibilities. For example, you might want to feed the output of the stored procedure to your web or Windows application to create a dynamic chart. Whatever you choose, the intent of this article was to sufficiently explain the Histogram API to allow you to leverage it in the application or vehicle of your choice.

Michael Sorens is a freelance software engineer, spreading the seeds of good design wherever possible, including through his open-source web site, teaching (University of Phoenix plus community colleges), and writing (contributed to two books plus various articles). With BS and MS degrees in computer science and engineering from Case Western Reserve University, he has worked at Fortune 500 firms and at startups, using C#, SQL, XML, XSL, Java, Perl, C, Lisp, PostScript, and others. His favorite project: designing and implementing the world's smallest word processor, where the medium was silicon, the printer "head" was a laser, and the Declaration of Independence could literally fit on the head of a pin. You can discuss this or any other article by Michael Sorens here.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date