SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: Can I create reporting Services report based on MDX that lookups data from relational database?

A: This could be done by embedding code inside Reporting Services report. Mogen Nielsen has excellent blog entry on how to do this: "Deploying Embedded Code in Reporting Services". (SSAS-Info.com note - It appears that blog post was removed, so we found and made a copy of that content bellow). Original link was: http://frustrated-developer.blogspot.com/2007/06/deploying-embedded-code-in-reporting.html

Yesterday I was setting up some reports in SQL Server 2005 at a client. I was to make a tabular report based on an MDX-statement. To get all the information intended I had to make lookups in the database to e.g. get the name of the customer and things like that.

The solution was to embed some code in the report, which made a query to the sql server database containing a Data Warehouse and then returning the result to the actual row.

You can embed code in your reports by viewing the properties of the report (right click the report surface and select properties). The resulting window contains a tab called Code.


If I then had to use my function DoCracyStuff(), I would do this by using an expession in the report looking like this: =Code.DoCracyStuff("param1")

In my scenario yesterday I had to use a sql connection and issuing a query towards the sql server. That was easy done using ADO.NET and System.Data.SqlClient.SqlConnection and SqlCommand. The only thing to do was adding a reference to System.Data at the References-tab.
Everything worked as expected. I got the details that I wanted. At least when using the preview function of the report designer.

I continued doing some more reports and when the day had come to its end and I was almost headed for the weekend, I thought it was time to deploy the reports to the reporting server. Drama! At every place in which I used my embedded code the resulting text was #ERROR. Too bad!
Googling a bit gave me more a less the answer.. Reporting Services is using the Code Access Security features of the .NET runtime, which retrospectively makes perfectly good sense. Reporting Services gives the user the opportunity to deploy his or her own reports, which gives everyone the opportunity to write code in the custom code tab. This could lead to a serious vulnerability. However by using Code Access Security Reporting Services managed to only allow the most simple operations.
Returning to my scenario, this is really a pain in the ass... I had to change the Code Access Security policy - the reports are located internally at the company and everyone has access to the database server, so giving them the opportunity to execute ADO.NET statements against the SQL Server won't cause any trouble.

So how do you change RS to allow queries to SQL Server?
You have to change the policy file of the reporting server, which you find here (by default installation):
%ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer
the file to edit is: rssrvpolicy.config.

In this config you have to make a new Named Permission Set. So at the Named Permission Set Node insert the following:

The task is to change the PermissionSetName from Execution to RSDataAccess.
After this your reports will be able to make calls to ado.net.

Someone will sure note that I could do the same by using custom assemblies, and only grant the necessary code access security rights for this assembly only, but I find this solution easier to manage right away, when the only permissions needed are those for SQL Server.