Reporting Services - Analysis Services - Displaying Dimension Members as Columns

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]

Any ideas?

Thanks,
Arjun

As the error message says, the SSRS Analysis Services provider expects dimension on rows. If you want to use this query, you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0. The limitation of this approach is that the OLE DB driver doesn't support parameters. If you need to parameterize your report, you need to use an expression-based query that concatenates the parameter values, a-la RS 2000.

|||Thanks Teo, that makes sense. I found the OLE DB driver and I'll use this method. Really strange limitation in SSRS, hope they get rid of it in the next release.|||

you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0

How do you do this, Please?

|||When setting up your data source, choose the Microsoft OLE DB Provider for Analysis Services 9.0.|||thanks, but that choice does not show up when creating a datasource in reporting services 2005. OLE DB provider for analysis service 9.0 is definitely installed on that machine. I do see that choice from Excel when creating a pivot table based on a cube. Thanks for your help.|||Chose OLE DB as Type, then click on the Edit button.|||Thanks, it works .