Deploying Embedded Code in Reporting Services

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.

Exploring Katmai part 3 (Database Engine)

Some new features of the relational database engine has come through to the CTP. Some of them looks very exciting. I actually think that some of them will make my life even better.

Merge (wuhuu)
Merge is a new statement like insert, update and delete. It works as a combination of the three, that is you will by using merge be able to merge two tables issuing updates when columns in the source table have changed, inserts when new rows have arrived at the source and deletes when rows have been deleted at the source.


As an example of this look at the following transact-sql statement:

MERGE Production.Product AS prod1
USING (SELECT [ProductID], [Name], ProductNumber, Color, StandardCost FROM Production.Product2) AS src
([ProductID],[Name], ProductNumber, Color, StandardCost)
ON (prod1.ProductID = src.ProductID)
WHEN MATCHED AND
(prod1.[Name] <> src.[Name] OR
prod1.[ProductNumber] <> src.[ProductNumber] OR
prod1.[Color] <> src.[Color] OR
prod1.[StandardCost] <> src.[StandardCost]) THEN
UPDATE SET prod1.Name = src.Name,
prod1.ProductNumber = src.ProductNumber,
prod1.Color = src.Color,
prod1.StandardCost = src.StandardCost
WHEN SOURCE NOT MATCHED THEN
DELETE;

The statement looks for updates to the table Products2. When the table has changes to Name, ProductNumber, Color or Standard Cost then an update statement is issued.
When rows in destination is not at source then this statement will delete the rows.
When rows in source is not at the destination you will be able to issue Insert statements (not in the example, though) by using WHEN NOT MATCHED THEN.

This really is a nice addition to t-sql. I have done a bunch of this type of code for the last couple of years. Now I really look forward for the new version of SQL Server. :-)

Table-valued parameters
This is a functionality which lots of sql-developers have been looking forward to.
In stored procedures it hasn't until now been easy to transfer table-valued data, for instance a list of words for doing search.

Some solved this by using Xml or by comma-separating values in a varchar parameter and then parsing the xml or comma-separated list in the stored procedure, while others did pass the values into a global temporary table and retrieved the values from there.

Some did just pass a lot of parameters, that is @search1, @search2, @search3 and so on, which had the ability to contain null values and then you would check if the value was null for each of the parameters. This approach also has the problem with the fixed count of parameters.

These approches are no longer necessary as a new parameter type is introduced namely the table-valued parameter type, where you define the structure and then have the ability to pass data in that structure to your procedures.


The table type is defined in this way:
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50), CostRate INT );
GO

And can then be used in the stored procedure or UDF's like this:

CREATE PROC TestProc
@tableValuedParam AS LocationTableType
AS
...

Administrative policies

At the management side of SQL Server another new feature has been added. That is the ability to add policies at the administrative side. Hmm, that means more power to DB admins. The question is if that's a feature we like..

The policy editor looks like this:


It gives the admin the opportunity to set rules like stored procedure names must begin with "prod_" and table names can't be no longer than 20 chars and so on.

Different database options can be enforced by the admins, too. That's for instance wheter to allow quoted identifiers but also setting default File Location and File Group.

+= operator in t-sql
One of the enhancements I've seen is the += operator. That is the C-construct to add a number to a variable.
The following statement illustrates this feature:

DECLARE @number AS INT
SET @number = 1
SELECT @number --Result: 1
SET @number += 1
SELECT @number --Result: 2

Not a big change, but a change getting the t-sql language closer to the newer generation of programming languages like C#, C++ and Visual Basic.NET

Exploring Katmai part 2 (SSAS)

First up in my explorations is new features of Analysis Services in Katmai..

Attribute Relationships

A new feature when designing dimensions is a new tab "Attribute relationships", which visualizes the relationships between attributes. That is, you will be able to see the hierarchies in a visual manner and how they interact with each other.

The example below is a visualization of the attribute relationships of the product dimension.

From within the Attribute Relationships it is possible to create new relationships. A new dialog will popup, which looks like this:

New Key Columns Dialog Box
When designing the keys of the dimensions a new dialog box is available - nothing much really. It looks like this

Another new feature when talking dimensional key design is, that it is now possible to change the key in the properties window without opening up any windows/wizards..

Backup and restore
Another thing is that backup and restore has undergone some structural changes which should give a performance gain, however I haven't tested this...

So what's the verdict?
So for the matter of Analysis Services nothing much has come through yet. I look forward to seeing other improvements of Analysis Services in future community previews.

Exploring Katmai part 1

Last week I downloaded the newest CTP from Microsoft in order to explore new features of the upcoming version of SQL Server. This version has got the name SQL Server Katmai June 2007 CTP.

As a new "feature" the samples are no longer installed with the installation of SQL Server as was the fact with all previous versions. The sample databases are now a project at CodePlex. Actually I find that positive as the sample databases will then evolve as time goes by and bugs will be fixed faster I believe.
The sample databases can be found here:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4605
Afterwards you can get the samples (SSAS projects, SSRS projects and a lot of other samples) here:
http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4603

My first impression is that everything looks as SQL Server 2005, which is actually fine. No need to implement a new GUI as the GUI of SQL Server 2005 works just fine.

So now I plan to find out what has actually changed under the hood at least with regards to the BI platform.
Stay tuned for more info...