MDX Syntax in SSRS

Hi Everyone:

I am using SSRS to generate an MDX query for me. I don't know MDX, ans am trying to learn on the fly. This is what it has done:

WITH MEMBER [Measures].[Qty]

AS '-1 * [Measures].[Quantity]'

MEMBER [Measures].[Fill Price]

AS 'IIF( [Dim Trade Info].[Hold Type] IS [Dim Trade Info].[Hold Type].&[CASH],[Measures].[Direction],[Dim Trade Info].[Hold Type].NAME)'

SELECT NONEMPTY ({ [Measures].[Qty], [Measures].[Fill Price] } )

ON COLUMNS, NONEMPTY ( { ([Fill Date].[Full Date].[Full Date].ALLMEMBERS

* [Dim Team].[Team].[Team].ALLMEMBERS

* [Dim Trader].[Trader Name].[Trader Name].ALLMEMBERS

* [Dim Trade Info].[Hold Type].[Hold Type].ALLMEMBERS

* [Dim Model].[Model Name].[Model Name].ALLMEMBERS

* [Dim Market].[Report Name].[Report Name].ALLMEMBERS ) } )

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

( SELECT ( [Fill Date].[Full Date].&[2007-08-08T00:00:00]

: [Fill Date].[Full Date].&[2007-08-09T00:00:00] )

ON COLUMNS FROM [Trade]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

For the second member, Fill Price, In the false part of the IIF ([Dim Trade Info].[Hold Type].NAME I was able to generate the NAME after much trial and error. However, what I really want is the VALUE. When I try .VALUE, the query gives me null everywhere. What is the correct syntax? I have tried:

[Dim Trade Info].[Hold Type].VALUE

[Dim Trade Info].[Hold Type].CurrentMember

[Dim Trade Info].[Hold Type].].[Hold Type].VALUE

MEMBERTOSTR([Dim Trade Info].[Hold Type].VALUE)

[Dim Trade Info].[Hold Type].[All]

[Dim Trade Info].[Hold Type].All

[Dim Trade Info].[Hold Type].ALLMEMBERS

.

.

.

Well, you get the idea. I've pretty much tried every thing I can think of.

Barry

I think I know what you're asking. If not, disregard this post and slap me silly

If you by VALUE mean: the value of the current member of the Measures dimension that intersects with the current member of the attribute hierarchies in the context of the query. As described in BOL http://technet.microsoft.com/en-us/library/ms145982.aspx

Then you need [Fill Price] to return the [Measure] you want instead of .NAME or you need to select the [Measure] you want on COLUMNS.

Best regards

- Jens

|||

I think you may be looking for the key value? If so, try this:

[Dim Trade Info].[Hold Type].CurrentMember.Properties("Key")

Alternatively, you can also use any of the other member properties (whether it be the intrinsic ones, i.e. key, name, value, etc.) or any of the custom member properties you add via the dimension designer.

If this is indeed what you are after, see for more details:

http://msdn2.microsoft.com/en-us/library/ms144780.aspx

Hope this helps,

John

|||Thanks John! That is helpful and seems to work for strings. However, when I try to apply it to numbers I get all zeros. :-(|||

You have access to vb functions - so you can do things such as CInt ([Dimension].[Attribute].CurrentMember.Properties("property name") ) to convert it to say, an integer. Of course you can use CDBL() too, etc.

If they are returning zero's, are the member properties truly zero in the dimension? Is the IIF statement working properly? Just throwing out some ideas...

Good luck,

John

|||

Thanks for everyone's help. I still can't get this to do what I want. I think the problem occurs when I want to select from a different table. When I do the intial query:

AS 'IIF( [Dim Trade Info].[Hold Type] IS [Dim Trade Info].[Hold Type].&[CASH],[Measures].[Direction],[Dim Trade Info].[Hold Type].NAME)'

It works OK. As long as I select something from Measures or [Trade ID].[Hold Type] its OK. However, there are other instances where I want to select values from another table based on the value of one table. In those cases, I want to do something like this :

AS 'IIF( [Dim Trade Info].[Hold Type] IS [Dim Trade Info].[Hold Type].&[CASH],[Dim TradeId].[Spot 3 Month].NAME), [Dim TradeId].[Price All In].NAME)'

or

AS 'IIF( [Dim Trade Info].[Hold Type] IS [Dim Trade Info].[Hold Type].&[CASH],[Dim TradeId].[Spot 3 Month].CurrentMembers.Properties("Name"), [Dim TradeId].[Price All In].CurrentMembers.Properties("Name"),)'

In the inital case, I get the actual value that I want. In case two, I get all nulls. In the last one, I get 'All' for the name. I suspect that I am not telling it to connect the two tables.

I am not an MDX programmer. I am just trying to make some small changes to the MDX that is generated by the query builder in SQL Server reporting Services. I suspect I need a short course in MDX if there is a such thing. :-)

Anyway, if anyone can suggest a short answer, that would be great. If not, I'll just show the user both fields.

Barry

|||

Unfortunately, I think the short answer in this case is to include both fields. MDX isn't like SQL at all - the only thing it has in common are Select, From and Where. From there, MDX is all about sets, relationships between attributes, etc. You can't (easily) join together dimensions as you would in SQL. I am still very new to AS & MDX, I found this book helpful in understanding concepts:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/11/fast_track_to_mdx.aspx

Also, another book I found helpful:

http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080

Good luck!

|||

Thanks for all your help John. We decided to put all the values I need in the cube, where I am able to select the ones I need from different columns based on other values.

We have Safari here, and I did find another MDX book than the one you recommended. If I need to do any more, I think I'll give it a read.

Barry