MS SQL Reporting Services Reports for MSCRM including basic security

Microsoft CRM is using Crystal Reports for reporting purposes. This works pretty fine as long as you do not want to modify reports or create your own reports. Crystal will then become very slow if you are working with thousands of records in your database. You could tweak Crystal, but instead, you could also take a look at MS SQL Reporting Services (SRS). Microsoft has released a report pack for MSCRM v1.2 which includes 6 reports. This report pack will give you a quick start on working with SRS Reports in MS CRM. But, when examining these reports, you will notice that the security rights which are set within MS CRM are completely gone when requesting these reports! In this posting I will show you how to create basic security for custom SRS reports.

The security in MSCRM is working with Security Descriptors. We cannot use these Security Descriptors in SRS. Therefore we will have to recreate the security settings. These settings are all stored in the database. Depending on how much security is required for your business, you can get the settings. In this example I will show you how to include settings on business unit level. Let's assume that you are working in an international company called Avanade. The headquarters are in the USA and there are several other locations in other countries including The Netherlands and France. In the end of the example, the users which reside in The Netherlands are able to see the Dutch accounts, the France users the France accounts and the headquarters will see all accounts.

For this example I do expect you to install Windows, Visual Studio, MSSQL and Reporting Services on your own. That shouldn't be too hard though :)

As soon as you have Reporting Services installed, we can start creating our report. We will do this in Visual Studio. There should be a new project type: "Business Intelligence Projects". From this project type we will select "Reports Project". Within this project we will chose to add a new report (Solution Explorer, right click on Reports and select "Add New Report"). Visual Studio will ask you to first create a Data Source. Please fill in a nice name, select SQL Server and enter your connection string. You will probably use this Data Source more often, so it would be useful to select the checkbox "Make this a shared Data Source".

Now its time for the real work. Let’s create the query. A user from the Netherlands should only see Dutch accounts. We will achieve this by using the userid. Based on this GUID we can find out in which Business Unit this person resides. If this is the top BU, then we should select all accounts, otherwise we select a filtered set of accounts. The first step is to select every account which resides in the same business unit as the user. This query will get us there.

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU)
ORDER BY Name

As you can see, I am using the view Account instead of the table AccountBase. This view has already the address data included, which saves me a lot of query coding.

As soon as you press enter after entering the query, you will get a lot of layout options. Currently I do not care too much about how the report looks, so let’s choose to finish this report. When viewing the report in the preview window, you should enter a guid of a user which does own some accounts. Just to be sure that you do get some results.

If you enter the userid of a user which resides in the USA Business Unit, then you will only find USA accounts. We can solve this by making sure that this query will also search for accounts which are in child business units. I have created a user defined (recursive) function which accepts a GUID and returns a table with all the child business units. Here is the function:

CREATE FUNCTION dbo.GetChildBU (@currentBU UNIQUEIDENTIFIER)
RETURNS @businessunitsTable TABLE(ID UNIQUEIDENTIFIER) AS
BEGIN
DECLARE @businessunitID UNIQUEIDENTIFIER
DECLARE BU_Cursor CURSOR FOR
SELECT BusinessUnitId FROM BusinessUnitBase WHERE ParentBusinessUnitId = @currentBU

OPEN BU_Cursor

/* Read current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID

WHILE @@FETCH_STATUS = 0
BEGIN
/* Return current */
INSERT INTO @businessunitsTable
VALUES (@businessunitID)

/* Return children */
INSERT INTO @businessunitsTable
SELECT * FROM dbo.GetChildBU(@businessunitID)

/* Read next current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID
END

RETURN
END

With this function in mind, you can modify your initial query to include these Business Units. This will be the result:

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU) OR OwningBusinessUnit IN (select * from GetChildBU (@owningBU))
ORDER BY Name

If you have added the function to the MSCRM database and modified the query for the report, then you should be able to see all the accounts in the users Business Unit and all the child Business Units.

OK, now the reports are fine, but where do we store them in CRM? It would not be logical to store some of the reports under the reports part in the Navigation bar and some modified reports in the Left pane or menu bar. You will probably want them to be in the same screen as the other reports. This you can solve by modifying the map_xml.aspx file in the Reports folder. You can copy and paste one of the other items and you will have a link to your reports. Just put them in a fancy -if possible looking like crm- datagrid and you have extended CRM with your own reports! Please keep in mind that the last modification is unfortunately not supported...

Happy reporting!