Help in MDX or calc Members

• inampudianil

  Ten Centuries

  Points: 1274

  More actions

  September 10, 2012 at 3:36 am

  #150109

  Hi,

  I have 2 dimensions prod and prod_warr

  1) prod-> which has list of prod's

  prod table schema

  prod_id,Name

  ------------

  1,a

  2,b

  3,c

  2) prod_warr-> which stores the warrenty related items with ref to customer.

  prod_warr schema

  prod_warr_id,prod_id,cust_id,warr_st_dt,warr_en_dt

  ---------------------------------------------------

  1,1,2,2010-12-01,2012-12-01

  2,2,4,2011-12-01,2012-12-01

  3,1,12,2010-12-01,2012-12-01

  4,2,14,2010-12-01,2012-12-01

  Now,i need to Count the list of products with warrenty(calender dates is. in b/w warr_st_dt and warr_en-dt) per month wise.how can i implement in Calc Measures in SSAS.

  Here is the SQl version query for the above requirement.

  Select YEAR(EFF_DT)as EFFyear,MONTH(EFF_DT) as Effmonth,COUNT(PROD_ID)as prodcount from dbo.TM_warr dwith(nolock)

  inner join TM_prod on d.prod_id=IBU.prod_id

  where (EFF_DT between warr_strt_dt and warr_end_dt)

  group by YEAR(EFF_DT),MONTH(EFF_DT)

  Now. i can i implement in MDX.

  Thanks in advance

  Best Regards

  aa

• Raunak Jhawar

  SSCoach

  Points: 15701

  More actions

  September 17, 2012 at 12:08 am

  #1538406

  You need to create caluclated measures for the COUNT measure and to filter by date you can use the colon(:) operator as in January 2012 : April 2012, which will bet all members that fall in the specified period OR you can explicitly write each member

  Serach more about STRTOMEMBER and STRTOSET

  Raunak J