Dimension as Parameter in Reporting Services

Dimension as Parameter in Reporting Services

• Tups

  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

  
  
  

• LaurieC

  

  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.

  
  

• jsjensen

  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

  
  

• Ezhilarasan M

  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
  • Sql Server security to connect from RS to Sql Server

  Hope this helps,

  Santi

  
  
  

• rschiefer

  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.
  
  

• uotmiem

  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

  
  

• mcm_ham

  I dont have idea what is wrong. However Thanks!
  

• cactushome

  It seems you need Administrator rights to perform Openrowset operations

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

  
  
  

• Douglas Chew

  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.

  
  
  
• Dimension as Parameter in Reporting Services