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 .

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 .

Reporting off of a Database Mirror

All,

I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:

http://msdn2.microsoft.com/en-US/library/ms175511.aspx

The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."

My question is, how can you programmatically direct connections to the latest snapshot?

Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.

Thank you,

Racer-D

hi racer,

You can change your connection string to connect directly to the database snapshot.

database snapshot are treated as a new copy of the database

regards,

joey

|||And programatically means that you can do one of two things. You can either interrogate sys.databases for the most recent creation date or you can use a standard naming convention and direct based on the naming convention.

Reporting off of a Database Mirror

All,

I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:

http://msdn2.microsoft.com/en-US/library/ms175511.aspx

The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."

My question is, how can you programmatically direct connections to the latest snapshot?

Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.

Thank you,

Racer-D

hi racer,

You can change your connection string to connect directly to the database snapshot.

database snapshot are treated as a new copy of the database

regards,

joey

|||And programatically means that you can do one of two things. You can either interrogate sys.databases for the most recent creation date or you can use a standard naming convention and direct based on the naming convention.

Reporting off of a Database Mirror

All,

I have created an asynchronous database mirror and a snapshot of the mirrored database similar to what is described in the following article:

http://msdn2.microsoft.com/en-US/library/ms175511.aspx

The article says that once you create the mirror that "At this point, new client connections can be programmatically directed to the latest snapshot."

My question is, how can you programmatically direct connections to the latest snapshot?

Does anyone have a suggestion, or know what the article is getting at? We will use SQL Server Reporting services to query off of the database snapshot.

Thank you,

Racer-D

hi racer,

You can change your connection string to connect directly to the database snapshot.

database snapshot are treated as a new copy of the database

regards,

joey

|||And programatically means that you can do one of two things. You can either interrogate sys.databases for the most recent creation date or you can use a standard naming convention and direct based on the naming convention.