Custom SSRS Reports and CRM Online 2011

October 26, 2010 by · Leave a Comment 

One of the main limitations of CRM Online has traditionally been the lack of custom reporting capabilities. While CRM on premises users have always been able to do custom SSRS reports, online users have been limited to just using the report wizard, or having to use some complex data replication schemes to extract CRM data to local reporting databases.

CRM 2011 changes everything. While still not giving direct back end SQL access to the database for security reasons, CRM 2011 introduces a plugin for Business Intelligence Development Studio (BIDS) 2008. This BIDS extension enables creation of SSRS reports using fetch XML queries as report data sources. This means that SSRS reports can be written that work with both CRM Online and On Premise, and it removes one of the limitations that forced companies that would otherwise be good fits for CRM online to have to deploy on premises.

Abhijit Gore's has a detailed overview on the CRM team blog of how the BIDS extensions work. Read it here: http://blogs.msdn.com/b/crm/archive/2010/10/19/getting-started-with-custom-reports-in-the-cloud.aspx

CRM MVP Frank Lee  from Workopia has another way to create custom reports. View article…

if you want to try the BIDS extensions with the CRM 2011 beta, you can download the bits here: http://download.microsoft.com/download/1/9/1/19136B1A-5C0A-4B17-89E0-AB0ADBC913C7/CRM2011-BIDSExtensions-ENU-i386.exe

Unable to publish a report in Dynamics CRM

September 23, 2010 by · Leave a Comment 

Recently I created a report in Visual Studio but was unable to upload it in MS CRM. Below is a copy of a post by Joel Lindstrom on Customer Effective describing some useful tips on how to identify the possible cause:

Sometimes when writing SSRS reports for CRM, you will find that the report works fine in Visual Studio, but will fail when you upload them to Dynamics CRM.  This can be frustrating, because it can sometimes be difficult to identify the cause of the issue—it works fine when you preview it in Visual Studio, but when you upload it, the upload will fail, and typically the error message created will be very generic—something to the extent of “Call your System Administrator.”

Typically when you see this problem, it has to do with SSRS functionality specific to CRM reports.  Here are the top 3 causes that I see reported by users:

1.  Data source—When you create an SSRS report, you have the choice of using an Embedded data source or a shared data source.  For CRM reports, you must use an embedded data source.  If you use a shared data source, your report will preview in Visual Studio, but you will not be able to upload it.

2.  Hidden parameters—If you use the special hidden CRM parameters, such as the CRM_URL parameter to build dynamic hyperlinks to CRM records, if you don’t set them up properly, the report upload to CRM will fail, but it will still preview OK in Visual Studio.  Make sure that the parameters are set to hidden and allow null values.

3.  Pre-filters—When using CRM Report Pre-Filtering, if you use pre-filtering incorrectly, sometimes the report may fail to upload correctly.  In this case, like the others, the report will preview without error in Visual Studio, but will have issues uploading to CRM.

Troubleshooting

So now that we’ve talked about what some of the common causes for the “works in Visual Studio, but not in CRM” issue, how do you troubleshoot what is causing your report issue?  The best place to start is the event log of the CRM Server.  If you get a “Contact your Administrator” error message, frequently you will see a more detailed error message.  Recently I had a user experiencing this issue, and I checked the event log on the CRM server.  I found the following error message captured in the event log:

The parameter ‘CRM_URL’ has no default. A default is required for all non-nullable parameters without a prompt or the valid values list has to contain Null.

After modifying the SSRS report CRM_URL parameter to accept null values, the report could be successfully uploaded.