March 4, 2011 at 7:37 am
Hello Everyone out there,
First off, I'm using Analysis Services 2008, Visual Studio 2008 and Excel 2010.
I have a very simple Calculation in an OLAP Cube (defined using MDX in the Calculations tab in VS2008) that simply works out the %age total for the currently displayed row against the current total.
It works absolutely fine and I can see it OK when I browse the cube in either Visual Studio or SSMS. However, when I access the cube from Excel, the Calculation Name shows correctly in the Measure group in the Pivot Table Field List, but when I drag it across into the Pivot Table the column in entirely blank - it doesn't matter how I filter the data or what other measures and/or attributes I drag into the Pivot Table, this column stays resolutely blank.
I've looked at a lot of forums but I can't seem to find anyone with a similar experience.
Does anyone know what's going on here? Any help will be much appreciated.
Regards
Colin
March 4, 2011 at 10:45 am
Can you post your calc member MDX? Even a 'cleaned up' version where you change the names to protect the innocent?
Steve.
March 9, 2011 at 3:11 am
Here's the MDX that I'm using - I picked it up from an Web Forum, and as I say, it works fine except when accessig iot from Excel.
(10 * IIF(
( [Measures].[Order Count], Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.Parent ) = 0,
NULL,
[Measures].[Order Count] / ( [Measures].[Order Count], Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.Parent )
)) / 10
In the remaining Calcualtion Tab boxes, I'm specifying the following:
Name = [Order Count % age]
Parent hierarchy = Measures
Format String = "Percent"
Visible = True
Non-empty behavioue = Order Count
Associated Measure Group = (Undefined) - I've also tried associating the Calculation with the Measures group without success.
Regards
Colin
March 22, 2011 at 9:53 pm
I to have exactly the same issue.. (Help)
March 23, 2011 at 12:06 am
Reminds me a little of that footer i've seen floating 'round here 'bout being able to explain when using 😀 (no offence intended guys).
The issue is that you've got the Axis function running over Axis zero (0), which is the columns. Because you're running Excel 2010, if you move your Values (labelled "[Sigma Sign] Values" in the Areas boxes of the pivot table) to the rows, and drag your other attribute to the columns, you'll find your measure works a treat.
If you want it to work for Rows, then change it to the correct index (Rows are Axis 1) - I've pasted a 'x' and 'y' calc member measure code below for reference.
You could, of course, simply use the formating within Excel itself, and have it show the values as percentages - % of Column Total, for when dealing with rows, % of Row Total, when dealing with columns 🙂 And, again using Excel 2010, if you wanted to argue that you can't see both the raw number *and* the percentage, simply drag the same measure into the Values section again (it'll be named ABC2, where ABC is the standard measure name).
CREATE MEMBER CURRENTCUBE.[Measures].[x] AS
(10 * IIF(
( [Measures].[Order Count], Axis(1)(0)( Axis(1)(0).Count - 1 ).Dimension.CurrentMember.Parent ) = 0,
NULL,
[Measures].[Order Count] / ( [Measures].[Order Count], Axis(1)(0)( Axis(1)(0).Count - 1 ).Dimension.CurrentMember.Parent )
)) / 10 ,
FORMAT_STRING = "Percent",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Measures].[y] AS
(10 * IIF(
( [Measures].[Order Count], Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.Parent ) = 0,
NULL,
[Measures].[Order Count] / ( [Measures].[Order Count], Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.Parent )
)) / 10 ,
FORMAT_STRING = "Percent",
VISIBLE = 1;
HTH,
Steve.
April 1, 2011 at 8:13 am
To: stevefromOZ
I've just got around to trying this at it does just what I wanted.
Thanks from
Colin
P.S. I'll just have to get booked onto an MDX course somewhere or at the very least find a good book!
April 1, 2011 at 10:02 am
Glad it worked. On the book front, anything by Mosha Possumansky, George Spofford, Chris Webb would be good. If you're looking for a course, i know Brian Knight is traveling around the US *a lot* lately giving his PragmaticWorks courses, not sure if they have an MDX specific course, but if they do and he's giving it, it'll be good.
For a really gentle introduction, google "Bill Pearson +MDX" (without the quotes) and the first hit should take you one of the overview pages of the series/s that he's put together on MDX, SSAS, SSRS etc. He has nice bite size chunks of teaching nearly always with very solid explanations of what you're doing and why.
Steve.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply