Most users who run our out-of-the-box reports realize at some point that these generic reports aren’t always a perfect fit for their business needs. The task of developing reports which are useful by all Microsoft Dynamic CRM users is nearly impossible, so we have to make some assumptions to get a “best fit” result.
With that in mind, we have designed the CRM platform to be very customizable using a rich extensibility infrastructure. Reports can be customized and extended by customers in order to better fit their business needs. However, editing our reports inside the Business Intelligence Development Studio (a SQL Server Reporting Services add-on to Visual Studio) can be challenging. These issues are easy to work around, but they are a little off-putting. So in this post, I want to discuss some of these issues and the simple workarounds you can do in order to start editing the generic reports.
Discussing the details of how to create a Report project in Visual Studio and how to add existing reports to that project is out of the scope of this post. For that type of information, I would recommend the Designing and Creating Reports section of the SQL Books Online.
Data source preparation
Once you have one of our reports loaded into Visual Studio, one of the first things you’re going to try to do is run that report to test to make sure that everything has loaded correctly. When you do that you’re are most likely going to see this error:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot create a connection to data source ‘CRM’.
Cannot open database “Adventure_Works_Cycle_MSCRM” requested by the login. The login failed.
Login failed for user ‘DOMAIN\user’.
Because we can’t possibly know every customer’s organization database name, we have hard-coded in some constant value into our report definitions. When the report is published to CRM, we update the data source definition to point to the right database, but there’s no way to work around the constant value inside the actual RDL file. So to fix this, follow these steps:
1. Click the Data tab on the report. Once you do this, you’re going to get another error about the Adventure_Works_Cycle_MSCRM database – this can be safely ignored for now.
2. Click the “…” button next to the Dataset drop-down. The drop-down might contain different values depending on which report you are opening – this is fine.
3. In the Dataset dialog, click the “…” button next to the Data source drop-down.
4. Edit the connection string to point to your organization database. The dialog launched when you click the “Edit…” button will help you edit this string if you are unfamiliar with connection string syntax.
5. If you are editing a single logical report (ex: Account Distribution), which might have multiple constituent physical reports (Account Distribution and Account Distribution Detail), you’ll need to repeat these steps for every physical report in the report family.
Once you’ve completed these steps, you should be able to switch back to the Preview tab on the report and run the report.
Running data sets
While every report we provide out of the box has multiple data sets, you’re really only likely to do anything with one or two of them: the scary ones that build up dynamic SQL statements. They’ll look something like this:
I’m going to assume that you have enough knowledge of SQL to machete your way through this jungle of code, but you’re probably going to want to try to run the code after you’ve made your changes. Your SQL instincts would tell you that clicking the red exclamation icon in the toolbar will run the code, and for the most part they will be right. Except in this case, you’re going to get an “it’s Greek to me”-style dialog prompting for something:
All of the major SQL statements in our reports require query parameters. These parameters don’t have any concept of default values, so you actually need to specify a value before Visual Studio can run the report. The good news is that for the most part, these query parameters are named after report parameters, and you can just steal default or other possible values from the report parameter definition. Once you answer all of the troll’s questions, he’ll let you cross the bridge.
Missing data set fields
Sometimes you can be editing a report and all of a sudden you get an error like this:
An error occurred during the local report processing.
The definition of the report ‘/Report name’ is invalid.
The Value expression for the textbox ‘txtWhatever’ refers to the field ‘somefield’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Most of the cases where I’ve seen the “Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope” error, the problem has been due to missing data fields:
For some reason, Visual Studio occasionally takes it upon itself to delete all the fields associated with a data set. The cause isn’t entirely clear, but the fix is: just follow the steps outlined above in the “Running data sets” section above, except you’ll want to click on the Refresh Fields button (two green arrows on a page) instead of the Run button.
Report Wizard reports
In addition to the reports we provide out of the box, it’s also possible to run into the above issues with reports generated by the CRM 4.0 Report Wizard. We specifically designed the Report Wizard feature to generate reports that provide value at creation time, but are also easy to customize in order to exactly suit their business needs. So feel free to customize any of our reports, and don’t let a couple of speed bumps like the ones discussed above stand in your way.
Cheers,