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. Anonymous 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. Anonymous 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. Anonymous 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. Anonymous 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.