May 1, 2006 at 7:40 am
I am having problems getting most of the excel custom calculations
to work when connected to an analysis service cube. They
work okay when a pivot table is against straight excel data,
but not when connected to an analysis service cube. The results just
return '#NA'.
To get to the calcuations... right click the measure.
select field settings -- options -- and in the 'show data as:'
drop down, select one of the selections.
These options work okay.
'% of row'
'% of column'
These options do NOT work.
'Difference From'
'% Of'
'% Difference From'
'Running Total In'
The one I really need to work is 'difference from'.
Does anyone know what you have to do to get this to work?
I have NOT using SQL Server 2005.
I appreciate any hints. Thank you very much.
May 2, 2006 at 9:34 am
Hi Julie:
You won't like my answer, but Microsoft says it can't be done with native pivottables. Here's a link to their article (KB231052).
Here are a couple of suggestions:
1) Create a calculated member in the OLAP cube. If you don't know how to do this, then contact the IT person who created the cube to include the percent-type calculations you need. You should be able to then pull those fields into your pivot.
2) Download Microsoft's Excel add-in for Analysis Services (I assume your cube is in AS). The add-in is free.
3) Use Microsoft's Office Web Components (OWC). Also, free. If you're not familiar with OWC, it basically gives you the same functionality of Excel, but in a webpage. For example, if you save an excel file that has a pivot table as a webpage file (be sure to select interactive functionality), you can then manipulate the pivottable in Internet Explorer -- In fact, I believe the pivottable OWC gives you much greater flexibility compared to the regular pivottable. Various percent-type calculations can be done in OWC.
4) Purchase an Excel add-in from a third-party company such as XLcubed.com or IntelligentApps.com The beauty of these add-ins is that they are very powerful, easy to use, and allow you to stay in Excel. They're way better than MS Excel add-in for AS. Of course, they cost a couple hundred bucks per license. (We use XLCubed, but Intelligentapps is also quite extraordinary.)
Hope that helps. Good luck.
--Pete
May 2, 2006 at 12:08 pm
Thank you very much Pete. For our purposes the calculated measure or OWC also did not provide the functionality so I will follow your suggestion and look into the 3rd party add-in. Its helps to know that you are happy with using them. Thanks again, Julie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply