FetchXML reports do not trigger RetrieveMultiple plugins in CRM 2011

 

It is by going down into the abyss that we recover the treasures of life. Where you stumble, there lies your treasure.
Joseph Campbell

The CRM forums are a great resource for learning interesting solutions and problems in Microsoft Dynamics CRM

I read this interesting gotcha/CRM Quirk this week about a CRM developer who had written a plugin which was triggered on the RetrieveMultiple event on his custom entity.  The user was wondering why his plugin was not being triggered when the user ran a report using FetchXML.

RetrieveMultiple Plugins, What, Why and How?

So you can have plugins which trigger on RetrieveMultiple for an entity.  Some readers are thinking why would you want to do that?

Reasons like

• Translate data held in the CRM database
• Exclude data from RetrieveMultiple queries
• Log RetrieveMultiple requests
• Calculate some fields in the retrieve from other entities
• Decrypting encrypted fields

There are lots of other reasons, but they give you an idea.  The user wouldn’t even know a plugin had run because they would just receive the results as normal.

The retrieve multiple is triggered in CRM when the user

• Runs an advanced find
• User clicks on a view (e.g. active accounts)
• Quick search
• Associated views
• Plugins doing retrieve multiples
• Console apps doing retrieve multiples

 

What about reports and FetchXML

So why don’t reports and FetchXML trigger the RetrieveMultiple plugin

The answer was explained in this forum post answer by CRM legend David Jennaway who is a CRM MVP, when I was starting in CRM development David had a fantastic CRM blog which I followed avidly and still do, but but unfortunately for the CRM community David rarely writes any blog posts these days (2 post in 2014).

Reports do not trigger plugins. SQL reports bypass CRM completely, and Fetch reports also bypass the plugin pipeline

 He also add this answer

Just to clarify the last post, in CRM 2011 fetch queries are submitted via the RetrieveMultiple message, whereas in CRM 4 they were submitted via the Fetch message and (bizarrely) you could handle them in plugins on the Execute message

This logically makes sense because SQL reports definitely would hit the plugin pipeline and I’m guessing FetchXML when run from reports must be run using a slightly different mechanism (I have no knowledge of this)

I’m not sure if this is the same in CRM 2013 but I’m guessing it’s probably going to be the same because I don’t think there have been changes in those areas of functionality.

Advertisement

CRM 2011 – Custom reports not working

I had an unusual error today where the default reports worked but any custom reports just whirred a bit and then produced a blank page.

looking at the event viewer logs on the CRM server I found I was getting MSCRMREporting error saying

Report render failure. Error: An error has occurred during report processing. (rsProcessingAborted)

luckily typing this into the internet produced a few hits and this seems to be a problem quite a few people have experienced.

The solution to the problem was to do with SPN (service principal name) not being set.  I believe the reason for this was because I changed the service account which runs the services for CRM and by changing this it somehow had an effect on the reports and current SPN’s were not validating the reports with this new user.

To fix the problem I had to log onto the Domain controller and run this command line setspn instruction

setspn -a http/CRMSERVERNAME DomainName\crm service account

an example would be, if my crm server was called crm, my domain hosk and the user was called crmservice.

setspn -a http/crm hosk\crmservice

It also took me a while to get this to work and it was only when I typed everything in lower case that the setspn instruction finally worked.

I found the solution above from the two links below, they are worth reading because they explain the solution more thoroughly and the first one has other things to try if the setspn instruction does not resolve the problem

http://dynam1cscrm.wordpress.com/2011/05/17/dynamics-crm-2011-spn-and-windows-authentication-configuration-for-running-custom-reports/

http://blogs.salentica.com/direland/2011/12/28/when-crm-2011-custom-reports-dont-work/

 

Although the link below is a bit more information than you need I did find this quite interesting in explaining about CRM and SPN’s

http://blogs.msdn.com/b/crm/archive/2009/08/06/configuring-service-principal-names.aspx

 

CRM 2011 – Why can’t I add my report to a solution

The quick answer to the question is for me to stop being an idiot.

I had written my report, I had uploaded the report, it was running fine.

I then tried to add the report into the solution, opened my solution –> reports –> adding existing

I then couldn’t find my report!

checked the reports, yep it’s there

did an advanced find, the report appears.

I then edited the report and remembered, this report is a my report, a personal report.

This is the same as creating a personal view, unless you share it everyone then no one else can see.

Reports are slightly different because you can choose an option called Make available to organisation.

To find this option you have to go to the report you created –> Edit –> Actions –> make available to organisation

Then everyone can see it and more importantly you can add to your solution

if you have a change of mind and want the report to be viewed by only you, you can in Actions choose to revert  to personal report.

CRM 2011 – Reporting options in CRM

I had to write a report for a customer this week and unfortunatly I wasn’t able to write the report using the report wizard.

This was very bad news because all other reporting methods take a lot more time.

So what are my options

• Dashboard and charts
• Reporting wizard (although for this report it wasn’t any use)
• Excel Powerpivot
• SRS reports – FetchXML
• SRS reports – SQL

To give you some background one of the reports was a report grouping on date (month, year), another group of accounts and another group of Call Type.  In the report they wanted to show the number of resolved calls and the percentage of each call type.

Dashboards and charts were not really used for this because this was a report rather than a visual graph.  I am a big fan of charts and dashboards but one aspect that makes them a harder sell is the dashboards and charts cannot be scheduled and can’t be exported from CRM without doing a print screen (or I haven’t found a way to do this yet, please comment if you know how)

I was able to run the report in Excel PowerPivot, although it took me a while to work out why I couldn’t group the date by month (as seen in many Google searches on the subject).  If you cannot group/format a date in excel to month this is probably because there are some blanks in the date field, if you fill in this blanks you will then be able to format the date by month.

The excel PowerPivot report looked very good and has the advantage of being self sufficent.  What I mean by this is once you have saved the Excel report onto your hard drive you can run the report from your machine without opening CRM and the reason you can do this is because you can refresh the data within Excel.

I think excel is a really useful tool because you can also view the individual lines behind the graph, so it does give you a bit of drill down in basic terms.

I do find some users don’t view reports in Excel very highly and almost don’t see this as a legitimate solution to their reporting needs.

 

I tried to write the report using FetchXML, this does make writing reports quite simple because all you need to do is create the correct advanced find and then download the fetchXML.  The problem I had with this method was all the grouping had to be done then in the report.  My SRS reporting skills are not great and I couldn’t really get round the problem of the report displaying every line, e.g. every call, I didn’t want this I wanted a summary count of the different call types.

Searching around I found you can group one item in a fetchXML.

The other solution is using SQL and doing selects from Filteredlists.  The reason it’s good practise to use filtered lists is these lists automatically add the CRM security to the selects as if you are viewing the records in CRM.  It can be a bit slower and some reports don’t need to worry about security if you are showing summary values and not individual records.

The benefit of creating an SQL report is you can group the data in the SQL statement and use the report to just show the data.

The downside to using SRS is it’s hard to use and formatting the report can be a puzzling and time consuming practise of trial and error.

I really wish Microsoft would supply an easy to use reporting system on the same lines as the dashboards and charts.  Reporting is one of the key areas for CRM, useful reports can help get support for a CRM project from the key decision makers.

My hope is the Sharepoint/SQL Server 2012 new reporting tools are very good and if they are not then Microsoft should buy a company which has already developed a reporting tool.

 

So in the end I did the report in Excel PowerPivot and a version in SRS reports using an SQL statement I wrote.

CRM 2011 – No reports working in CRM

I had a very odd and unusual problem, actually I say that most problems that involve reporting services are almost always odd and not unusual.

I was working on CRM which had already been setup (not by be) and the users were having problems with reports.

The problem was none of the built in reports were working, you also couldn’t upload any reports.

When you tried to run a report I got a

Microsoft CRM Error Report saying

Cannot run OOB report, unless SRS Data connector is installed

it is possible to use CRM with out SRS data connector working, you won’t have any reports.  The odd thing here was all the reports were in CRM, they just weren’t working.

I installed the SRS data connector but I was still getting the error message.

Initially I thought the problems were because the installation was done on a named instance.

The installation of the SRS connector seemed to work without any problems.  So I tried running the repairs and configs on the CRM server, still no joy.

I finally disabled the organisation, edited the organisation and then pressed next.  I got a message about the SQL server setup.

I ran the SQL Server configuration tool and found the FULL TEXT Search, SQL Server Replication and Client Tools connectivity had not been enabled.

I added the in and reinstalled the SRS Connector.  I now got a different error message in reports

Error occurred while getting the data source contents

which finally took me to this blog page

http://weblogs.asp.net/pabloperalta/archive/2011/09/05/error-occurred-while-getting-the-data-source-contents-for-the-report.aspx

One thing I also noticed is that when I went to the reports manager

http:///Reports/

there were no reports for any organisations, which explained why I couldn’t run any of the default reports but the question was how do I load them.

The blog post above says you can use the tool

PublishReports

which is a command line tool to load the default reports, you need to just pass it the name of your organisation

publishreports organisationName

after running this the reports were loaded into CRM and I could finally run reports.

it took me ages but I finally resolved all the problems

CRM 2011 – Creating Custom Reports with Bids

I have been dipping my toe into the world of custom reports and trying out the new Bids extension which allows you to create reports using FetchXML.

This is a great because you can do an advanced find in CRM 2011 and then download the fetchXML from this and use is as your datasets

For a great run through in creating your first Custom Report you should use this step by step walkthrough

http://blogs.msdn.com/b/crm/archive/2010/10/26/how-to-creating-custom-report-with-microsoft-dynamics-crm-2011-bids-fetch-extension.aspx

I also found this article which is very good

Getting start with Fetch xml reports in CRM 2011

I will admit to having difficulties in setting up the Datasource, I didn’t understand what I was meant to put in and if you are just trying to do it without the walk through above it’s baffling because it doesn’t really prompt you or indicate what you should put in

1. Select Microsoft Dynamics CRM Fetch for type and fill in the connection string in the following format: ServerURL;OrganizationName;HomeRealmURL

1. only ServerURL is required, this would be the url to your CRM server like:https://someserver.crm.dynamics.com

1. The optional OrganizationName should also be included if you have multiple CRM environments, it will use the first organization otherwise – you will find this value within the Developer Resources area in the CRM client located within Settings > Customizations (refer to the red mark).
2. The HomeRealmURL, also optional, it will be a registry value from HKLM\SOFTWARE\Microsoft\MSCRMBidsExtensions\HomeRealmUrl

I don’t know why you would need to put in the HomeRealmUrl?

This is what I had to put in

http://crmdevcrm;Hosk

the first part is my CRM url and the second part is the organisation Name.  you can find this in the developer resources which are held in

SETTINGS/Customizations/

and then under Organisation Unique Name

This all seemed like a lot of effort, why could we just paste in the server url with the organisation in it.

Using FetchXML involves a lot of downloading and opening files and then pasting the contents into reports.  It’s a pity that CRM doesn’t offer you the option to save the fetchXml to the clipboard so you could then just paste it into report designer.

I still haven’t got the custom reports working, it’s giving me some kind of permissions error perhaps.  My first experience with the Bids reporting is it’s a good work around from Microsoft and I will give them some credit for that but it does feel like a workaround.  Things are not quite as smooth as they should, the process of doing an advanced find, then downloading it, opening it, changing the alias information and then copying it into SSRS.

Making a report in SSRS is slow process and I think this is an area Microsoft need to spend some time improving.

CRM 2011 – Reports – removing lines if blank for address

I had a tricky problem of adding an address to a report.  The address had the usual

address 1

address 2

city

county

etc

The problem was a lot of the addresses didn’t have a value for address 2 and it was leaving a nasty look blank row on my report.  So I asked one of the reports gurus at work and he found this lovely if statement for my expression.  It checks to see if the value is blank, if the field has a value and then puts in a carriage return and then goes to the next line.

=

IIf(IsNothing(First(Fields!Address1_Name.Value, “DataSet5″) ),””,(First(Fields!Address1_Name.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_Name.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line1.Value, “DataSet5″) ),””,(First(Fields!Address1_Line1.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_Line1.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line2.Value, “DataSet5″) ),””,(First(Fields!Address1_Line2.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_Line2.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Line3.Value, “DataSet5″) ),””,(First(Fields!Address1_Line3.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_Line3.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_City.Value, “DataSet5″) ),””,(First(Fields!Address1_City.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_City.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_StateOrProvince.Value, “DataSet5″) ),””,(First(Fields!Address1_StateOrProvince.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_StateOrProvince.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_PostalCode.Value, “DataSet5″) ),””,(First(Fields!Address1_PostalCode.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_PostalCode.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) ) &

IIf(IsNothing(First(Fields!Address1_Country.Value, “DataSet5″) ),””,(First(Fields!Address1_Country.Value, “DataSet5”) ))&

IIf(IsNothing(First(Fields!Address1_Country.Value, “DataSet5″) ),””,ChrW(13)&ChrW(10) )

How to upload large report files in CRM

If you have a report file in CRM that is larger than 4MB, you will get an annoying and nondescript error when you try to upload it.

The default report size in CRM 4 (and I think CRM 2011 as well).  This article – Report and Snapshot Size Limits, this section explains about the maximum size and why it is set to 4MB

Maximum Size for Published Reports and Models

On the report server, report and model size is based on the size of the report definition (.rdl) and report model (.smdl) files that you publish to a report server. The report server does not limit the size of a report or model that you publish. However, Microsoft ASP.NET imposes a maximum size for items that are posted to the server. By default, this limit is 4 megabytes (MB). If you upload or publish a file that exceeds this limit to a report server, you receive an HTTP exception. In this case, you can modify the default by increasing the value of the maxRequestLength element in the Machine.config file.

Although a report model might be very large, report definitions rarely exceed 4 MB. A more typical report size is in the order of kilobytes (KB). However, if you include embedded images, the encoding of those images can result in large report definitions that exceed the 4 MB default.

ASP.NET imposes a maximum limit on posted files to reduce the threat of denial-of-service attacks against the server. Increasing the value of the upper limit undermines some of the protection that this limit provides. Increase the value only if you are confident that the benefit of doing so outweighs any additional security risk.

Now if you have some pictures in your report it can easily go above 4MB but when you try to upload the report you will get an error.  You will need to configure the report services, although in this case someone else did this some I’m not entirely sure what had to be changed.  I guess you will need to up the memory, these two links should help with that.

Configuring Available Memory for Report Server Applications

http://msdn.microsoft.com/en-us/library/ms159206.aspx

How to: Modify a Reporting Services Configuration File

http://msdn.microsoft.com/en-us/library/bb630448.aspx

You also need to change some web.config settings for the Microsoft Dynamics CRM website, to up the limit from 4MB’s.

This forum post had a good response to what settings you should change, I found you needed to make this change the CRM web.config.

Increasing the maxRequestLength under httpRuntime in web.config file of reporting services will help.  The default value is 4MB, however, base64 encoding used by RS has an overhead of approximately 25%, so the actual limit will be hit at around 3.2MB.

e.g. to increase the maxRequestLength to 20MB:

<httpRuntime executionTimeout = “9000” maxRequestLength=”20480″ />

My college also sent me this email, although I’m not sure where he got it from but it basically says something similar to the line above.  I not so sure about changing the machine.configs but you may need to so I have included it.

The root of the problem here was .NET. Nothing changed when editing the web.config files. However, there were two machine.config files on the server in the following locations:

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\CONFIG

and

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

The machine.config in the v1.1.4322 folder had exactly what I was looking for with comments on what to replace. But changes these settings will not affect anything. Changing the machine.config for v2.0.50727 will have an affect. The reason why this is, is because IIS was using ASP.net v2.0.50727 (but changing IIS to use 1.1 did not accomplish anything). Now the strange thing about the 2.0.5 version was that it did not have any of the parameters already in it that I needed, so I skipped over it when my search didn’t find maxRequestLength (my mistake there).

HERE IS THE FIX

1. Go to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
2. Open machine.config
3. Search for <system.web>
4. Add in <httpRuntime executionTimeout=”9000″ maxRequestLength=”102400″ /> under the <system.web> (I added it right before the end)
5. Save
6. Restart IIS

That should fix the problem. I attached the code that I edited so you know what to look for. The first part is from the file that doesn’t affect anything but tells you what everything does. The second part is the part that I edited and fix everything with.

1:  2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45:

–v1.1.4322  httpRuntime Attributes: executionTimeout=”[seconds]” – time in seconds before request is automatically timed out maxRequestLength=”[KBytes]” – KBytes size of maximum request length to accept useFullyQualifiedRedirectUrl=”[true|false]” – Fully qualifiy the URL for client redirects minFreeThreads=”[count]” – minimum number of free thread to allow execution of new requests minLocalRequestFreeThreads=”[count]” – minimum number of free thread to allow execution of new local requests appRequestQueueLimit=”[count]” – maximum number of requests queued for the application enableKernelOutputCache=”[true|false]” – enable the http.sys cache on IIS6 and higher – default is true enableVersionHeader=”[true|false]” – outputs X-AspNet-Version header with each request –> <httpRuntime executionTimeout=”1200″ maxRequestLength=”102400″ useFullyQualifiedRedirectUrl=”false” minFreeThreads=”8″ minLocalRequestFreeThreads=”4″ appRequestQueueLimit=”100″ enableVersionHeader=”true” /> –v2.0.50727 <system.web> <processModel autoConfig=”true”/> <httpHandlers/> <membership> <providers> <add name=”AspNetSqlMembershipProvider” type=”System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” connectionStringName=”LocalSqlServer” enablePasswordRetrieval=”false” enablePasswordReset=”true” requiresQuestionAndAnswer=”true” applicationName=”/” requiresUniqueEmail=”false” passwordFormat=”Hashed” maxInvalidPasswordAttempts=”5″ minRequiredPasswordLength=”7″ minRequiredNonalphanumericCharacters=”1″ passwordAttemptWindow=”10″ passwordStrengthRegularExpression=””/> </providers> </membership> <profile> <providers> <add name=”AspNetSqlProfileProvider” connectionStringName=”LocalSqlServer” applicationName=”/” type=”System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”/> </providers> </profile> <roleManager> <providers> <add name=”AspNetSqlRoleProvider” connectionStringName=”LocalSqlServer” applicationName=”/” type=”System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”/> <add name=”AspNetWindowsTokenRoleProvider” applicationName=”/” type=”System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”/> </providers> </roleManager> <httpRuntime executionTimeout=”9000″ maxRequestLength=”102400″ /> </system.web>