25.

Charting Data from a Database

Your first task is to build the core functionality of the application-the data sheet. This sheet will use a PivotTable to retrieve the data from an external database. You'll link a chart to the PivotTable and then format the chart and the PivotTable to make a dramatic presentation.

Retrieve External Data into a PivotTable

The easiest way to retrieve data from an external database into a PivotTable is to use an Office Data Connection (odc) file. A data connection file contains all the information necessary to connect to any OLE DB data provider. OLE DB is a data connection technology that allows you to connect to a wide variety of data sources. Once you have created a data connection file, you can retrieve data into a list or a PivotTable report.

1. Save a new blank workbook as Chapter12 in the folder containing the practice files for this book, replacing the copy of the finished workbook that you copied earlier. Then rename Sheet1 to Data.

2. From the Data menu, point at Import External Data and click Import Data. (Do not use the PivotTable And PivotChart Report command.) In the Select Data Source dialog box, select Connect To New Data Source and click the New Source button.

   

3. In the Data Connection Wizard dialog box, select Other/Advanced and click Next.

   

4. In the Data Link Properties dialog box, select Microsoft Jet 4.0 OLE DB Provider and click Next.

   Clicking Next switches to the Connection tab.

   

5. On the Connection tab, click the ellipsis button next to the box for entering a database name, navigate to the folder containing the practice files for this book, select  Orders.mdb, and click Open. Then click OK.

   

6. The next page of the wizard allows you to choose the table that contains the data. Leave the Orders table selected, and click Next.

   

7. Type The Garden Company in the File Name box as the name for the new data connection file, and click Finish.

   

8. Back in the Import Data dialog box, click Create A PivotTable Report.

   

When you use the Import Data command, you can still create a PivotTable from the data connection you specify. The data connection is a simple file that you can transfer to another computer as needed.

Define the PivotTable

When you choose to create a PivotTable report from within the Import Data command, you are put directly into Step 3 of 3 of the PivotTable And PivotChart Wizard. You can now define the appearance of the PivotTable.

1. In the wizard, click Finish.

   The shell of the PivotTable report appears, along with the PivotTable Field List. (If the PivotTable Field List does not appear, click the Show Field List button on the PivotTable toolbar. If the PivotTable toolbar is not visible, right-click any toolbar and click PivotTable.)

   

2. Drag the Category field to the Drop Row Fields Here area, drag State to the Drop Page Fields Here area, drag Date to the Drop Column Fields Here area, and drag Net to the Drop Data Items Here area. Then close the PivotTable Field List.

   

3. On the PivotTable toolbar, click the PivotTable menu, and then click Table Options. Turn off Grand Totals For Rows and AutoFormat Table, and then click OK. You'll apply custom formatting to the PivotTable, and you don't want Excel to automatically change the format each time you select a new state.

   

4. Click the PivotTable menu, point at Select, and click Entire Table. Then repeat, but click Data instead of Entire Table.

   This selects all the data cells of the report.

5. On the Formatting toolbar, click the Comma Style button, and then click the Decrease Decimal button twice.

   This formats the revenue values with commas, which makes the numbers easier to read.

   

   When you create the PivotTable, the data connection tells the PivotTable where the database is located. The data connection stores the entire location of the database. If you move the workbook or the database to a new location, the PivotTable won't be able to find the database. You can edit the connection string so that the report will find the database file in the current folder.

6. Point at the Data menu, click Import External Data, and then click Edit Query. Within the string in the Connection box, select and delete the path that precedes the  Orders.mdb file name. Leave the rest of the connection string intact, and then click OK.

   

   Tip 

   Even though you create a data connection file (The Garden Company) to create the report, the PivotTable internally stores the connection information and never uses the file again. If you don't need the data connection for a different purpose, you can remove it once the PivotTable is created.

To remove a data connection, simply act as if you were retrieving data for a new PivotTable report, but then right-click the data connection file and click Delete. If you need to modify the connection for an existing PivotTable report, select within the PivotTable, point at the Import External Data menu, and click Edit Query.

Enhance the Layout of the PivotTable Report

To improve the layout of the report, you want product categories to sort in descending order of revenue. You want to display quarters instead of months, and you want the same quarters always to appear, even if you select a state that did not sell any products during that quarter.

1. In the list next to the State button, select ID as the state and click OK.

   The report shrinks, showing only categories and months in which sales actually occurred in Idaho. Notice also that the list of categories is sorted alphabetically, with Fruits appearing higher than Herbs or Tools, even though the revenue is lower.

   

2. Double-click the Category tile (in the row area), again select the Show Items With No Data check box, and then click the Advanced button. Select Descending as the AutoSort option, and select Sum Of Net from the Using Field list box at the bottom of the AutoSort Options group. Then click OK twice.

   All the categories now appear, sorted in descending order.

   

3. In the State list, select CA.

   This switches the state to California. The Garden Company first started selling in California in August 2000. As a default, months before that month are hidden.

4. Double-click the Date tile (in the column area), select the Show Items With No Data check box, and then click OK.

   This displays all the months, regardless of whether sales were made in that month in that state.

   

5. Right-click the Date tile, and on the Group And Show Detail submenu, click Group. In the By list box in the Grouping dialog box, deselect Months, select Quarters and Years, and then click OK.

   This automatically groups the months into quarters and years, even though the quarter and year values are not in the original data source.

   

   When you group dates, you end up with two rows of labels. A chart based on a PivotTable works just fine with one or more rows of labels. Because you specified Show Items With No Data for the Date field, the table shows extra 'catch-all' dates at the beginning and end. You don't want those in the table. While you're at it, you can eliminate the year 2000 from the report, showing only the most recent two years.

6. Click the arrow next to the Years tile, deselect <1/1/2000, 2000, and >10/2/2002, and then click OK.

   The unwanted periods disappear.

   

The PivotTable report now shows the dates as quarters and years, sorts the product categories in descending order of sales, and retains the same row and column headings, regardless of which state is selected.

Format the PivotTable

The table looks good, except that you want a slightly more dramatic look for the EIS application. You also want the columns to keep the same width, instead of adjusting as the data changes. In addition, you will need a second copy of the PivotTable report that can serve as the data source for a PivotChart.

1. Insert a new column to the left of column A, and insert two new rows above row 1. Set the width of column A to 3.5, the width of column B (the category labels) to 18, the width of columns C through J (the data values) to 9, and the width of column K to 1.3.

   Tip 

   If you want a chart based on a PivotTable to have a different orientation than the PivotTable, you need to make a second copy of the PivotTable to use as the basis for the chart. Because both PivotTables share the same data cache, creating a copy of the PivotTable doesn't require significant additional system resources.

2. Before continuing to format the PivotTable, make a copy of the Data sheet. Press and hold the Ctrl key as you drag the Data sheet tab to the right. Rename the new sheet ChartData, and reselect the Data sheet.

3. You want the background to be dark gray (leaving the black outlines in the PivotTable distinguishable) and the font for most of the cells to be light gray. Press Ctrl+A to select the entire Data worksheet. On the Formatting toolbar, click the Fill Color button and select Gray-80% (the top color in the rightmost column). Click the Font Color button, and select Gray-25% (the fourth color down in the rightmost column). Then select cell A1 to deselect the worksheet cells.

   

4. Move the mouse pointer over the top of cell B7 until it turns into a black downward-pointing arrow, and then click.

   This selects the Category labels.

   Tip 

   If the mouse pointer never turns into a black arrow when you move it around over the labels of a PivotTable report, you do not have selections enabled for the report. To enable selections, click in the PivotTable, and on the PivotTable toolbar, click the PivotTable menu, the Select submenu, and the Enable Selection command.

5. Change the Category labels to bold and italic. Change the font color to White (the lower right color in the Font Color palette).

   

   You won't need the column headings from the PivotTable because the chart will have labels for the years and quarters. You can hide the top part of the PivotTable to get ready to add the chart, but first put a formula at the top of the sheet to show which state is currently displayed.

6. In cell J1, enter the formula =C3. Format the cell as right-aligned, bold, and italic, with 36 as the font size, and Turquoise (fourth row, fifth from the left) as the font color.

7. Hide rows 3 through 7. Then save the Chapter12 workbook.

   

The PivotTable is ready. Next you'll create a chart to display the PivotTable data.

Create a PivotChart

A chart can make the numbers in the table easier to interpret. To show both the total orders for a state and what portion of those orders came from each category, a stepped area chart is a good choice. A stepped area chart is a stacked column chart with no gaps between the columns.

When you associate a chart with a PivotTable, the chart becomes a PivotChart. The row headings in the PivotTable always correspond to the horizontal axis of the PivotChart. You, however, want the dates as row headings in the PivotTable and as horizontal labels in the PivotChart, so you need to base the chart on the copy of the PivotTable.

Chart Wizard

1. Activate the ChartData sheet, click in the PivotTable, and then click the Chart Wizard button to create a new PivotChart on a new sheet.

2. Drag the Category tile to the legend area, and drag the Year and Date tiles down to the horizontal axis.

   

   Rearranging the chart also rearranges the PivotTable it's based on. If you want to control the orientation of a PivotTable report separately from a chart based on it, you must create a separate copy of the report.

3. On the PivotTable toolbar, select the PivotChart menu and click Hide PivotChart Field Buttons.

   You won't be manipulating the chart anymore, so you won't need the field buttons on the chart.

4. Make a few more changes to the general chart layout. On the Chart menu, click Chart Options. On the Gridlines tab, select Category (X) Axis Major Gridlines. On the Legend tab, select Left as the placement option. Then click OK.

5. Double-click any of the columns, and select the Options tab. Change the Gap Width setting to 0, and click OK.

6. Before you move the chart to the sheet with the table, you need to ensure that the font won't change size. Double-click in the white space above the chart to format the Chart Area. On the Font tab, deselect the Auto Scale check box and then click OK.

   

7. Now you can move the chart to the Data sheet. On the Chart menu, click Location, select As Object In Data, and then click OK.

   The chart appears on the worksheet, overlapping the formatted tabular report.

   

   You want to position the chart above the grid, aligned so that the columns in the chart match up with the appropriate columns in the table. Changing the height of row 2 will give you enough room for the chart. If you change the chart so that it doesn't automatically resize with the underlying data cells, you can temporarily adjust row heights to precisely position the chart.

8. On the Format menu, click Selected Chart Area and select the Properties tab. Select the Don't Move Or Size With Cells option, and then click OK.

9. Change the height of row 1 to 20 and the height of row 2 to 183. Then press and hold the Alt key as you drag the upper left corner of the chart to the upper left corner of cell A2, and as you drag the lower right corner of the chart to the lower right corner of cell K2.

10. Change the height of row 1 to 40 and the height of row 2 to 150.

    The chart overlaps the state identifier and part of the table, but that ensures that the pieces will all flow together when you make the chart transparent.

    

    You can now turn off the row and column headings because you won't need them again.

11. If the chart is selected, press Esc to deselect it. On the Tools menu, select Options. Click the View tab, clear the Row And Column Headers check box, and then click OK.

    When you move the mouse over items in a chart, a chart tip displays information about that item. You want the chart tips to display the value for any item, but you don't want the name of the item to show. You set a global charting option to make that change.

12. On the Tools menu, select Options. Select the Chart tab, clear the Show Names check box, and then click OK.

Format a PivotChart

You need to make the chart transparent so that it will integrate smoothly with the table. To make the chart completely transparent, you need to change the chart area, the plot area, and the legend. Rather than select and change each object interactively, you can make the changes using Microsoft Visual Basic for Applications from the Immediate window. While you're at it, you can change the color of the fonts in the chart to a light shade of gray.

1. Activate the Visual Basic Editor, and in the Immediate window type the following statements:

   set x = Activesheet.ChartObjects(1).Chart x.ChartArea.Font.ColorIndex = 54 x.ChartArea.Fill.ForeColor.SchemeColor = xlNone x.ChartArea.Border.LineStyle = xlNone x.Legend.Fill.ForeColor.SchemeColor = xlNone x.Legend.Border.LineStyle = 0 x.PlotArea.Fill.ForeColor.SchemeColor=xlNone x.PlotArea.Border.Color = vbBlack 

   Tip 

   To make Visual Basic show you lists of methods and properties, create a macro containing the statement Dim x as Chart and step through the declaration before entering statements in the Immediate window.

   

   The name of the state should be just over the top of the plot area, and the lines around the horizontal labels should just barely touch the PivotTable lines. The grids don't match perfectly on the left, but because the alignment changes whenever the value scale changes, you'll create a macro later to fix the vertical alignment.

   You can also change the colors of the chart to be evenly spaced shades from black to turquoise. Excel uses the eight colors from 17 through 24 as fill colors in a chart. You have seven series in the chart. By changing the colors in the palette, you can change the colors on the chart. The color turquoise contains the maximum possible amount (255) of green and blue, but no red. By dividing 255 by 6, you can obtain seven evenly spaced shades of turquoise.

2. In the Immediate window, enter the following two lines:

   set x = ActiveWorkbook  For i=0 To 6:x.Colors(17+i)=RGB(0,i*(255/6),i*(255/6)):Next

   The colors change to shades of turquoise.

   

3. Switch back to Excel, and save the Chapter12 workbook.

The chart now blends in almost perfectly with the background. Next you can add some simple macros to control both the PivotTable and the PivotChart.