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 Services

Hello,
I am currently working on creating badges and labels. I want them to
be on 2 columns. I set the column property of the report to 2 and
added the function =Ceiling(RowNumber(Nothing)/20) in for the List
data region that I used. I have also tried using a table but it didnt
work for me. Let me know if you know the trick to creating a 2
columned badge in reporting services.
Thanks,
NunuOn Apr 14, 11:42=A0am, nardosa...@.gmail.com wrote:
> Hello,
> I am currently working on creating badges and labels. I want them to
> be on 2 columns. I set the column property of the report to 2 and
> added the function =3DCeiling(RowNumber(Nothing)/20) in for the List
> data region that I used. I have also tried using a table but it didnt
> work for me. Let me know if you know the trick to creating a 2
> columned badge in reporting services.
> Thanks,
> Nunu
Any insight'

Reporting server is not so smart as Analysis server

Hi,

I have a fact table with 2 columns value A and value B

In Analysis server I add a calculated column C as A / B

Now my data looks like this

A B C

0 10 0

5 10 0.5

If you add totals in Analysis server you get

5 20 and 0.25 which is correct

If you add totals in Reporting server you get

5 20 and 0.5 which is not correct.

How can I fix this ?

Thanks in advance

Constantijn

If you are using a table report, in your group footer you could add an expression for C that equates to

=sum(Fields!A.value)/sum(Fields!B.Value)

|||

Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.

|||

Teo,

Many thanks for that tip - I had totally missed that little trick.

Will

|||Thanks, I wasn't aware of the that function

Reporting server is not so smart as Analysis server

Hi,

I have a fact table with 2 columns value A and value B

In Analysis server I add a calculated column C as A / B

Now my data looks like this

A B C

0 10 0

5 10 0.5

If you add totals in Analysis server you get

5 20 and 0.25 which is correct

If you add totals in Reporting server you get

5 20 and 0.5 which is not correct.

How can I fix this ?

Thanks in advance

Constantijn

If you are using a table report, in your group footer you could add an expression for C that equates to

=sum(Fields!A.value)/sum(Fields!B.Value)

|||

Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.

|||

Teo,

Many thanks for that tip - I had totally missed that little trick.

Will

|||Thanks, I wasn't aware of the that function

Reporting on a Cube -- Time Dimension Issue

Hello,

I have an Analysis Services Cube that I would like to report on. However, the Time Dimension currently only has four columns, Day of Month, Month(name) , Year, and DateKey (DateTime representation at midnight for every day). Thus when I drag the month attribute onto the report, it is sorted April - August - December - etc. instead of Jan - Feb - Mar. How do I fix this? I remember reading something in the MSDN Library about it but I can't find it again now.

ThomasJust remove sorting for Time group in Report. It's well known problem.|||Hmm, I haven't added any sorting, this is just drag-and-drop from the list of dimensions in the "Report Builder" tool for Reporting Services and in Visual Studio. So I haven't added any sorting, however in VS I can modify the User Sort - Value property. Unfortunately if I remove it or set it to another field (my date key field) VS crashes when I try to preview. Any other suggestions?|||The Month attribute KeyColumns should be defined as a composite key consisting of the Year and Month column. This should get sorting right because if no Sort attribute is specified UDM will sort by the key. Take a look at how the Month attribute is defined in the AW sample cube.|||Thanks a bunch, but here's a funny story. I actually figured that out and was coming here to post the answer in case any one else needed it. Seems like you beat me to the punch. Thanks again anyways!