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 Service MDX problem

Hi all,
I am using Analysis Services cubes as backend to my Reporting Service
reports currently. I have a situation where I select
measures in axis 0
and
a heirarchy members & a level(in another heirarchy) in axis 1.
Now the result set will look like...
heiarchymember1 heirarchymember2 heirarchymember3 level measure
-- -- --
-- --
but my report requires the values of level column above to be column names
and the measure value to be values for those columns. In simple words I want
to transpose a column to rows and measure values should appear as data under
those transposed columns.
I cannot make use of matrix dataregion to do this as I have other
restriction. I can use only table dataregion. Is there a way (say some
function) available in mdx to do this kind of transpose with cube data in
reporting services.
Adventure works query which is good to explain my situation is
SELECT [Measures].[Total Product Cost] ON COLUMNS,
{([Product].[Product Categories].MEMBERS,
[Geography].[Geography].[Country].MEMBERS)} ON ROWS
FROM [Adventure Works]
Pl run the above query from reporting service report builder because if you
run in management studio u will see in different format.
Now when you run the above query in RS you can see Category, SubCategory,
ProductName, Country,Total Product Cost columns. What I want is
Category, SubCategory, ProductName,country1,country2,... as columns and the
relevant Total Product Cost measure values under the country names as data.
If it is sql I can use Case statement to do this kind of transposing. But
here in reporting service MDX we can only use measures no other dimension in
axis 0, which actually leads to this kind of transposing requirements.
Pl let me know what is the best way to accomplish this.
ThanksHi Wei Lu,
Since our reporting requirements are such that we cannot use matrix and we
have to use table with different groups all placing one below the other, I
tried a different solution. I found somewhere that If I make use of OLEDB
provider instead of MS Analysis Services Provider so that I dont have
restriction to put only Measures on columns axis. Now my question is what is
flexibility that I loose if I use oledb provider for analysis services 9.0
than Microsoft Analysis services provider?
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ringt,
> Thank you for your posting!
> From your description, my understanding of this issue is: You want to use
> the MDX in the report and you want to show the heirarchymember in the
> column. If I misunderstood your concern, please feel free to let me know.
> I suggest you use the Matrix control in your report to show the data.
> The attachment is the Report I designed using the Matrix. Please check and
> let me know whether this meet your requirement.
> The datasource is the Adventure Works and the MDX query is the one you
> posted.
> I put the Country in the column group and Total Product Costs in the
> Detail, others in the Row group.
> Please let me know the result so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ringt,
Thank you for the update.
Based on my research, this issue is by design. Although the MDX you want to
use Multi Dimensions in the columns axis could run in the Analysis
Services, the data is extracted as a flattened row-set and can be used by
the report builder only as a row-set. So the Report Builder will not care
about certain things related to OLAP.
I suggest you use the OLEDB for AS 9.0 driver so that you could add Multi
Dimensions in the columns axis.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Ringt,
How are you doing on this issue, does Wei's last reply help clarify the
problem further or have you got further progress on this? If there is still
anything we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Reporting from Cubes?

Hi,
I have a question. Can we use Reporting Services to
report from OLAP Cubes. If yes, can we write MDX also to
report from Cubes using Reporting Services.
Thanks
Steveshort answer: yes and yes. Look at MSDN for a slide show from teched'04 on
MDX expressions and Reporting Services. If you use proclarity you might
also want to contact Proclarity for a reference for their soon to be
product - an add in to Reporting Services for Proclarity views.
hope this helps.
dlr
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> Hi,
> I have a question. Can we use Reporting Services to
> report from OLAP Cubes. If yes, can we write MDX also to
> report from Cubes using Reporting Services.
> Thanks
> Steve|||For RS 2000 please read this MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
You may also want to download these samples:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
RS 2005 Beta 2 contains a graphical MDX query designer which will generate
the MDX for you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> Hi,
> I have a question. Can we use Reporting Services to
> report from OLAP Cubes. If yes, can we write MDX also to
> report from Cubes using Reporting Services.
> Thanks
> Steve|||I have always like this paragraph from the msdn article:
>For example, the two MDX queries below return the exact same
information.However, only one is ideal when used as >a source for a matrix
in a report.
>MDX Expression
>SELECT
> CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members}) on
columns,
> NON EMPTY ([Store].[Store City].members) on rows
>FROM SALES>MDX Expression
>SELECT
> CROSSJOIN ({[Measures].[Unit Sales]},{[Time].[Month].members}) on
columns,
> NON EMPTY ([Store].[Store City].members) on rows
>FROM SALES>By the time you've finished reading this whitepaper, you should
be able to discern which of these expressions will >work as the source of a
matrix, and which will not.
but thanks for playing...
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23DTCIpYoEHA.3556@.TK2MSFTNGP10.phx.gbl...
> For RS 2000 please read this MSDN article:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
> You may also want to download these samples:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> RS 2005 Beta 2 contains a graphical MDX query designer which will generate
> the MDX for you.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Steve" <anonymous@.discussions.microsoft.com> wrote in message
> news:43cf01c4a133$15c93b50$a501280a@.phx.gbl...
> > Hi,
> > I have a question. Can we use Reporting Services to
> > report from OLAP Cubes. If yes, can we write MDX also to
> > report from Cubes using Reporting Services.
> >
> > Thanks
> >
> > Steve
>