question

MuraliKrishna avatar image
MuraliKrishna asked

SSRS Report using cube - How can I get two separate column names from a single column using MDX?

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....
ssrsmdx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.