Wednesday, October 7, 2009

Microsoft Excel – Asymmetrical Reports

No more counting rows of data in PerformancePoint and matching them up with the previously selected data to create asymmetrical reports.  Excel 2010 has this functionality built-in… provided you have a cube or data source behind the scenes created.

I wonder how dynamic it really is though… seems like next year (or next month) I’m going to be changing the columns in my report manually.  There had better be some dynamic time intelligence in there – without resorting to custom MDX.

Creating an Asymmetric Report with the New Set UI

A very common report layout needed by our customers is to show actual sales for the year 2008 while showing forecasted sales for the year 2009. Here is a PivotTable showing actual, budgeted and forecasted sales quotas for the last two fiscal years (click to see larger image).


As you can observe there is a lot of irrelevant information in our report such as Forecast for 2008 (since we have actual data for 2008) and Actual for 2009 (since this only includes 9 months of data). If I tried to hide Actual it would actually get hidden for both 2008 and 2009, but since I have Excel 2010 I can go ahead and create a set out my information on columns by simply going to PivotTable Tools -> Options in the ribbon, clip_image001 and selecting “Create set based on column items”.

Now the Named Set creation UI will pop up (click to see larger image):


The UI contains all the tuples that currently define the column labels of my Pivot report. Now I can easily remove the tuples I no longer want. In this case I am going to go ahead and remove:

Microsoft Excel


Alexis said...
This comment has been removed by the author.
Alexis said...

Once my girlfriend asked me about damaged xls files,but I couldn't help for her.Yesterday I accidentally found on a soft forum next program-xlsx view.To my surprise tool solved her problem in seconds and for free.My girl kissed me))