January 22, 2013 at 9:27 am
Hi
I have an SSRS parameter for Financial year (2009/2010, 2010/2011 etc). However, When I execute my MDX I get an extra row as follows:
Headers:
Financial Year, ParameterCaption,ParameterValue, ParameterLevel
Values:
Blank Value, (null), [Date].[Financial Year.&[]
How do I get rid of this superflous row?
Here's my code:
WITH MEMBER [Measures].[ParameterCaption] AS [Date].[Financial
Year].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS
[Date].[Financial Year].CURRENTMEMBER.UNIQUENAME MEMBER
[Measures].[ParameterLevel] AS [Date].[Financial
Year].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Date].[Financial Year].CHILDREN ON ROWS FROM [APC_ContractMonitoring]
I'm pretty new at MDX, so any help would be much appreciated.
Cheers
Canazei
January 22, 2013 at 2:54 pm
Have you tried the NONEMPTY() function?
http://msdn.microsoft.com/en-US/library/ms145988(v=sql.105).aspx
HTH,
Rob
January 23, 2013 at 2:47 am
Hi Rob
Just tried that and the result is the same.
Just wondering about a where clause..
Cheers
C
January 23, 2013 at 12:22 pm
What about the NON EMPTY keyword? Did you use the NONEMPTY function or the NON EMPTY keyword?
http://thatmsftbiguy.com/nonemptymdx/
HTH,
Rob
January 25, 2013 at 3:08 am
Hi Rob
Bit late in replying as I was away yesterday.
I've just tried both NON EMPTY and the NONEMPTY() function and the latter has pointed me in the right direction, even though I still can't remove the blank parameter value.
The reason? - Even though the value against the date parameter itself is blank, there are other records in the cube recorded against the blank value. So I don't actually need to delete the value until I find out the reason why. Once the records are amended I'll use the NONEMPTY() function and that should do the trick!
Many thanks for your help, I'm grateful.
C
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply