where clause contain > in MDX
HI select top 2 * from table name where date>09 oct,10 how to write where date>09 oct,10 in mdx amit
October 14th, 2010 3:05pm

you cannot use WHERE clause in MDX. Instead you shoulduse FILTER function or ":" range appropriatly. In this case range will work fine. Find the sample query below This gets top 2 dates greater than 24-July-2005 SELECT {(null)} ON 0, topcount(strtomember("[Date].[Date].&[20050724]"):[Date].[Date].[null] ,2,(null)) ON 1 FROM [Adventure Works]
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 3:10pm

0 Vote As Helpful you cannot use WHERE clause in MDX. Instead you shoulduse FILTER function or ":" range appropriatly. In this case range will work fine. Find the sample query below This gets top 2 dates greater than 24-July-2005 SELECT {(null )} ON 0, topcount (strtomember ("[Date].[Date].&[20050724]"):[Date].[Date].[null] ,2,(null )) ON 1 FROM [Adventure Works] what could i do if i want to see top 2 dates less than 24 july,2005 amit
October 14th, 2010 3:21pm

reverse the range as below SELECT {(null )} ON 0, topcount ([Date].[Date].[null]:strtomember ("[Date].[Date].&[20050724]") ,2,(null )) ON 1 FROM [Adventure Works]
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 3:30pm

Please post these MDX questions in the SSAS forums to get more answers.
October 14th, 2010 3:31pm

HI Sorna, ya i can understand ,i suppose to raise question in SSAS forum.I will be carefull from next time, By the mean time ,i am 80% done through with the above query,will you please solve my problem this time in this forum. My requirement is : SELECT {(null )} ON 0, topcount ([Date].[Date].[null]:strtomember ("[Date].[Date].&[20050724]") ,2,(null )) ON 1 FROM [Adventure Works] though with the help of above query i found the date desired but now i have one measure say zzx,now i want last 2 date where date < 24 july and zzx>0. Kindly suggest what to do for this. Regards Amit
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 4:30pm

I think this is the same question you have asked in another thread and i have posted my answer there. Take a look into it http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/bd9ab745-551a-44ab-97cb-42cc02bff842 and you should combine both MDX as below SELECT {(null)} ON 0, TOPCOUNT ( FILTER ({[Date].[Date].[null]:strtomember("[Date].[Date].&[20050724]")} ,([Measures].[Internet Sales Amount]>15000 )) ,2,(NULL)) ON 1 FROM [adventure works]
October 14th, 2010 4:38pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics