How to get 2 Separate columns names from a single dimension column name based on filters using mdx query

• raghavag7

  SSC Enthusiast

  Points: 183

  More actions

  September 6, 2014 at 2:06 am

  #289761

  I have one SSRS report which I created using a cube. In that I have a "contract name" column which I will get from a [contract] dimension. I have to get specific contracts and additional contracts from that [contract name] in the [contract] dimension. I will do this by using [type of contract] dimension by using [SPCON] and [AdCON] filters. My requirement is that I have to get specific contracts and additional contracts as separate columns in the same dataset from [contract] dimension by filtering with [type of contract] dimension values like SPCON for specific contract and ADCON for additional contracts. I wrote the query below:

  SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {([Contract].[Contract Name].[Contract Name].ALLMEMBERS * [Lob].[Lob Code].[Lob Code].ALLMEMBERS * [Region].[Region Code].[Region Code].ALLMEMBERS * [Region Parent].[Region Name].[Region Name].ALLMEMBERS * [Lob Parent].[Lob Parent].[Lob Parent].ALLMEMBERS ) } ON ROWS FROM

  (SELECT STRTOSET(@LoB,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Sub_LoB,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Region,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Sub_Region,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Customer,CONSTRAINED) ON 0 FROM

  ( SELECT ( { [Type Of Contract].[Type Of Contract].&[SPCON] AS [SpecificContract]} ) ON COLUMNS FROM [iWise])))))) WHERE ( [Type Of Contract].[Type Of Contract].CurrentMember )

  ...to find specific contracts. Can anyone suggest a way to get additional contracts also in that query as separate column?

  SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {([Contract].[Contract Name].[Contract Name].ALLMEMBERS * [Lob].[Lob Code].[Lob Code].ALLMEMBERS * [Region].[Region Code].[Region Code].ALLMEMBERS * [Region Parent].[Region Name].[Region Name].ALLMEMBERS * [Lob Parent].[Lob Parent].[Lob Parent].ALLMEMBERS ) } ON ROWS FROM

  (SELECT STRTOSET(@LoB,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Sub_LoB,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Region,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Sub_Region,CONSTRAINED) ON 0 FROM

  (SELECT STRTOSET(@Customer,CONSTRAINED) ON 0 FROM

  ( SELECT ( { [Type Of Contract].[Type Of Contract].&[ADCON] AS [AdditionContract]} ) ON COLUMNS FROM [iWise])))))) WHERE ( [Type Of Contract].[Type Of Contract].CurrentMember )

  i wrote above query to filter Addition contracts by using ADCON filter of type of contract dimension as separate dataset

  but i want both addition contracts and specific contracts in single dataset

  that iam unable get can any one suggest me how can get this....