Order by in Dimension attribute of SSAS

• keaarpee

  SSC Journeyman

  Points: 95

  More actions

  November 6, 2009 at 7:08 am

  #136784

  Hi, I have got Employee dimension with the following attributes.

  [Biz Group Code],[Biz Group Name],[Change Date]. I want to sort [Biz Group Code] based on [Change Date]. I am using order by and order by attribute using attributekey based on change date.

  One more thing to be noted is I have used [Biz Group Name] as Name column for [Biz Group Code], so that when I browse name will show up rather than code.

  The data is looks like Biz group code is same but different names in different dates. My requirement is to get the latest biz name associated with code ie., when I aggregate show up the latest biz name associated with code.

  ex: [Biz Group Code],[Biz Group Name],[Change Date].

  NN , GRP_OLD , 7/1/09

  NN , GRP_MED , 8/1/09

  NN , GRP_NEW , 9/1/09

  YY , GP_OLD , 7/1/09

  YY , GP_MED , 8/1/09

  YY , GP_NEW , 9/1/09

  At aggregation I would liek to see the out put as folows

  [Biz Group Code], ORg count

  GRP_NEW, 3

  GP_NEW,3

  Please note that I set name as group name for code.

  No matter what I set up, am getting the following out put

  [Biz Group Code], ORg count

  GRP_MED, 3

  GP_MED,3

  It is taking alphabetical order name. One more thing that I am able to sort correctly group name attribute alone order by attribute using attributekey based on change date but we want the same effect for group code with name set to group name attribute.'

  Please advise.

  Thanks

  >Ravi