This project has moved and is read-only. For the latest updates, please go here.

OLAP PivotTable Extensions & Excel Services

Topics: Resolved
Dec 7, 2008 at 7:11 PM

Does anyone know if OlapPivotTableExtend add-in will work in Excel Services?  (e.g., allow for a dynamically calculated column in an Excel Services Report created in Excel 2007)?

Thanks in Advance!
Dec 8, 2008 at 1:09 PM
The answer is yes.

OLAP PivotTable Extensions need only be installed on computers which need to create new private PivotTable calculations. After those calculations have been defined, the PivotTable can be distributed to others without problem. If published to Excel Services, the private calculations you define will still be active in the PivotTable. And if you distribute the Excel 2007 workbook to other Excel 2007 users, they will be able to continue designing and manipulating that PivotTable without problem.

Please post back to confirm the above if you try this.

The Calculation Library does not need to be distributed unless other users wish to start brand new PivotTables and reuse certain calculations you have created using OLAP PivotTable Extensions.

Marked as answer by furmangg on 6/10/2014 at 3:54 PM
Jan 19, 2009 at 10:58 PM
Thanks for the reply and for building this tool.  The extension does appear to work in Excell Services Reports.  However, it appears that it can only dynamically calculate measures, not members.  So if you wanted to dynamically calculcate the difference (better / worse) between the measure of two members, it doesn't appear to support that....  Is that true?  Have you come across this challenge and if so, any sense of a work around?  

Thanks in advance for your help.
Jan 20, 2009 at 1:11 AM
Edited Jan 20, 2009 at 1:26 AM
What does your PivotTable look like? Did you put the dimension attribute with the calculated member on rows, columns, or filters? Did you turn on the "show calculated members" property on the PivotTable? If you added that calculated dimension member to the MDX script in the cube would it work in Excel Services?
Jan 20, 2009 at 9:36 AM
I tried it out for the first time. It appears there's a bug in Excel Services such that if there's a line break in the definition of a calculated measure or calculated dimension member, then you get an error that says:

Unable to Load Workbook  
The workbook that you selected cannot be opened.
The workbook may be in an unsupported file format, or it may be corrupt.
Would you like to try and open this file in Excel?  

To be more exact, if you try to add the following expression as calculated measure to a PivotTable using OLAP PivotTable Extensions, then you get that error:

 [Measures].[Internet Order Count] = 0
 ,[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count]

However, if you remove the line breaks, then you get no error:

IIf( [Measures].[Internet Order Count] = 0 ,null ,[Measures].[Internet Tax Amount] / [Measures].[Internet Order Count])

I'm going to look into reporting this to Microsoft. I'm also going to look into whether I can do something in OLAP PivotTable Extensions to work around this bug.

If you remove the line breaks, does it work for you?

Jan 20, 2009 at 10:37 AM
I just posted Release 0.6.1 which works around this issue. It works around the issue by replacing \r\n line breaks with \r line breaks in the calculated member formula it saves to the PivotTable.

Please install this release and let me know if it solves your problem after re-adding all your calculated members/measure to your PivotTable then republishing to Excel Services.
Marked as answer by furmangg on 6/10/2014 at 3:54 PM
Jan 20, 2009 at 10:32 PM
I'm told this bug won't occur in Excel Services if you have Sharepoint 2007 SP1 installed and Office 2007 SP1 installed. I haven't confirmed this myself, but I thought I'd pass it along.

Regardless, OLAP PivotTable Extensions 0.6.1 works around this issue by removing line breaks behind the scenes (even though you see the line breaks when editing the formula in OLAP PivotTable Extensions).
Marked as answer by furmangg on 6/10/2014 at 3:55 PM
Jan 21, 2009 at 11:37 PM
Thanks for the quick response.  I think we might be getting closer but we might be misunderstanding the MDX.  Weare trying to create a report that looks like this. Our measure in theentire report is Revenue (We have only one measure in the cube). There are twodimensions in this report Region (row labels) and Basis (column labels). Ourbasis dimension has only two members: Actual and Target. Is there any way tocreate a dynamic "member" for the basis dimension that will give methe difference between Actual and Target for the same measure (revenue)?

<o:p> </o:p>

                           Actual     Target         B/W<o:p></o:p>

MidAtlantic             $406      $201         $205<o:p></o:p>

NorthCentral           $118       $217        -$99<o:p></o:p>

Northeast               $224      $26          $198<o:p></o:p>

OtherRegion           $3         $55        -$52<o:p></o:p>

Southeast               $308      $70          $237<o:p></o:p>

Southwest              $197      $145         $52<o:p></o:p>

West                     $63        $315        -$251<o:p></o:p>

<o:p> Belowis the MDX query used to retrieve the data:</o:p>

<o:p> </o:p>

SELECTNON EMPTY Hierarchize({[Basis].[Basis].[Basis].Members}) DIMENSION PROPERTIESPARENT_UNIQUE_NAME ON COLUMNS , NON EMPTYHierarchize({DrilldownLevel({[Geography].[Region].[All]})}) DIMENSIONPROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT({[Basis].[Basis].&[Actual], [Basis].[Basis].&[Plan]}) ON COLUMNS FROM [CUBENAME]) WHERE ([Date].[Calendar].[Month].&[2007]&[4],[Accounts].[Accounts].&[990371],[Measures].[Value])

Thanks again for your help!


Jan 21, 2009 at 11:55 PM
You should be able to add a member called:


(assuming that's the name of your All member)

with the formula:

[Basis].[Basis].[Actual] - [Basis].[Basis].[Target]
Marked as answer by furmangg on 6/10/2014 at 3:55 PM
Jan 22, 2009 at 12:25 AM
Success!!  Thank you.  This was EXTREMELY Helpful.  

Now if we could only figure out how to parameterize the calculation so it dynamically calculcated column b- column c based upon what is selected in the column filter.

I don't think that can be done, but we are in a much better place with this working.

Thanks again