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

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.

Comments (4)

  1. Luke says:

    I’m having trouble finding information on how to use the new add-in. I can’t seem to add a page filter. Can you point me in the right direction?

  2. Harsh Trivedi says:

    If it requires OLAP to be run, cann’t we achieve this by Pivot table, because I don’t want to use OLAP.

  3. GRC says:

    Has anyone noticed a different in performance in write-back between a structured report vs. a free-from report?  My free-form report writeback seems much slower and doesn’t totally refresh the data after loading.  

  4. Yong Hwee says:

    Information on using the writeback function (what-if analysis) has been limited. Not much details on that even on the whitepaper. Anyone out there has experience on using it with SSAS 2005 cube? Any sample? Thanks in advance.

Skip to main content