Hi

Using PS2010, SQL 2008R2, Excel 2010, SharePoint 2010

I'm keen to use OLAP CUBEVALUE formulas in Excel for some non standard report requirements.

I would like to add Powerpivot to the Server for this purpose with a SharePoint level install but I understand that the PS OLAP database is not compatible with Powerpivot for this kind of functionality and a new cube or database may be necessary.

I'm assuming that the desktop install will also not work for these formulas if connected to the PS Olap Database.

I wonder if anyone has instructions for making the necessary adjustments so that CUBEVALUE formulas work in these situations?

Thanks

Brian

Hello,

You cant use the SQL AS instance installed as part of PowerPivot for SharePoint to host your Project Server OLAP - as far as I am aware - but why would you want to? The Project Server OLAP cube will need a different SQL AS instance or a different SQL Server AS.

What issues do you have with the CUBEVALUE function in PowerPivot for Excel? If you are having issues with it what about writing the MDX query and getting the Project Server OLAP cube to create that calculated measure for you when the cube builds?

Paul

Hi Paul

Thanks for your comment. Appreciated.

I haven't yet experimented with the CUBEVALUE formula just considering:-

Some of the reports that are being requested include what are fairly minor but difficult amendments to make to standard olap pivottable outputs e.g. show year so far actuals, full year forecast  accompanied by last months totals for just certain elements of the Pivottable. Also some rows to be hidden. Snapshot adjustment is not really an option for this.

Some reports are mostly lifting certain specific columns from OLAP pivottables (timephased) and mixing that with other info from elsewhere (mostly non timephased). Also some totals from different cubes to be placed in the same plain excel table to allow a few calculations.

I considered MBX but have only used MBX in the past when a specific measure is to be added to every time dimension - however I realise there is more to MBX than I know.

Do you think me considering CUBEVALUE to move away from the fixed pivottable format is a sensible approach in these kind of circumstances?

I realise I could make some progress interrogating the reporting database directly rather than the OLAP Cubes but my experience is that these kind of reports (whether using excel services or SSRS) normally run very slow.

Cheers

Brian

Hi

No need for reply on this. I have made very good progress. Loving it!

Brian