topic Query on MDX function in OLAP in SAS Programming https://communities.sas.com/t5/SAS-Programming/Query-on-MDX-function-in-OLAP/m-p/128436#M260400 <HTML><HEAD></HEAD><BODY><P>Hi SAS Expert,</P><P></P><P>I would like to create a calculated member in SAS OLAP to perform ranking. Following is the desired result as attached (MDX.jpg)</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0"><TBODY><TR><TD style="border: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P><STRONG>Mis Month</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P><STRONG>Category</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P><STRONG>Sales Amt</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P><STRONG>Ranking</STRONG></P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Apr-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Grocery</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>9999</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Apr-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Electronic</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>5000</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>2</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Grocery</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>6213</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>2</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Beverage</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>2012</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>3</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Electronic</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>8782</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Jun-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Beverage</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>5000</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR></TBODY></TABLE><P></P><P></P><P>I found a similar solution at <A href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/050a18f3-dac6-4e3a-bf00-d92319b88714/mdx-ranking-based-on-two-dimensions-and-one-measure">http://social.msdn.microsoft.com/Forums/sqlserver/en-US/050a18f3-dac6-4e3a-bf00-d92319b88714/mdx-ranking-based-on-two-dimensions-and-one-measure</A></P><P>But it is using Microsoft platform and the syntax/ function is not available in SAS, i.e. nonempty function is not available</P><P></P><P>Can you please advise how to achieve this in SAS mdx?</P><P>Thank you.</P><P></P><P>Regards,</P><P>Mike</P><BR /><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/10581i4D3B161BF1E6F149/image-size/large?v=1.0&amp;px=600" border="0" alt="MDX.jpg" title="MDX.jpg" /></BODY></HTML> Mon, 08 Jul 2013 03:43:35 GMT mikesatriaevo 2013-07-08T03:43:35Z Query on MDX function in OLAP https://communities.sas.com/t5/SAS-Programming/Query-on-MDX-function-in-OLAP/m-p/128436#M260400 <HTML><HEAD></HEAD><BODY><P>Hi SAS Expert,</P><P></P><P>I would like to create a calculated member in SAS OLAP to perform ranking. Following is the desired result as attached (MDX.jpg)</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0"><TBODY><TR><TD style="border: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P><STRONG>Mis Month</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P><STRONG>Category</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P><STRONG>Sales Amt</STRONG></P></TD><TD style="border: solid black 1.0pt; border-left: none; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P><STRONG>Ranking</STRONG></P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Apr-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Grocery</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>9999</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Apr-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Electronic</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>5000</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>2</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Grocery</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>6213</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>2</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Beverage</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>2012</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>3</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>May-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Electronic</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>8782</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR><TR><TD style="border: solid black 1.0pt; border-top: none; padding: 0 5.4pt 0 5.4pt;" valign="top" width="113"><P>Jun-13</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="90"><P>Beverage</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; padding: 0 5.4pt 0 5.4pt;" valign="top" width="78"><P>5000</P></TD><TD style="border-top: none; border-left: none; border-bottom: solid black 1.0pt; border-right: solid black 1.0pt; background: #EAF1DD; padding: 0 5.4pt 0 5.4pt;" valign="top" width="66"><P>1</P></TD></TR></TBODY></TABLE><P></P><P></P><P>I found a similar solution at <A href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/050a18f3-dac6-4e3a-bf00-d92319b88714/mdx-ranking-based-on-two-dimensions-and-one-measure">http://social.msdn.microsoft.com/Forums/sqlserver/en-US/050a18f3-dac6-4e3a-bf00-d92319b88714/mdx-ranking-based-on-two-dimensions-and-one-measure</A></P><P>But it is using Microsoft platform and the syntax/ function is not available in SAS, i.e. nonempty function is not available</P><P></P><P>Can you please advise how to achieve this in SAS mdx?</P><P>Thank you.</P><P></P><P>Regards,</P><P>Mike</P><BR /><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/10581i4D3B161BF1E6F149/image-size/large?v=1.0&amp;px=600" border="0" alt="MDX.jpg" title="MDX.jpg" /></BODY></HTML> Mon, 08 Jul 2013 03:43:35 GMT https://communities.sas.com/t5/SAS-Programming/Query-on-MDX-function-in-OLAP/m-p/128436#M260400 mikesatriaevo 2013-07-08T03:43:35Z Re: Query on MDX function in OLAP https://communities.sas.com/t5/SAS-Programming/Query-on-MDX-function-in-OLAP/m-p/128437#M260401 <HTML><HEAD></HEAD><BODY><P>Have you investigated Proc Rank?&nbsp; It is designed to do this job for you.</P><P></P><P>Richard</P></BODY></HTML> Mon, 08 Jul 2013 09:40:43 GMT https://communities.sas.com/t5/SAS-Programming/Query-on-MDX-function-in-OLAP/m-p/128437#M260401 RichardinOz 2013-07-08T09:40:43Z