Dimension as Parameter in Reporting Services

Hallo everyone:

I am using reporting services and I am looking for a way to define a parameter “dimension”.

The problem is that in Reporting services are available just the fields resulting from the mdx statement.

Using the following statement I can use the fields “value” and “Customer”

Select {[Measures].[value]} on 0, {[Customer].allmembers} on 1 From [Cube1]

I could solve it like this

Select {[Measures].[value]} on 0,

Iif(@.DimParameter = “Customer”,

{[Customer].allmembers} [Cube1],

{[Products].allmembers})

on 1 From

but the fields available would be “value” and “Customer” if the standard value of @.DimParameter is “Customer”

I also tried to create a set but I got the same results.

Is there a function in mdx that works like sql to define the names of the retrieved fields?

Select Customer as myDim Fom table1

Thanks

I tried to do that a while ago, and ended up creating a sproc in SQL Server that connects to Analysis Services using OPENQUERY.

Something like this:

create proc Example

@.dimension varchar(1000)

as

declare @.sql as varchar(8000)

declare @.mdx as varchar(2300)

set @.mdx = 'select {[Measures].[Unit Sales]} on columns, {##rows##} on rows from Sales'

set @.mdx = replace(@.mdx, '##rows##', @.dimension)

set @.sql ='SELECT * FROM OpenRowset(''MSOLAP.3'',''DATASOURCE=localhost; Initial Catalog=Foodmart 2000;'',

''' + @.mdx + ''')'

print @.sql

exec (@.sql)

If you're using SQL Server 2005 you'll need sp1 for this to work.

Hope this helps,

Santi

|||

Many, Many Thanks!

I tried you’re example but I could not referred to the fields in the select statement.

Instead of using selec * I would like to do something like: select [Customer].[Name] as myDim.

I couldn’t achieved it. So I am creating a temporal table, inserting the results from the select statement and selecting the temporal table:

CREATE TABLE #t(Dimension varchar(100), Measure varchar(100))
INSERT INTO #t
exec(@.sql)
select * from #t
drop table #t

It works.

How did you manage it? Is there an easier way ?

|||I forgot to mention it, I also used a temporal table. Don′t know if there is an easier way, but since this works I'm happy with it.|||

Hi

How do I have to configure the users security. In such a way that, they can access the report that uses the stored procedure with the openset function?

|||

That could be a problem. In my case I filter users out with Reporting Services security, and then grant execute permissions to the stored procedure to Reporting Services.

|||

How did you configure your users in the Data Base and in the Analysis Services. Because when I try the report i works fine. But when someone else try it, they cannot connect to AS. I have other reports that access AS and everyone can access them with any problem.

|||

I have a Windows Group with the users. This group is added to the pertinent role in Analysis Services, and to the role in Reporting Services.

However, I have the RS Data Origin configured using Sql Server Security instead of Windows Security, saving the credentials in the Report server. Of course the Sql Server login must have the relevant permissions to execute the Stored Proc, and to create temporal tables.

Bottom line:

One group with the users, added to the roles in AS and RS|||I dont have idea what is wrong. However Thanks!|||

It seems you need Administrator rights to perform Openrowset operations

see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417389&SiteID=1

sql