How to add date filter in MDX where clause

• sgelle

  Valued Member

  Points: 57

  More actions

  September 9, 2014 at 7:23 am

  #295940

  Hi,

  I am new to MDX query. Can any one suggest how to write below T-SQL query into MDX query.

  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;

  I want to pass start and end date fields dynamically.

  Mondrian Cube definition:

  <Cube name="PRD_AREA_LINE_CHART">

  <Table name="well_prod_bst_horiz_og_2_yrs"/>

  <Dimension name="Prd_Area_Line_Data1" foreignKey="FileNo">

  <Hierarchy hasAll="true" allMemberName="Well_Status" primaryKey="FileNo" defaultMember="Well_Status">

  <Table name="well_prod_bst_horiz_og_2_yrs"/>

  <Level name="Date" column="Date" type="Timestamp" uniqueMembers="true"/>

  </Hierarchy>

  </Dimension>

  <Dimension name="Prd_Area_Line_Data2" foreignKey="FileNo">

  <Hierarchy hasAll="true" allMemberName="Well_Status" primaryKey="FileNo" defaultMember="Well_Status">

  <Table name="well_prod_bst_horiz_og_2_yrs"/>

  <Level name="Date" column="Date" type="Timestamp" uniqueMembers="true"/>

  </Hierarchy>

  </Dimension>

  <Measure name="BBLSOIL_TOTAL" column="bbls_oil" aggregator="sum" formatString="Standard"/>

  <Measure name="BBLSWATER_TOTAL" column="bbls_water" aggregator="sum" formatString="Standard"/>

  <Measure name="MCF_PROD_TOTAL" column="mcf_prod" aggregator="sum" formatString="Standard"/>

  <Measure name="VENT_FLARE_TOTAL" column="vent_flare" aggregator="sum" formatString="Standard"/>

  </Cube>