Dimension members as columns in list

[Kindness]

i have two dimensions: Questions and Organizations. and one fact table with textual responses.

fct (
question id
org id
fct_response
Last Edited dt
Last Edited by
)

So, I need to create report, which look like somehow like this.
----------------------------------------------
                | org 1           | ... | org n           |
----------------------------------------------
Last Edited| Last Edited   | ... | Last Edited   |
dt             | dt 1 1          | ... | dt 1 n          |
----------------------------------------------
Last Edited| Last Edited   | ... | Last Edited   |
by             | by 1 1         | ... | by 1 n          |
----------------------------------------------
question 1 | response 11 | ... | response n1 |
----------------------------------------------
...
----------------------------------------------
question n | response 1n | ... | response nn |
----------------------------------------------

I can't use crosstab becouse it doesn't display a textual measures. List displays textual measures, but i don't know how to display organizations from dimension as columns in list.

so if it posible to create dymanic column set in list, please, tell me! 

[Lynn]

Deja Vu All Over Again....seems you've posted this question before. I think the moderators don't like that.

Is your package relational? If so, then here are two possibilities.

1) Is the maximum number of organizations fixed? If so, construct your query to flatten the data. The query items would be:

Code Select Expand


[Question], [Org1Response], [Org1LastEditDate], [Org1LastEditBy], ... , [OrgNResponse], [OrgNLastEditDate], [OrgNLastEditBy]

You can do this by making multiple queries and then union them together, or a single query with the appropriate expressions to bucket the data. Something like:

Code Select Expand

if ( [OrgID] = 1 ) then ( [Response] ) else ( '' )

Obviously this only makes sense if the maximum number of organizations is known, relatively stable, and there are not a very large number of them.

2) Use a list and a repeater or repeater table in a master-detail relationship. Your main list query will just present the questions. The secondary query will have the question as well as the organization responses. Drag a repeater table into the list, associate it with the second query, and connect the repeater to the list via a master-detail relationship.

This isn't always the best performing option as the detail query gets fired repeatedly. If your data sets are small it may be fine for you.

Formatting may be challenging if not all organizations respond to each question, but you can play around to see if this is feasible for your situation. You could get around this situation by using outer joins to ensure there is a row for every question/organization combination. The reponse could indicate 'None' for situations where there isn't one.

[Kindness]

thank you ever so much!  :)