Monday, October 24, 2005

More with the Spreadsheet Add-In -

If you have used Discoverer OLAP, you may already know that you can choose to export all combinations of, or selected combinations of page-items in a worksheet (see the last screenshot in this post below). This can be particularly useful if you want to flatten out your results for further analysis in Excel (and never mind what I said about Excel and therapy in my earlier post). Well, the same functionality is available in the Spreadsheet Add-In also. And you because you are working in Excel to begin with, the results are available that much faster.
Taking the example below, I have a page-item in my query, Year, that has five values. I can use the context menu and click the option 'Expand Pages to Worksheets'.

The Add-In creates a new workbook, and creates one sheet for each page-item value. To help users even more, it adds as the first sheet a table of contents, with links to all the sheets in the workbook.

This, the TOC, can be very useful if you have a lot of page-item combinations. See below: Year has five values, and the Region dimension at the given hierarchy has six members. Using simple arithmatic, 5x6 equals thirty (30).

And 30 can be a lot of sheets.

A TOC therefore is much more appreciated in this example. As you can see below, every combination of page-item values generated is linked, and the value combination listed for the user's benefit. Furthermore (and there really seems to be no end to the number of small but very useful things here), the members of the geography dimension are indented for readability.

The Discoverer OLAP screenshot with the option to export all or selected combinations of page-items.