Can I create Reporting Services report based on MDX that lookups data from relational database
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:
<NamedPermissionSets>
....
<PermissionSet class="NamedPermissionSet" version="1" Name="RSDataAccess" >
<IPermission class="SecurityPermission" version="1" Flags="Execution" />
<IPermission class="SqlClientPermission" version="1" Unrestricted="true" />
</PermissionSet>
....
</NamedPermissionSets>
The new permission set includes the SqlClientPermission, which allow Reporting Services to run ADO.NET code.
The unrestricted="true" attribute is for allowing all calls, but you could refine this to lock down the user a bit more. Look at the MSDN docs here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlclientpermissionattribute_properties.aspx
So now you have a permission set. Next step is to apply this to the policy for embedded code.
This is the first code group below the xml statements above.
It should look like this:
<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="RSDataAccess" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. ">
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.