|Can I create Reporting Services report based on MDX that lookups data from relational database|
|Written by Mogen Nielsen|
|Monday, 02 July 2007 18:48|
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.
<PermissionSet class="NamedPermissionSet" version="1" Name="RSDataAccess" >
<IPermission class="SecurityPermission" version="1" Flags="Execution" />
<IPermission class="SqlClientPermission" version="1" Unrestricted="true" />
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.
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.
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?