1 Reply Latest reply: Feb 5, 2012 2:16 PM by Sumant RSS

    MDX query with runtime parameters

    858140
      Hi,

      I am facing difficulty in passing parameters to MDX Query below.I want to add below conditions to the query pasted below and should be able to pass the values run time.

      Where [Measures].[Forecast_Unit] > ? and [Measures].[Gross_Margin_Percent] < ?

      MDX Query without paramters

      SELECT
      {
      [Measures].[Forecast_Unit],
      [Measures].[Selling_Price],
      [Measures].[COGS],
      [Measures].[Cost],
      [Measures].[Gross_Margin_Percent]
      }
      ON COLUMNS,
      {
      CROSSJOIN
      (
      {[Product].[Part_Number].Members},
      { systemperiod([Calendar].[Quarter]).lead(1)}
      )
      }
      ON ROWS
      FROM [Gross_Margin]
      WHERE [Measures].Forecast_Unit > Parameter("forecastUnits",double)
      AND [Measures].Gross_Margin_Percent < Parameter("grossMargin",double)

      Thanks,
      Hari Devarapalli
        • 1. Re: MDX query with runtime parameters
          Sumant
          I suspect you must have solved this problem already as this thread is old.

          For passing parameter, function parameter is right. I think, you must facing issue in executing MDX with where condition. right?

          for this kind of condition I would use filter functionality of MDX.

          filter(<your cross join>,[Measures].[name] > parameter(<parameter Name>,<type>)

          for example
          SELECT [Measures].[Internet Sales Amount] ON 0,
          
          FILTER(
          
          [Date].[Date].[Date].MEMBERS
          
          , [Measures].[Internet Sales Amount]>10000)
          
          ON 1
          
          FROM
          
          [Adventure Works]
          Note: Have seen slow behavior sometime while using filter

          Edited by: Sumant on Feb 6, 2012 1:46 AM