Thursday, August 25, 2011

CRM 4.0 - supported way to use stored procedures in reports

It is written in SDK that it is possible to use Stored Procedures in reports and following article will describe how to do it.


The first thing I should warn you about that it is totally unsupported to create own stored procedures in the database of CRM organization. So make it supported you have to create separated database for your stored procedures.

Creation of database for SPs - open SQL Management Studio and follow next steps:



Now DB is created and you can create SP inside it:



Query is the simplest just to show the idea. Now I will design report:




This screenshot shows how to call external stored procedure.




The result of work of current report.

Next part will describe how to make this report context sensitive - as a parameter XML variable should be passed. Stored procedure will look like:


Design of the report:



And last thing before upload of the report to CRM is to give Network Service account rights to work with our SPs database:






And the last thing is to upload this report to CRM and test it:






As you can see prefiltering works fine for this case.