I have a table with dozens of columns, each of which has a value of
1-5. Each column represents a response to a question in a survey. I
need to produce a report that displays each of the questions in a row,
and the distribution of responses for each of the possible values 1-5.
In other words,

Survey Response 1 2 3 4 5 All
------------------------ ------ ------ ------ ------ ------ ------
Customer felt satisfied 5 8 24 84 112 233
Customer felt valued 1 18 48 97 42 206

TIA

One way would be to have a question dimension and a scale dimension. Then
you would just do something like this for the MDX. This assumes you'd have
a count measure defined in your cube based on a response id.

SELECT {[QuestionScale].members} on columns,
{[Question].members} on rows

FROM

SurveyCube

Where [Measures].[ResponseCount]


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"Greg" <greg.block (AT) comcast (DOT) net> wrote

Thanks - that was an excellent approach! I had to "normalize" the
table by creating another table with a row for each response (rather
than a column for each response). Then I cubed the table, developed an
MDX like so:

SELECT
{
CrossJoin(Measures.Members, [Responses].Members)
} ON COLUMNS,
{
[Topics].Members
}
ON ROWS
FROM [Survey]
WHERE [Surveys].[All Surveys].[My Survey]