CODEWITHSUNDEEP
t-sqlmdx
sgelle
sgelle

Reputation: 53

How to write date filter in MDX where clause?

I am new to MDX. Could please suggest how to write below T-SQL query in MDX Query language.

T-SQL:

    SELECT wp.date,Sum(wp.bbls_oil)   
         AS BBLSOIL_TOTAL,Sum(wp.bbls_water) 
         AS BBLSWATER_TOTAL,Sum(wp.mcf_prod) 
         AS MCF_PROD_TOTAL,Sum(wp.vent_flare)
        AS VENT_FLARE_TOTAL 
    FROM  well_prod_bst_horiz_og_2_yrs wp, well_index wi
    WHERE wp.fileno = wi.fileno 
      AND wp.date <= :startDate 
      AND wp.date >= :endDate
      AND wi.apino IN (:wellids) 
   GROUP BY wp.date ORDER BY wp.date ASC";

In the above query, Start and End date values are supplied dynamically.

Upvotes: 0

Views: 5335

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Assuming you have measures named BBLSOIL, BBLSWATER, MCF_PROD, and VENT_FLARE_TOTAL and your date attribute is named [Date].[Date], and your :startDate contains [Date].[Date].&[20120101] and your :endDate contains [Date].[Date].&[20141231], and your cube is named Name of your Cube you would write

SELECT {
       Measures.[BBLSOIL],
       Measures.[BBLSWATER],
       Measures.[MCF_PROD],
       Measures.[VENT_FLARE_TOTAL]
       }
       ON COLUMNS,

       [Date].[Date].&[20120101] : [Date].[Date].&[20141231]
       ON ROWS
  FROM [Name of your Cube]

i. e. you put an MDX set containing the list of required measures on the columns axis and you put a range (specified by :) on the rows axis. Aggregations like Sum and GROUP BY are not necessary inn MDX, these are handled by the cube definition.

Upvotes: 4

Related Questions