June 20, 2013
Force CRM 2011 Fetch Report To Show Subreport Even If There Is No Data
There are two common problems that every CRM report writer worth their salt will run into over time: (1) Why does my report render properly in Visual Studio but yet errors in CRM? and (2) how do I force my subreport to show if there is no data? In on-premise installations, the latter problem is solved easily by the "select 'abc' as dummy" trick described in this post, but the former can be caused by a myriad of reasons. And even with the "select 'abc' as dummy" solution, that doesn't apply to a fetch report. How can one effect the same result without SQL?
Fortunately for you readers I was faced with these problems just yesterday. Toiling in the midnight hour, unable to let the problem go unsolved, and dealing with the unresolved emotional issues stemming from the Boston Bruins recent Stanley Cup Final loss to the Chicago Blackhawks (plus one referee), I had written a Fetch-based report and sub-report combination which did exactly what I wanted it to when testing in Visual Studio (2008 version, for those keeping score at home). For the sake of simplicity, let's just say the report showed Account (Company) information, and the sub-report brought back Contact information. The Account unique identifier was passed to the sub-report and the sub-report would either display the contact information associated with the account, or display a message to the effect of "There are none". (Note: I do not use the NoRowsMessage in SSRS, again for those keeping score at home). I had this working in Visual Studio perfectly, if I do say so myself. However, once I loaded the report into CRM, I found to my dismay that it did not have the same effect.
Before I reveal the solution, I think the approach to the problem is equally as important as the solution itself. The heuristics here boiled down to the acronym "WWVID?" (What Would Vanilla Ice Do, although I could als have gone with "WWCND?"-What Would Cam Neely Do?, or my favorite "WWWWD?"-What Would Wes Welker Do?). But I digress. The answer to my question was "Stop. Collaborate and LIsten." As I mentioned earlier I was toiling in the midnight hour. And I had a feeling, maybe even a suspicion, that one or more of my colleagues would be obsessing about problems of their own. After all, at Customer Effective we are always trying to take it to the next level. So I put my slide rule down, and reached out to my colleagues. Sure enough, though the hour was late, our own MVP Scott Sewell and Sure-To-Be-MVP-At-Some-Point Jerry Martin were both toiling themselves. Their own thoughts, as well as going through the process of writing the problem down, helped to unveil the solution. And like most solutions, it was obvious once it appeared. While the lead paragraph here suggests that I knew it all along, it came down to solving for the empty dataset. Where there were no contacts for that account, the report would not display the sub-report at all.
As the reader has probably gathered, were this a pure SQL problem it would be a slam dunk. The issue became one of how to create a fake dataset using FetchXML? I needed to always bring back a record, and not be forced to filter down from a huge subset of records, lest I get a performance hit. At long last, the solution for you readers: add another dataset to the subreport that looks for the current user from the User entity. This solved the problem, as there will always be one, and only one, user running the report. Once I added this dataset, there was no difference between Visual Studio and CRM.