I am porting an application from AS2000/RS2000 to AS2005/RS2005. I have an
MDX query that I have rewritten which works from within the SQL Server
Management Studio, but it does not work from Reporting Services 2005. I am
wondering if anyone has any pointers. Here is the query:

with
set [top 10 final codes] as
'topcount([Attributed Class].[Attributed Class].[Attributed
Class].[AC1].children, 10, [Measures].[Cust Ret Last Week])'
set [cust_ret_type] as
'{[Measures].[Pct Cust Ret Last Week],
[Measures].[Pct Cust Ret Prev 4 Weeks],
[Measures].[Pct Cust Ret Prev 8 Weeks]}'
select
{[VMI].[VMI].[Filter].&[valid]} on columns,
{crossjoin({[top 10 final codes]}, {[cust_ret_type]})} on rows
from
[My Cube]
where
([Product Family].[Product Family].[Product Family].&[PF1],
[Diagnosis Code].[Diagnosis Code].[Filter].&[valid],
[Source Code].[Source Code].[Filter].&[valid])

When run from RS2005, I get the following error message:

Query preparation failed.

Additional information:
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)

My guess is that it is balking at having the [VMI] dimension on the first
axis since this states that it should not reference any dimension other than
Measues on the first axis. The only reason that I am using that dimension is
to get a single column. Is there maybe a way to do this with a measure?
Anyway, thoughts would be appreciated.

If you revert to the OLE DB for OLAP (in this case, 9.0) provider in RS
2005, rather than the Analysis Provider, there should no longer be the
limitation of only placing measures on columns.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

Very interesting. The query now runs, but I now have 2 other problems.

1) The OLE DB driver doesn't seem to support named parameters.
2) The reporting services field names all get ugly names like:
Measures_product_family_value (compared to product_family_value)
Product_Family_Product_Family_Product_Family (compared to Product_Family)

Is there any connection layer that supports all the features of OLAP and RS.

Thanks for your time,

Keehan

"Deepak Puri" wrote:

Hi Keehan,

In terms MDX support, RS 2005 seems to give with one hand while taking
away with the other - witness this entry in Chris Webb's blog:

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!412.entry

This message has apparently been acknowledged by the RS Product Team,
don't know when we can expect more:

http://blogs.msdn.com/bwelcker/archi...15/504305.aspx


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks Deepak. Looks like I will keep 2 datasources as each is better at
some things than the other. As always, much appreciation.

Keehan

"Deepak Puri" wrote: