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!

15 comments:

Anonymous said...

Hi Ronald,

Thank you so much for sharing this information to us. This is really beneficial. Lot of thanks again!

Ronald Lemmen said...

You're welcome imee. Good luck with the reports!

Anonymous said...

Hi Ronald,

I would like to confirm where should I install the MS SQL Server 2000 ReportPack for MS CRM 1.2. Can I install it in my single server deployment using Small Business Server 2003? Or is it required to install it in a different machine?
Furthermore, with regards to customizing MS CRM 1.2 using SQL Server 2000 Reporting Services, what is/are the supported edtion(s)? Is it either Reporting Services Developer Edition or Reporting Services Standard Edition?

I have raised this question since I have tried to install the abovementioned ReportPack for MS CRM1.2 in my current CRM 1.2 deployment in SBS 2003 and I have encountered the following error "Object reference not set to an instance of an object". In this set up there's no Visual Studio 2003 installed. I was not able to install it since indicated in the reference document that it is optional.

Hope to have your feedback soon. Thanks in advance Ronald!

Cheers,
Imee

Ronald Lemmen said...

Hi Imee,

You should be able to install the report pack on a SBS implementation, even without VS2003. Unfortunately I do not have SBS to test it for you. There are some other people in the ms newsgroup(http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.crm&lang=en&cr=US) who can answer this question.

About which version to install: You should install the same version of Reporting Services as you do have from SQL. That means that you would probably have to install the Standard Edition.

Good luck,
Ronald

Anonymous said...

Lot of thanks Ronald! :)

Anonymous said...

hi ronald,

information provided by you is very helpful. but i have diffrent requirement . i want to add new report which i have created with the help of sql reporting service to ms crm 3.0. i want to hear from you on this. if you can explain it step by step then i would really appreciate. thaks . waiting for your reply.

Ronald Lemmen said...

Hi dhwanit,

I wanted to write a blog article about this, but due to our NDA I cannot yet do this.

I can tell you, because reporting services is the default reporting engine within v3.0, that the adding reports procedure is the same as within any other reporting services implementation. The helpfiles of Reporting Services or other websites can help you good with this.

Kind regards,
Ronald

Anonymous said...

Hi Ronald,
Thanks for replying , I got a solution of adding reporting service reports into CRM 3.0. I have one another problem now . I want to create my own UI to get input parameters for my report in CRM then how should i do that? I also want to know that if i am making a matrix report and i want to set row field and column field dynamically then how should i do that ? I will appreaciate if you answer my above two questions quickly.

Regards,
dhwanit shah

Ronald Lemmen said...

Hi dhwanit,

It will be a bit difficult to get your own UI in between there. If you find a way to open your own page from CRM, then you can let the user fill in the required fields there and open the report yourself. When opening the reports with attributes like "?userid={1234-456-485-546}&searchValue=Ronald", then SRS will not ask for these and will show the report directly. You can then manage your own layout.

As far as I know, it is not possible to create a dynamic widths. I do still calculate myself what the widths should be and set this static.

Anonymous said...

fHi Ronald
you have very excellent Blog
but i want to know what is the main difference between reporting in SQL and CrystalReport Obviously
thanks for you
byeee

Anonymous said...

hi ronald
i want to say to you you have excellent blog
but i want to know what the main defferent between reporting in SQL and crystal Report
thanks for you
byeeeeeeeeeee

Anonymous said...

hi ronald
i want to say to you you have excellent blog
but i want to know what the main defferent between reporting in SQL and crystal Report
thanks for you
byeeeeeeeeeee

Anonymous said...

Some suggestions on Column width:

1. You can make the width expression relative to the max value length for that column.

2. Usually what I do for abnormally long Account Names (for instance) is take the first 30 characters if the length is greater than 30 and follow up with "...", like so:

=Iif(Len(AccountName) > 30,Left(AccountName,30) & "...",AccountName)

Unknown said...

Ronald,

you wrote in your blog: "Then the real fun starts. You encounter issues. Here are some catches to think about:
- Creating a prefilter. By adding an alias to a table named CRMAF_Filtered[entityname], you can use prefiltering. A select query like "SELECT name FROM FilteredAccount AS CRMAF_FilteredAccount" is enough. This works both for reports built in VS2003 as well as VS2005.
If this doesnt work, then check that the DataProvider isn't set to OLEDB or something else. This needs to be SQL.
"

that is exactly what I have. However no filter button available.

What could be the problem?

OptiSol said...

Your blog is good source for SQL server reporting services information. It’s a good blog with lots of information. Keep the good work on.