Post reply

Help with Excel ''Difference From'' with Analysis Service.

  • May 1, 2006 at 7:40 am

    #114117

    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

    #635353

    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

    #635422

    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