I presented at the London meeting of the SQL Server User Group in March, here’s a walkthrough of the first part of what we covered. The data I used in the presentation and this blog is the AdventureWorks sample data for SQL Server 2008 R2 available on Codeplex.
Pivot tables are Excel’s way of browsing and querying Analysis Services cubes, and a pretty fine job they make of it mostly. Things were seriously improved in Excel 2007 but there were still some rather confidence-shaking omissions. Excel 2010 makes the experience a bit more logical and gives a better result.
For a start, the conditional formatting has been improved considerably. Conditional formatting is something that helps give worksheets a “dashboard” feeling. To apply in a Pivot Table, you just need to select any one cell in the data area then click the Conditional Formatting button in the Home ribbon. Select the format type you want to apply which will fill that cell and give you an auto-format button. 
Selecting this gives you the choice of how far you want to expand the formatting. The key option is to apply it at the hierarchy levels you’re currently viewing.
Now when you expand the hierarchy, the formatting hasn’t been applied to the child level. In most situations this is likely to be correct, as the values for child members shouldn’t really be compared to members of their parent level.
They can be compared to one another, though. So you can add more conditional formatting at this level. It’ll be best if the formatting applied uses at least different colours to the parent in order to make it quite clear that there is a difference between the levels.
The neat thing about adding conditional formatting at a level is that it is then context sensitive to the displayed members. So if you expand a different parent node, you’ll see all the formatting adjust to match.
The actual behaviour of some of the conditional formats has been improved too. If you apply formatting to a measure which has both positive and negative values, the negatives will be displayed as a negative.
Slicers
A navigation tool which further enhances the experience of using Excel as a dashboard tool, Slicers are a selection tool. They come into their own with dashboards which are to be published to other users, as they provide a quick and easy way of selecting data.
Inserting a slicer launches a dialog box from which you can choose dimension levels to slice on. If you select a hierarchy rather than a level, the dialog automatically selects all the child levels. This will then cause Excel to add a slicer for each of the levels and they will be linked to one another.
Grouping slicers in this way means they cascade the selections the user makes. So if in the AdventureWorks samples you create a slicer on product categories, choosing “Bikes” will limit the available selections in the subcategory slicer.
Of course, you can still add further slicers which are not part of a group but operate independently. You can also format slicers to match the look and feel of the rest of the sheet.
One final feature of slicers – each can be linked to multiple pivot tables or charts, providing they are based on the same data set. The slicer ribbon contains a button which allows configuration of these connections.
I’ll post some details on sparklines tomorrow.
