Hi

I have a 4 dimensions Person, Question, Client, QuestionSet and a
measure Encounter

I am looking for an output like the below

Answer Six Twelve
_________ ______ _____
Constantly 5 7
Rarely 1 0
Never 3 2

Six = Count of Encounter ( Where QuestionSet = DiabetesSi)
Twelve = Count of Encounter ( Where QuestionSet = DiabetesTw)

The sets are same except for the QuestionSet. I want to show them as
columns


MDX that I am trying

with
set [Template] as ''NonEmptyCrossJoin({[Person].[Person].members},
{[Question].&[SecondHandSmoke]}, {[Client].&[52]})''
-- This is the common set

member [Measures].[Six] as ''<What do I write here>' -- This should use
[QuestionSet].[DiabetesSi] for the measure total
member [Measures].[Twelve] as ''<What do I write here>' --This should
use [QuestionSet].[DiabetesTw] for the measure total


Select {[Answer].[Value].members} on rows,
{[Measures].[Six], [Measures].[Twelve]} on columns
from DM_Answer

I dont know what to write in the calculated members above so that the
output shows Six and Twelve as Columns

Please help

Milind

Hi Milind,

Not sure what you mean by "Count of Encounter", but here's my
interpretation of what you want:

member [Measures].[Six] as
'([Measures].[Encounter, [QuestionSet].[DiabetesSi])'
member [Measures].[Twelve] as
'([Measures].[Encounter, [QuestionSet].[DiabetesTw])'

Select {[Answer].[Value].members} on rows,
{[Measures].[Six], [Measures].[Twelve]} on columns
from DM_Answer
where ([Question].&[SecondHandSmoke], [Client].&[52])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Perfect. Thanks alot Deepak!!!

Milind