Creating reports in CRM 2011 using Microsoft SQL Server Reporting Services (SSRS)

The reports in CRM 2011 were built using Microsoft SQL Server Reporting Services (SSRS).   If you are familiar with SSRS, you can build custom reports or you can highly modify the reports that come with CRM.

Here’s a few key things to know:

1.   If you are using CRM 2011 on-line, the process of connecting to the database is different than if you are using the on-premise version.

With CRM 2011 on-line, you can’t just connect to the database.   You have to use something called a “fetch-based” connection.   See this Microsoft guidance:   http://msdn.microsoft.com/en-us/library/gg328097

2.   You can create custom CRM reports in either Business Intelligence Development Studio or Report Builder.   Either tool works.   Once you have your RDL file built, you upload it to CRM using the CRM interface.  In CRM, go to Reports and click New.   For “report type”, change the dropdown box to “existing file”.   Then point to your RDL file.

3.   Microsoft wants you to build reports on a series of special database views called “filtered views”

You don’t have to.   You can still build custom reports on the original CRM database tables.   But see this Microsoft article for a discussion of filtered views:  http://msdn.microsoft.com/en-us/library/gg328467.aspx

4.  If you want your reports to automatically filter based on the CRM record you are in, there is a trick.   For example, if you have a quote open in CRM, and you want to print a custom report just for that quote, there is a trick to building the report.

CRM has a feature called “filtered views”.

See this Microsoft article:   http://msdn.microsoft.com/en-us/library/gg328288.aspx

So, in the example of building a report on a quote, when you build the report, your sql statement should say:

SELECT
*
FROM
FilteredQuote as CRMAF_FilteredQuote

When you upload your RDL file, you must alter the properties of the report to have it show up in the quote form.  In the “categorization” section of the report properties, under “display in”, make sure you add “Forms for related record types”.

CRM will then automatically filter this custom report based on the quote that you are in.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: