Category Archives: SSAS

MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=CurrencySelect [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

SSAS Workshop in Phoenix, AZ 6/21 – 6/22


Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

MDX Puzzles: Super 8–Solution

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS

Then finally we’ll reference these to calculated measures in our final select statement.

MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

MDX Puzzles: Super 8

If you’ve followed my blog over on, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out. As always, these MDX queries were written against the Adventure Works DW. Here are the requirements:


  • Calendar Year


  • Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist)
  • First Date in Year


  • Take a gander at this

And here’s a sneak peak at my results:


If you think you’ve got the correct answer, send your query to

 email me

Of all the correct answers I get, I’ll pick one at the end of next week (April 29) and send out one of these really cool shirts, so good luck, peeps!


SSAS 2008 Bug: Normal Additive Measures and Semi Additive Measures in the Same Measure Group Don’t Display Properly

Devin Knight and I stumbled onto another weird SSAS bug today. We had a measure group that had two measures. The first measure was semi additive (Last Non Empty) and the second measure was a normal additive type (Sum). The additive measure would not display along side the semi additive measure, whether I browsed the cube in Excel, BIDS, SSMS, or wrote an MDX query.

Well as it turns out this is a known issue that was identified some time ago. SQL Server 2008 SP1 solves this issue: .


So if you’re ever on site working in an environment you are unfamiliar with and you experience goofy behavior where semi additive and additive measures in the same measure group do not display properly, check the SQL Server AS version! The version to look out for is 10.0.1600.

SSAS 2008 Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked perfect, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error:

Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),

Show empty cells icon

my calculations appeared to be working correctly. Apparently this is some kind of bug in the cube browser in SSMS and BIDS. You’ll notice that if you connect to your cube from Excel and browse the cube using a Pivot Table, you are able to successfully browse the cube without any strange errors. I hope this saves someone else the 45 minutes I spent trying to figure out what in the world was going on.