Dela via

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

  • Artikel

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

  • Anonymous
    August 04, 2004
    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?
  • Anonymous
    April 17, 2006
    If it requires OLAP to be run, cann't we achieve this by Pivot table, because I don't want to use OLAP.
  • Anonymous
    May 21, 2006
    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.  

  • Anonymous
    August 28, 2006
    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.