Just as it is easy to display multiple measures on either the rows or columns, it is easy to allow for multiple dimensions to be displayed on the rows or columns. In a sense, Figure 6-20 showed this by having both the dates and measures on the columns, but imagine now that the grid should show the gross profit by products by sales territory in the rows and by time in the columns. Placing Gross Profit in the background, Date.Date.Calendar on the Columns, and both Product Model Lines and SalesTerritory on the Rows leads to such a report. When multiple dimensions are placed on either the rows or the columns, the order in which they appear in the Rows and Columns panels will determine the order in which they appear in the grid. In the example of both products and sales territories being placed on the rows, if products are first then products will show first, with sales territories indented slightly on the next row. The user can still expand or drill down on members of either products or sales territories to perform analysis. Figure 6-21 shows an example of a user having expanded both the All Products and the All Sales Territories members one time (as well as expanding All Periods.) Note that the first set of rows has the total for all products and all territories, followed by the details of the territories; the next section moves to the first product model, the repeats the territories, and so forth.
PivotChart and PivotTable Reports
The PivotChart and PivotTable are more powerful than the Analytic Chart and Analytic Grid, but the PivotChart and PivotTable reports use the Office Web Components (OWC) and this requires both an install on the client machine if they don’t already exist, and it requires that each user viewing the reports have a license for the controls.
Starting either a PivotChart or PivotTable starts a wizard that asks the developer the same three questions as any other object (name, folder, and permissions). After filling in this information the design surface is rendered. PivotCharts have two buttons on the toolbar that load additional tools: Commands and Options and the Chart Wizard. Commands and Options allows for changes to the borders, toolbars, and so forth. The Chart Wizard, on the other hand, launches a dialog box that walks the developer through the process of creating a report. Unlike the Analytic Charts and
C h a p t e r 6 : A n a l y s i s w i t h P e r f o r m a n c e P o i n t S e r v e r a n d P r o C l a r i t y
1 6 3
Analytic Grids, the PivotCharts and PivotTables can report against many different data sources, including relational tables, cubes, XML, and more. PivotCharts can even be created from data entered manually at the time the chart is created.
Unlike PivotCharts, PivotTables only have one active button, Commands and Options, when first created. The Commands and Options button here is basically a conglomeration of the Commands and Options and Chart Wizard options from PivotCharts. This one button opens a dialog box that lets users connect to a data source as well as change some properties of the grid.
Once a data source is chosen, the designer can choose to either select a single table or cube, or to write a query that returns the desired records. After creating a connection for a PivotChart, a Chart Field List appears that shows all of the measures and all of the attribute hierarchies. In keeping with the previous examples, a connection to the Adventure Works cube in Analysis Services would provide a list like that shown in Figure 6-22.
Figure 6-21 Multiple dimensions are placed on both the rows and columns while a single measure is in the background.
1 6 4
B u s i n e s s I n t e l l i g e n c e w i t h M i c r o s o f t O f f i c e P e r f o r m a n c e P o i n t S e r v e r 2 0 0 7Note also in Figure 6-22 that there are many different types of charts available, including bar, line, pie, area, and other chart types. Each chart type might have a variety of subtypes available as well. This makes these charts far more flexible than the Analytic Charts.
There are three areas on the chart onto which items can be dragged and dropped: the chart itself, which is the only area on which measures can be placed; an area for Series Fields (the Y-axis) 5; and an area for Category Fields (the X-axis). The hierarchies placed on the series or category areas can be expanded so that multiple selections can be made. The chart itself is interactive and double-clicking expands the data (it does not drill down) although buttons at the top of the chart allow for collapsing, expanding, drilling into, and drilling out. There are also buttons for filtering, sorting, and pivoting, and there is an area at the top of the chart for filters
Figure 6-22 A PivotChart showing the rich chart types and a list of the fields available in the Adventure Works cube. The downside of a PivotChart or PivotTable is the need for client-side software installation.
C h a p t e r 6 : A n a l y s i s w i t h P e r f o r m a n c e P o i n t S e r v e r a n d P r o C l a r i t y
1 6 5
(similar to filters on a dashboard as shown in Chapter 5.) Figure 6-23 shows a standard bar chart with Gross Profit for the four years for all products.
PivotTables are very similar. There is an area at the top that holds filters, and areas for data on the rows and columns. Multiple measures can be placed in the grid itself. Buttons allow for sorting and filtering, and there are a host of options on the Command and Options dialog box to show and hide certain items, as well as control formatting. Note that subtotals can easily be placed on the grid and developers can choose to hide or show rows and columns where all the values are empty. At both design time and runtime, columns can easily be resized to show all the data or the full text in the header. Figure 6-24 shows an example of a PivotTable with both Gross Profit and Gross Profit Margin, broken down by year and product models. Note that the formatting of the data from the cube is not honored; in other words, the Gross Profit does not show up as dollars nor does the Gross Profit Margin show up as a percentage.