"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...