Tip #352: When you can’t publish a report

Sometimes when writing SSRS reports for CRM, you will find that the report works fine in Visual Studio, but will fail when you upload them to Dynamics CRM.  This can be frustrating, because it can sometimes be difficult to identify the cause of the issue—it works fine when you preview it in Visual Studio, but when you upload it, the upload will fail, and typically the error message created will be very generic—something to the extent of “Call your System Administrator.”

Typically when you see this problem, it has to do with SSRS functionality specific to CRM reports.  Here are some of the top causes that I see reported by users:

  1. Data source—When you create an SSRS report, you have the choice of using an Embedded data source or a shared data source.  For CRM reports, you must use an embedded data source.  If you use a shared data source, your report will preview in Visual Studio, but you will not be able to upload it.
  2. Hidden parameters—If you use the special hidden CRM parameters, such as the CRM_URL parameter to build dynamic hyperlinks to CRM records, if you don’t set them up properly, the report upload to CRM will fail, but it will still preview OK in Visual Studio.  Make sure that the parameters are set to hidden and allow null values.
  3. Pre-filters—When using CRM Report Pre-Filtering, if you use pre-filtering incorrectly, sometimes the report may fail to upload correctly.  In this case, like the others, the report will preview without error in Visual Studio, but will have issues uploading to CRM.

Troubleshooting

So now that we’ve talked about what some of the common causes for the “works in Visual Studio, but not in CRM” issue, how do you troubleshoot what is causing your report issue?  The best place to start is the event log of the CRM Server.  If you get a “Contact your Administrator” error message, frequently you will see a more detailed error message.  Recently I had a user experiencing this issue, and I checked the event log on the CRM server.  I found the following error message captured in the event log:

The parameter ‘CRM_URL’ has no default. A default is required for all non-nullable parameters without a prompt or the valid values list has to contain Null.

After modifying the SSRS report CRM_URL parameter to accept null values, the report could be successfully uploaded.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #351: Tracing for plugins and custom workflow activities

CRM Online continues to improve on the existing features, as Andre “I’ve got 88 in my handle” Margono noticed when looking at the tracing functionality. ITracingService was available to the savvy developers since CRM 2011, however, the catch always has been that trace is only available when things go wrong, exception kind of wrong. Enter CRM Online 2015 Update 1 (currently in preview).

From the words of Andre himself:

I just noticed the new plugin/custom workflow tracing and logging functionality in my preview instance and now the content has been updated with the information: https://msdn.microsoft.com/en-us/library/gg328574.aspx#loggingandtracing

I think it is a great addition to the plugin/WF capability to keep track of the performance of the custom code where it might breaks.

Plus with this note: “If your custom code executes within a database transaction, and an exception occurs that causes a transaction rollback, all entity data changes by your code will be undone. However, the PluginTraceLog records will remain after the rollback completes.” the tracing functionality won’t interrupt the transaction as it happened today if I want to put custom logging functionality.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #350: Avoiding duplicate contacts when upgrading

When you upgrade CRM for Outlook, one thing to watch out for is duplicate contacts in Outlook. The reason contacts can get duplicated is that if you use Outlook synchronization for contacts and you remove or reinstall the client, the local synchronization database gets replaced. When you reinstall the client and the synchronization runs, a new copy of your synchronized contacts will be downloaded.

To avoid this happening, try to upgrade in place. In-place upgrades do not break the synchronization, and contacts should not be duplicated. If you are going one version in the upgrade, this is the recommended approach. And consider moving to server synchronization so you won’t have to worry about this for future upgrades.

If you are upgrading across multiple versions, in-place upgrades are not practical, and as a result, you will need to uninstall the Outlook client and install the new version. In this case, duplicate contacts cannot be avoided, and a strategy of remediation is your best course of action.

  1. Turn off synchronization in the Outlook client.
  2. Move all synchronized contacts to a different folder (you can tell which are synchronized by looking at the icon. It’s the two-headed guy).
  3. Uninstall the old version of the Outlook client.
  4. Install the new client and synchronize.
  5. Once the contacts are re-synchronized, you can delete the old contacts from the folder you moved them to.
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #349: Bulk delete in bulk

After using Bulk Delete Wizard in Dynamics CRM
Bulk Delete Wizard
one can be mistaken for thinking that bulk delete is only capable deleting one advanced find result screen at the time.

But if mentioning of C# invokes more that just musical associations for you, welcome to the realm of SDK, where things are not what they seem in user interface. As per SDK documentation:

With bulk deletion you can perform the following operations:… Delete data across multiple entities…

BulkDeleteRequest class contains a member variable QuerySet which, as the name suggests, is an array of QueryExpression objects.

Bonus

If you ever spent time trying to make your bulk delete to run faster by modifying query page size, stop it. System duly ignores all your attempts and creates its own PageInfo object that processes 1,000 records batches.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #348: Watch your audit partition size

We have had several tips this week regarding managing storage on CRM online, and I wanted to finish the week with one more way that the storage gremlins can creep up on you: Audit partitions.

I recently was reviewing one of my environment’s audit logs, and I found one of these things was not like the other.

IMG_0735

Due to some unfortunate circumstances, the most recent audit partition is 164 GB (that’s right, with a G).

What you need to know about audit partitions:

  1. You can delete them, starting with the oldest one.
  2. You cannot delete the current audit partition until the end date. They happen quarterly, so the end of the current partition is 3/31.
  3. You can delete them via the SDK, but it has the same limitations as number 2.

So what this means is that if you have a very large audit partition and it is using all of your available storage, there is no supported way for you to shrink it or delete it. You have to wait until the end of the period, then you can delete it.

For on premises users, you can go the unsupported route and delete audit records via T-SQL. If you use CRM Online, open a ticket with Microsoft support or add additional storage should this happen to you.

 

 

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #347: How to use datetime fields in WHERE clause

Gotta love user Q&A sessions, like the one at Convergence 2015 that I had a privilege to take part in. Questions from users are always refreshing, very often challenging and sometimes they are just the reminder that any small bit of information that one possesses is not necessarily the common knowledge.

On this occasion question was about poor performing reporting queries dealing with the activity records. There were tons of good suggestions but one thing that the user seems to have missed is how to properly use CRM datetime fields in the queries. Let’s say we’d like to filter all activities that started last month. The first query that comes to mind:

select activityid, subject 
from FilteredActivityPointer
where scheduledstart >= '1-feb-2015' 
  and scheduledstart < '1-mar-2015'

Reasonable? Yes. Best performing? Heck, no. The problem is that filtered views, among other things, convert datetime CRM fields into the timezone of the current user. To do that, datetime column names have suffix “utc” added to them and the original names are used to display local time equivalent. For example, every entity in fitered view would have createdon (local time) and createdonutc (the actual value stored in the database).

How this local time is calculated? Using scalar-value T-SQL functions. What chances are of the scalar-value functions in WHERE clause using an index? None. Hello, table scans.

The Spießrutenlaufen Rule of Filtered Views

Whenever CRM datetime field needs to be in the WHERE SQL clause, the utc version of the field should be used.

The above would become:

select activityid, subject 
from FilteredActivityPointer
where scheduledstartutc >= 
            dbo.fn_LocalTimeToUTC('1-feb-2015') 
  and scheduledstartutc < 
            dbo.fn_LocalTimeToUTC('1-mar-2015')

How much do you save? YMMV but on my test server against random CRM organization the execution time of the first query was 2280ms, whilst the second has finished under 80ms.

Like a boss meme96% improvement.

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #346: Scheduling daily bulk delete jobs

I noticed that my CRM Online storage was getting low, so I created a scheduled bulk delete job to remove system jobs. My environment has many workflows, and since upgrading to CRM 2015 and creating a bunch of rollup fields, I have noticed a slight increase in the number of system job records being created. I want to have a job that runs daily and deletes successfully completed jobs. However, the minimum frequency of bulk deletion job recurrence is 7 days.

Adam Vero gave me a great, simple suggestion–schedule multiple bulk delete jobs, one for each day of the week. Set each to recur every 7 days. The end result is a daily schedule.

UPDATE: Turns out there is an even easier way. Even though the drop-down doesn’t give you a daily option, you can still choose 1 by typing in the field. Like the phone call duration field, the “run the job after every X days” field gives you pre-set options, but also allows you to type in virtually any number you want. So simply type in “1″ rather than selecting a value from the drop down.

Screenshot 2015-03-17 23.08.31

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #345: Turn CRM Green

Today is St. Patrick’s Day, a day when many celebrate by wearing green and ingesting green-colored beer.

As you probably know, we at crmtipoftheday are fans of making CRM more festive. So wouldn’t it be great to be able to make CRM green?

With the CRM Online Spring ’15 Update, you can!

  1. Go to Settings–>Customization
  2. Click “Themes”
  3. Select the CRM default theme, then click “Clone” to make a copy of the default theme.
  4. Change the value for Header Color to HTML color code #006551

Screenshot 2015-03-16 22.11.29

The results will leave you yearning for the emerald isle.

Not only can you change the application colors, you can also now replace the logo. This “branding” feature is something that CRM users have frequently requested. We suggest that you choose something seasonally appropriate. A leprechaun, for example.

Leprechaninski

 

 

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #344: Why Can’t I Create Any Records?

If you use CRM Online, and you run out of space, what happens?

This:

Screenshot 2015-03-11 08.46.46

The Quick Create links disappear, replaced by a gray bar.

Screenshot 2015-03-11 08.47.01

And the “New” button disappears from views and grids.

When this happens, either add space or delete records (like completed system jobs) to free up memory. After doing that, there will still be some delay before you can once again add records. If it continues to not allow record creation, open a support case with Microsoft support.

VN:F [1.9.22_1171]
Rating: 4.0/5 (1 vote cast)
Tweet about this on TwitterShare on FacebookShare on Google+

Tip #343: In Dynamics CRM we trust

Having customer’s commitment issues? Something like this?

Oh, I don’t trust corporations, they are out there to get you and your money. Remember Lehman Brothers?

To start with, unlike subprime mortgages, Microsoft Dynamics CRM is a real product. What’s important though, that Microsoft, unlike payday lenders, recognizes concerns customers and prospects alike might have about entrusting their valuable business data to a cloud provider, Microsoft or not.

Enter Microsoft Dynamics CRM Trust Center. Abridged summary follows, to save you a visit until you have stocked up on your coffee supplies and have a spare afternoon to sieve through the abbreviations and trying to understand how PII is different from SSN.

Privacy

No ads, no mixing the data with other customers, data portability – unless there is a court injunction stopping you from doing so, you can “whack” your data at any time (by proxy, of course, no one will let you anywhere near the datacenter, and that’s a good thing).

Transparency

You know where the datacenters are. You know who has access to your data and under what circumstances.

Security obsession

Decades of experience, five layers of the security, proactive monitoring, restricted server access. ‘Nuff said.

Standards

First major cloud provider to support ISO/IEC 27018. Certified for ISO 27001, support for EU Model Clauses, HIPAA, SSAE annual attestation, FedRAMP, FISMA, U.S.–EU Safe Harbor, CJIS. Enough abbreviations?

What’s equally if not more important, is that Microsoft is absolutely open when CRM is no-worky. Take for example Regulatory Compliance FAQ

Office 365 and Microsoft Dynamics CRM Online do not support the processing, transmitting, or storing of PCI-governed data, such as credit card numbers.

In other words, any solution vendor claiming safe storage of credit card data inside Dynamics CRM, was designing subprime mortgages in their previous corporate lives.

I don’t know about you but I feel safe for my and my customers’ data.

Bonus points to Microsoft for making Australian site prettier than US one and for correctly spelling Centre.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
Tweet about this on TwitterShare on FacebookShare on Google+