Excel 2007

Convert to Formulas

Excel is the first choice browser to create reports from an Analysis Service cube. The cube data is displayed as a pivot table format which is extremely convenient and flexible format to slice and dice information. Pivot tables, however can only display data that exist in the data source. There may be instances where you might need additional calculations to be displayed in your report that could be derived from existing cube measures. For e.g. having a different method of determining sales quota for a particular region, calculation of Averages and Variances, what-if analysis and so on. In these circumstances, the Convert to Formula feature of Excel comes really handy. Continue Reading

Offline OLAP – Working with Disconnected Analysis Service Cube

Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Services Cube in disconnected mode. The Offline OLAP feature basically snapshots a version of the cube into a local cube file which can be used by Pivot Tables. Continue Reading

How to Clone an Excel PivotTable?

Ever came across a situation where you have to compare PivotTable data from same data source but from different environments, for e.g. to compare data between Test and Production environment?  In this post I’ll explain a nifty trick that helps to clone an existing Excel PivotTable and change its data source without having to create the clone PivotTable from scratch. Continue Reading…

Comparison of KPI Types in SharePoint Server 2007

SharePoint Server 2007 offers five different methods to create Key Performance Indicators (KPIs) 

  1. Using data from published Microsoft Excel 2007 spreadsheet.
  2. Using KPIs defined in SQL Analysis Service Cube.
  3. Using dashboard created in PerformancePoint 2007.
  4. Using data from SharePoint list.
  5. Using manual data entry.

This post compares the relative merits of methods 1-3 and suggests scenarios where a particular method is suitable over others. Continue Reading …