#MSDynCRM #CRM2013 Using FetchXML versus SQL script for writing Microsoft Dynamics CRM SSRS custom reports

This might sound a known fact but you will be surprised that quite a few people don’t know why and when do they need to use FetchXML for writing an SQL Server Reporting Services report as opposed to writing common SQL Server script.

Here is how Microsoft Defines FetchXML:

FetchXML is a proprietary query language that is used in Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online. It’s based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expressions. In addition it is used as a serialized form of query, used to save a query as a user-owned saved view in the userquery entity and as an organization-owned saved view in the savedquery entity.

A FetchXML query can be executed by using the IOrganizationService.RetrieveMultiple method. You can convert a FetchXML query to a query expression with the FetchXmlToQueryExpressionRequest message.
For information about how to use LINQPad to work with FetchXML, see this blog post: Getting FetchXML from LINQPad: http://blogs.msdn.com/b/devkeydet/archive/2012/04/22/getting-fetchxml-from-linqpad.aspx

Now to answer the main question of this post: When should I use FetchXML for reports?

As a fact, you cannot write SSRS reports with direct SQL script on Microsoft Dynamics CRM Online. Hence, if you want to write an SSRS report for Dynamics CRM Online, you have to write your query in FetchXML.

Within your SSRS report, you will need to create a dataset that returns the data you require in your report and then everything else will be the same as per any other SSRS report.

Please note however that you do require Microsoft Dynamics CRM Report Authoring Extension installed to be able to write FetchXML queries and to connect to your Dynamics CRM Online organisation.

Additionally, at the time of this post, you can only write Custom SSRS reports for Dynamics CRM using Visual Studio 2010 with SQL Server Data Tools SSDT OR Business Intelligence Development Studio (SQL 2008 and VS 2008 with BI)

You cannot write SSRS reports in Visual Studio 2012, 2013 at this point of time. SSDT for Visual 2013 is not currently available due to some issues.

Finally, here are some sample FetchXML queries:

Total number of Leads (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 </entity> 
</fetch>
Total number of Opportunities (Count - Aggregate):
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='name' alias='opportunity_count' aggregate='count'/> 
 </entity> 
</fetch>
Number of leads by Category:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='lead'> 
 <attribute name='fullname' alias='lead_count' aggregate='count'/> 
 <filter type="and">
 <condition attribute="crm_leadtype" operator="eq" value="100000001" />
 </filter>
 </entity>
</fetch>
<!-- where crm_leadtype is an option set (category) and its value is integer-->
The "Not Equal" filter condition example:
<condition attribute="crm_leadtype" operator="neq" value="100000001" />
FetchXML Average aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg' /> 
 </entity> 
</fetch>
FetchXML Maximum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max' /> 
 </entity> 
</fetch>
FetchXML Sum Aggregate:
<fetch distinct='false' mapping='logical' aggregate='true'> 
 <entity name='opportunity'> 
 <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum' /> 
 </entity> 
</fetch

More information and samples can be found here:
http://msdn.microsoft.com/en-us/library/gg309565(v=crm.6).aspx

Hope this helps!

Microsoft Dynamics CRM 2011 Reporting – Book Review

Packt Publishing has recently asked me to review the “Microsoft Dynamics CRM 2011 Reporting” book. I have just finished reviewing it and I found the book to be an excellent source for understanding reporting in Microsoft Dynamics CRM. It covers both the core reporting capabilities as well as the advanced dynamics CRM reporting features.

The book provides clear and valuable information about the basics of reporting, SQL Server, Dynamics CRM Entity Relationships and Database Basics. The book then delves into the Microsoft Dynamics CRM reporting capabilities starting with the simple Dynamics CRM report wizard to create Dynamics CRM reports and then focusing on the more advanced features. It introduces the SQL Report Builder and creating contextual and inline Dynamics CRM reports. It then briefly covers the use of reports and charts in Dynamics CRM dashboards. Finally the book discusses custom reporting and automation and the mobile client.

Overall the book is easy to read and has simple approach to explain complex reporting features in Dynamics CRM. I strongly recommend this book for Dynamics CRM reporting developers and other CRM consultants who want to understand more about the types of reports and capabilities Microsoft Dynamics CRM has.

SQL Server Reporting Services Error cannot decrypt the symmetric encryption key & website declined to show this web page

One of our “big and important” clients has approached us with an issue with their system. The problem is that reports are not accessible from the .NET application and CRM that we have built for them. If you try to open reports from these, you get the error in Internet Explorer saying “Website declined to show this webpage. The website requires you to login”. We were getting this error with everything related to reporting services. So when logged on the reports server machine, those urls:  http://localhost/reports , http://localhost/reportserver , http://localhost/reportsmanager all come back with this error or with the error 403 forbidden “Error 403 Forbidden access”.

To rectify this issue, I went through all the usual possible routes such as authentication on IIS, reports server and reports website on IIS. Made sure that all the application and system users have the right access to the reports server. Checked the application pool for the reports server, all these looked find with nothing that has been unchanged. I have also tried to check the reports server configuration manager. Went through all the usual steps to check that it is running and configured properly and again these were all fine with the nice green tick next to each step of the reports server configuration manager. I have also checked services running on the server and the server even viewer. All windows services and SQL reporting server services were running correctly and can be restarted with no issue.

 Then, a colleague of mine, tried to open reports from the management studio, and that was it. The error we got in the management studio was much more descriptive and helpful. The SSRS reports server management studio error said:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Report server disabled”

The problem here is because that the Reporting Services encryption key has been invalidated, in other words corrupted. This encryption key is stopping reporting services from returning any reports or even displaying the reports manager, etc..

To fix this issue, and after some research, there were two solutions to solve this issue (again solutions found by my colleague, not me!):

Solution1: In SSRS 2005, there is the encryption section on the left hand side.  We need to go to this section and restore the encryption key using a backup encryption key. The encryption key should probably (and hopefully) have been backed up during the setup of the Reporting Services. This encryption key should be regularly backed up and stored in a safe location along with the encryption key password. If you got this backup encryption key, and to solve this error, we need to restore it from the Reporting Services Configuration Manager, encryption section.

If you you don’t have backup of it, then you will have to go for the other longer and more annoying option which is:

Solution2You have to delete the encrypted content. This will remove the connection strings from all the reporting services data sources. Hence, in this case and after removing the encryption and generating a new one, you need to reset the data sources in Report Manager. Datasources could be shared (linked) or could be a separate data source for each report. You will need and update all these data sources after you removed the encryption.

 

Luckily! Our good client had the backup of the encryption key, so we only needed to do solution one (Thank God for that!). Well, to be fair, they searched on the server and found a file with extension ” *.snk “ on their server and it was the encryption key backup.

All SSRS encryption keys have file extension .snk.

That’s it.. problem solved. Thanks again to my colleagues work on these solutions.

 

Error with Report Server

We have been recently approached by a client who had a problem with their report server. If you try to access reports from the reports manager, reports run fine and you can view any report fully and in the right format. The problem arrises when a .NET application tries to open these reports, it just opens a new internet explorer window and uses a url to show the report. This was working fine until the client has probably made some changes to their report server. Since then, reports are not displaying in Internet explorer.

To cut a long story short, we eliminated all authentication issues by checking that security and access to all reports is properly configured and the right users has the right to run these reports. We also eliminated problems between SQL Server and the report server, as reports can run smoothly from the reports manager.

We finally came with the concolusion that the error must be related to Internet Explorer not able to render the report properly. We looked through IIS but could not find anything that looks wrong or not the way it should. I have then checked the security setting of Internet explorer (Tool –> Internet Options –> Advanced) and this all seemed fine but I ticked the box that says display all scripting errors. We also did something which I am not sure if it made any difference, which is that we added the server to the trusted sites in Internet Explorer. Then when we tried to open the report again, this time we started to get scripting errors (although they were not showing in the status bar before for some reason). Scripting error dialogs start to show and we finally started to understand what is happening. We got errors such as: “RS Controller is undefined”, “RSControl object reference not set to an object”. We quickly googled this error and we found an easy and simple solution for this (can’t find the original solution at the moment). The solution is to go to the report server itself, open IIS, right click on your reports server (usually under default website), go to directory –>configuration –> search for axd and double click to open its properties. On there uncheck the filter option (can’t remember the option on top of my head and I don’t have access to any IIS 6.0 at the moment to get the exact option wording). Finally click ok.

This has simply solved the problem, and we managed to get all reports back working from the browser and the .NET applicaiton now opens these reports externally with no issues at all.

I have created this post few days after the resolution, and I don’t have access to the client’s servers at the moment so if you have a similar problem and the post doesn’t answer your questions, please feel free to comment here and I promise I will get back to you as soon as I can with more details and may be a screenshot!

All the best.