mdx switch function on parameter conditional

i need run either topcount or bottomcount based on input parameter. not iif work tried case still getting error: (original topcount code works :-) ) ... have code repetition or can 1 somehow replace topcount either topcount or bottomcount input parm? tia, mic.

 

<execute xmlns="urn:schemas-microsoft-com:xml-analysis">
  <command>
    <statement>
      with
      member [measures].[pi] as
      (
      [measures].[prev iss amt]
      )
      member [measures].[ni] as
      (
      [measures].[next iss amt]
      )
      member [measures].[incr] as
      ([measures].[next iss amt]-[measures].[prev iss amt])
      member [measures].[incr%] as
      (([measures].[incr])/[measures].[prev iss amt])
     
      case @pos1
      when "bottom" then
 
      set [top 10 ni of 2010] as   
      bottomcount(
      filter(
      filter(
      {[dim cmplx].[cmplx nm].members},
      ([measures].[pi] &gt; strtovalue(@minrng))
      and
      ([dim cmplx].[div cd].[div cd].[bc])
      ),
      [measures].[pi] &lt; strtovalue(@maxrng)
      ),
      20,
      ([measures].[incr%], strtomember("[dim cmplx].[cmplx yr].&amp;[" + @cmplxyr + "]"))
      )

      else
     
      set [top 10 ni of 2010] as
      topcount(
      filter(
      filter(
      {[dim cmplx].[cmplx nm].members},
      ([measures].[pi] &gt; strtovalue(@minrng))
      and
      ([dim cmplx].[div cd].[div cd].[bc])
      ),
      [measures].[pi] &lt; strtovalue(@maxrng)
      ),
      20,
      ([measures].[incr%], strtomember("[dim cmplx].[cmplx yr].&amp;[" + @cmplxyr + "]"))
      )

      end

      select
      {[measures].[pi],
      [measures].[ni],
      [measures].[incr],
      [measures].[incr%]}
      on columns,
      {[top 10 ni of 2010]} on rows
      [storm star]
      (strtomember("[dim cmplx].[cmplx yr].&amp;[" + @cmplxyr + "]"))

    </statement>
  </command>
  <properties>
    <propertylist>
      <axisformat>tupleformat</axisformat>
      <format>multidimensional</format>
    </propertylist>
  </properties>
  <parameters>
    <parameter>
      <name>cmplxyr</name>
      <value>2010</value>
    </parameter>
    <parameter>
      <name>minrng</name>
      <value>50</value>
    </parameter>
    <parameter>
      <name>maxrng</name>
      <value>500000</value>
    </parameter>
    <parameter>
      <name>pos1</name>
      <value>top</value>
    </parameter>
  </parameters>
</execute>

 

with set topcountset as topcount  ({[geography].[geography].[city].members    *[date].[fiscal].[fiscal year].members}  , 5  , [measures].[reseller sales amount]  ) set bottomcountset as bottomcount  ({[geography].[geography].[city].members    *[date].[fiscal].[fiscal year].members}  , 5  , [measures].[reseller sales amount]  ) set setany as iif(2=1,bottomcountset,topcountset)  select [measures].[reseller sales amount] on 0, setany on 1 from [adventure works] where([product].[product categories].bikes) 

can use replace 2=1 @pos1="bottom"/'bottom'. test can set 2=1/1=1.

 

 

 

---

vinu

SQL Server  >  SQL Server Analysis Services