Hi

Person Question Answer QuestionSet
Tom Alcohol Yes Assessment
Tom Cigarette Yes Assessment
Tom Alcohol No After3Months
Tom Alcohol No After6Months

I want to write MDX for the following

Show me the "Answer" of "After3Months" for the people who had an
"Assessment"

In this case the answer is No ( 3rd record).

Any help is appreciated.

thanks

Milind

There is not enought information about the structure of your cube to be
able to suggest any MDX. It is hard to tell what the dimensions are and
what measures you have.

Are you asking for a count of people that answered yes/no? It could be
something like the following, but I am making a lot of assumptions here.

SELECT
{measures.[PersonCount]} ON COLUMNS,
NON EMPTY {Answer.Members} ON ROWS
FROM MyCube
WHERE
(
[QuestionSet].&[After3Months]
)

Do you have some MDX that you have tried that has not worked?

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1129746841.471128.61400 (AT) z14g2000cwz (DOT) googlegroups.com>,
milind.bhabal (AT) gmail (DOT) com says...

Hi Milind,

As Darren mentioned, more information about the structure of your cube
would be needed to suggest precise MDX.

Assuming that each column of your table represents a dimension (in AS
2000), and that a person can only have one answer for a given
combination of Question and QuestionSet:

'NonEmptyCrossJoin([Answer].[Answer].Members,
{[Person].CurrentMember}, {[Question].CurrentMember},
1).Item(0).Item(0).Name'

Select {[Measures].[AnswerText]} on columns,
NonEmptyCrossJoin([Person].[Person].Members,
[Question].[Question].Members,
{[QuestionSet].[Assessment]}, 2) on rows
from QuestionCube
where (QuestionSet].[After3Months])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Darren & Deepak - Thanks for responding!!

A person is asked questions ( which are grouped in a Questionset). A
question can be single choice (one answer) or multiple choice (
multiple answers).

Dimensions are Person, Question, QuestionSet, Answer

What I am trying to do is analogous to

select Answer, Count(Person) from AnswerFact
where QuestionSet = 'After3Months' and person in (select person from
AnswerFact
where QuestionSet = 'Assessment')


(The above SQL is just for understanding)

Output
Answer No Of People
Yes 5
No 10

Person Question Answer QuestionSet
Tom Alcohol Yes Assessment
Tom Cigarette Yes Assessment
Tom Alcohol No After3Months
Tom Alcohol No After6Months

Please help

thanks

Milind

Hi Deepak

Your mdx was what I was looking for. Sorry I was unaware of the
CrossJoin SetCount.

Thanks a lot!!!!

Milind