Building Advanced Reports with the Excel Add-in for SQL Server Analysis Services

  • Article

The Excel add-in is one of two just released Business Intelligence solutions available for download:

These are free of charge to licensed customers and include full documentation and PSS support.

The aim for the Excel Add-in for Analysis Services is to provide a fuller reporting solution for OLAP cubes than is natively enabled in Excel PivotTables. You can use it to build a report structure with different grouping and segmentation options, and you can then fill that structure with data from an OLAP cube.

Some nice features:

  • Drillthrough: the ability to see the constituent rows that make up an aggregation;
  • Suppress empty rows and columns (something not possible to do in an Excel PivotTable);
  • Elimination of selected dimension members from the report output;
  • Visual totals to only show aggregations of non-eliminated members;
  • Showing the unique name rather than the friendly name for all dimension members;
  • Showing server-defined formatting options (colour, font style, formatted value);
  • Ability to display MDX for the report as currently generated;
  • A free-from mode that allows a report to be generated using formulae rather than as a single monolithic block (like PivotTables are). This provides for a high level of customisation;
  • The ability to insert custom columns and rows in the middle of a report;
  • Leaf-level writeback for "what if" analysis.

If you're looking for a spreadsheet-based client into Analysis Services and you're finding PivotTables slightly too structured, this add-in is well worth evaluating.