locked
Formulas in OLAP Pivot Tables in Excel RRS feed

  • Question

  • Goodmorning,

    I am using Excel 2007 and 2010, but in both I have not found a way when I am in Excel and I get my Pivot Table data from Analysis Services, to add a calculated field in this pivot table. My button "Formula" in Tab Options is greyed out. For example, I can add Capacity and Work to my columns, but I would also like to show "Availability" (Capacity - Work). But I can not enter this little formula because of the grey button. Anyone any ideas?

    Thanks for answering!

    Willem ter Haar

    PS: Using MDX-expressions, due to build-time OLAP database, is a less preferred solution

    Monday, July 5, 2010 8:17 AM

Answers

  • Hey there people,

    First of all, thanks Sriram for answering, I doublechecked but I was also set on Automatic, so that was pretty much covered.

    But at the end of the line, I searched the net and came up with a Codeplex solution: http://olappivottableextend.codeplex.com

    With this tool it is very easy to add calculated fields. Problem solved (for me).

    Thanks for your cooperation.

    Willem

    • Marked as answer by WillemTH Tuesday, July 6, 2010 11:58 AM
    Tuesday, July 6, 2010 11:58 AM

All replies

  • Hi Willem,

    I believe those calculated field options are disabled if the data source is an OLAP cube a quick search seems to confirm that. I guess you have two options:

    1. The best option is to add it as a custom MDX expression via Server Settings > OLAP Database Management > Configuration

    2. A less pleasant option is to manually add another column to your spreadsheet that works out the Availability for you, based on the contents of the columns in the pivot. 

    Hope this helps,

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Monday, July 5, 2010 9:21 AM
  • Alexander,

    Thanks for taking the time to answer me, I am glad you answered. You know, I thought about using the MDX expressions, that is why I wrote the PS. I am perhaps naive to think that when Data Analysis went with PS 2007, we would have got a more sophisticated solution with Excel (a program which is ideal for formula's and calculating), but the fact that formula's are not functioning is a big disappointment. MDX expressions are tougher to teach to administrators at customers and in practicality don't come near the calculated fields of Data Analysis. Furthermore. MDX expressions slow down the cube, which is funny since Microsoft put so much effort in speeding up the cube (ability to remove baseline data, inactive tasks and even data from whole departments) Why would I ruin that speed with a dozen MDX expressions? Your second option also crossed my mind, but I can make a million extra columns but they will not enter my graph. Furthermore I can not sell that option to customers, it looks kind of silly.

    I am curious if somebody else has some other ideas, but this is a big disadvantage for those customers who want to get the most out of reporting in Excel. Again, Alexander, thanks for your input. If I may find a third solution I'll post it here.

    Greetings,

    Willem ter Haar

    Monday, July 5, 2010 6:39 PM
  • I think i am in different page, am able to see the Formula tab, can you provide the detailed repro steps?

    What is the Workbook calculation mode? I set 'Automatic' or 'Automatic except Data tables'.


    Cheers. Happy troubleshooting !!! Sriram E - MSFT Enterprise Project Management
    Monday, July 5, 2010 11:33 PM
  • Hey there people,

    First of all, thanks Sriram for answering, I doublechecked but I was also set on Automatic, so that was pretty much covered.

    But at the end of the line, I searched the net and came up with a Codeplex solution: http://olappivottableextend.codeplex.com

    With this tool it is very easy to add calculated fields. Problem solved (for me).

    Thanks for your cooperation.

    Willem

    • Marked as answer by WillemTH Tuesday, July 6, 2010 11:58 AM
    Tuesday, July 6, 2010 11:58 AM
  • Hello WillemTH,

    I use a calculated field called FTE (work/capacity) in my cube builds. I first set up a Data Analysis view in PWA that contains this calculated field. Then export the table to Excel. The calculated field come over just fine, and you can configure it to refresh the table each time the spreadsheet is open. Much easier then a a MDX expression for an admin.

    Wednesday, July 7, 2010 3:57 PM
  • Excuse me eMicah,

    Data Analysis in Project Server 2010? Have I missed something? I have not discovered it so far.

    Willem ter Haar

    Wednesday, July 7, 2010 8:13 PM
  • We're are still on 2007 and there are options in PWA to view the OLAP cube in what they call Data Analysis. After re-reading your intial post, I see where I should have picked up that you are using 10. Sorry for the confusion. Still I think the solution could be acheived in 10 using different verbage. I would be very upset if this functionaly was removed in 10.
    Thursday, July 8, 2010 3:58 PM
  • Data Analysis views are the name of the PWA views that access the Project Server cube through ODBC. In Project Server 2010, ODBC is gone and the Data Analysis views have been replaced by Excel Services workbooks. Overall you have about the same features but in a different format.
    J.F. LeSaux, PMP, Solution Architect, Microsoft Services
    Thursday, August 4, 2011 9:11 PM