Calculated Sets?

Topics: Resolved
Aug 11, 2010 at 12:42 PM

I've got some reports that would be much easier to develop if in addition to creating calculated members I could also create calculated sets.

I did some poking around in the source code/Excel API and it appears that Excel supports this via the other value of the XlCalculatedMemberType.  I'm thinking of something along the line of adding a member/set option on the calculations tab, and another property to the Calculation class in the CalculationsLibrary.cs class to hold the selected value.  There would need to be a couple of changes made to the MDX generation logic and the bntAddCalc_Click event handler in MainForm.cs too, but that looks like all that would be required.

Before spending time coding this up I wanted to check to see if there wasn't some fundamental reason that this wasn't already implemented (like it won't work for some reason)?  If no one knows of a problem with this approach I'd be glad to submit the patch once the changes are made.



Aug 11, 2010 at 1:04 PM

The display of named sets in PivotTables in Excel 2007 leaves a lot to be desired.

This feature is built into Excel 2010 natively. And the creation and maintenance of named sets is so far superior to any UI I would have put in OLAP PivotTable Extensions. You really should try it before you spend any time on OLAP PivotTable Extension enhancements.

Aug 11, 2010 at 2:47 PM


I'm less interested in displaying the sets than I am in using them in other calculated members.  The call center here is pretty wicked inconsistent about which set of queues they are interested in looking at data for from one report to the next (so putting the sets in the cube isn't a really great option).  I could just copy and paste the set into all the members , but I'd rather define it once, then use it in multiple calculated members.

For the forseeable future most people here are going to be on Excel 2007, so unless a set created using the UI in 2010 is backwards compatible with Excel 2007 I'd still like this feature.  I've just gotten 2010, so I'm not as familiar with it's capabilities, I'll have a look and see what it looks like.


Aug 11, 2010 at 3:20 PM
Edited Jun 10, 2014 at 11:09 AM
Using named sets in calculated measures will knock those calculations out of block computation mode. So you might performance test it with a named set and with copying/pasting the MDX for the named set into the calculated measure.

Edit: AS2012 improves performance of calculations using named sets.
Marked as answer by furmangg on 6/10/2014 at 4:09 AM
Jan 14, 2011 at 8:02 AM


I think Pivottableextensions are getting confused with

calculated sets created in new Excel 2010 UI.

Sets are listed in Calcs Dropdown. It's then possible to add the set to

pivot table wich leads in Pivotableextensions deleting the set and adding

the mdx Expression as calculated member. I think, if you decide not to support

sets in PT-Extensions it would make sense to check this on filling the combo e.g:

            For Each calc As Excel.CalculatedMember In pvt.CalculatedMembers
                If calc.Type = Excel.XlCalculatedMemberType.xlCalculatedMember Then
                End If






Jan 15, 2011 at 7:28 PM

peve, I agree OLAP PivotTable Extensions doesn't handle sets you've built with Excel 2010 very well. I'll definitely fix that in the next release so at least it doesn't cause problems.

Apr 4, 2011 at 6:45 PM

I have fixed OLAP PivotTable Extensions so it reacts better to named sets created in Excel 2010 UI (or with a macro). OLAP PivotTable Extensions itself doesn't let you create named sets, but forthcoming release 0.7.1 does at least prevent them from showing in the calculations dropdown and does at least show them in the PivotTable MDX query correctly as WITH SET.

Marked as answer by furmangg on 6/10/2014 at 4:09 AM
Apr 4, 2011 at 6:49 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.