Dynamics 365 (CRM) Unknown Report Version: 9.0 problem

Just a quick post on a nasty problem that caused me some grief, and my solution to fix it.
I am using Visual Studio 2017 with the recently released Dynamics 365 Report Authoring Extension. I wanted to customize the Dynamics 365 (aka CRM Online) Invoice report, so I went to the reports and downloaded the Invoice and Invoice Sub-Report RDL files. I knew that these reports would have SQL queries so I quickly set up a VS 2017 BIDS report project, and put the RDL files in there. I spent some time converting the SQL to FetchXML, and when I tried to save it, got the error message "Unknown Report Version: 9.0" and could not save the project.

I read several blogs that suggested manually tweaking the RDL source code and tried a few of those but it just caused more problems. I read that someone had a similar problem with RDLC files and they used VS 2015 to open and then save the original files before opening them in VS 2017, so I tried going back to one of my old VS 2012 environments where I had BIDS installed, opened and saved the RDL files in there, and them moved them to my 2017 development environment, and I now I can make my changes and save the work.

Please note, if this is your first time creating D365 reports with VS  2017, be aware that the RDL file you start with is not what you will publish to CRM. You must build your report project, then navigate to the Bin folder under your project and get the RDL files from there to publish in D365. The reason is that the report Project allows you to target different versions of SQL server.

MS CRM Autofilter gotcha

I am building a report using SSRS that I intend to use as an auto-filtered report inside of MS CRM (see http://blogs.msdn.com/b/crm/archive/2009/03/06/microsoft-dynamics-crm-pre-filtering-tips.aspx). The report pulls data from several views. Everything seemed to be working fine in development until I imported the report into CRM, then parts of the query seemed to stop working.

If you read the end of that article, it points out that all the entities referenced in the FROM or JOIN clauses will have a default filter applied to them that will limit what is included in the query to only records that were modified in the last 30 days.

In my case, I was using a parent entity with 4 outer joins, and I was only retrieving some of the data because some of the related tables had data that was not modified recently.

My solution to this problem was to create a single report that uses only one view with the CRMAF alias, and it used a sub-report that did not have the CRMAF alias on the views. This permitted me to associate the parent report with the entity, and pass the ID’s to the sub-report where there was no filtering.

In my case, I was creating a custom invoice for a custom invoice entity, so I created my parent report with just the one filtered view and gave it an alias of CRMAF_custominvoice. I embedded the sub-report in a table (tablix for VS 2008) control detail band, and gave the sub-report the ID value to pass as a parameter. In the sub-report, I had the rich and complex SQL that I wanted using filtered views, but not using the CRMAF alias.

SSRS Report Design Tips and Tricks

I found a neat document from MS that details some very usefull tips. The contents are:

Best Practices - which includes sections on performance, rendering, page size, logs, parameter passing, and how to switch from a 2005 version back to a SSRS 2000 version.

Report Samples - sections on using custom code for aggregations, passing images into the report, data from multiple sources, changing the report at runtime, resetting page numbers (usefull for muti-page invoices and such), using matrix controls to make data run across the page instead of down, and creating 'Green bar' reports.

You can download the document here:
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ReportDesignTips.doc

Formatting number fields

I have been setting up many reports for a MS CRM system using SSRS and there are a multitude of places where numeric and dollar amounts are being printed. I could just use one of the standard formatting codes provided by SSRS, but then if the customer changes their settings in CRM, it will not be reflected in the custom reports.

I realized today that there is a database function (found under the Programmability/Functions/Table-valued Functions) available to me in CRM called fn_GetFormatStrings() that returns most of the number formats I would need. The function returns a single record (you query it in SSRS like it was a table) and each field contains the number formatting string. Each field is named something appropriate, such as "NumberFormat_2_Precision" and the string looks like this: "###,###,###,##0.00;-###,###,###,##0.00;0.00". So to make use of that, we have created a query in the RDL called DSNumberFormat that pulls that one record and then in the Format for that textbox, we make a reference to that using the expression
=First(Fields!NumberFormat_2_Precision,"DSNumberFormat")