Connecting to CRM Online OData feed with Excel 2013 Power Query


Posted on 15th December 2013 by Jukka Niiranen in Tips

, , , , , , , , ,

The latest version of Excel contains a Get External Data menu option for linking the workbook to an OData data feed. Knowing that Dynamics CRM 2011 and 2013 both provide an OData endpoint, this would seem like a great option for retrieving data from a CRM Online based system for reporting purposes, since direct database connections aren’t available in the cloud. Unfortunately, if you try to enter the CRM Online OData URL as a feed link, you’ll get this error message:


“We can’t use the data from this feed.” Bummer. The reason behind the error messages is that Excel and PowerPivot have not been able to support the authentication mechanism required for accessing the CRM Online OData feed. There is a workaround that you could apply if you’ve got the CRM Outlook client installed on the same machine, as described in this video by Ed Martinez, but in general using PowerPivot for CRM Online reporting has been quite restricted.

Power BI December 2013 Update

Last summer Microsoft announced the preview program for Power BI, their cloud based business intelligence suite, containing tools for publishing reports in Office 365. The Power BI umbrella also covers client side components, including Power Pivot (now spelled as two separate words apparently) and Power Query, that bring new capabilities to Excel. To get an understanding of the features included or under development, I encourage you to watch this Power BI demo from WPC 2013, which definitely deserves the Coolest Tech Demo of 2013 trophy if you ask me.


The latest December 2013 Update for Power BI announcement mentions the following enhancements:

“Power Query can now connect to more data sources:

  • Sybase IQ
  • Exchange
  • Dynamics CRM Online

Hey, that last part sounds great! So, what does it mean in practice for us CRM people? Well, the Power BI Preview subscription still doesn’t provide the option to provision a CRM Online instance for the same Office 365 instance, so there’s not that much new things visible on the cloud side yet if you spin up a preview org. On the client side, however, there is now an updated version of Power Query Preview for Excel 2013 that gives us the possibility to finally connect to a CRM Online OData feed directly. Let’s explore that in more detail. (more…)

No-code Customizations with North52 Formula Manager, Part 3: Case Resolutions


Posted on 4th July 2013 by Jukka Niiranen in Tips

, , , , , , , , ,

In the previous articles (part 1 and part 2) we’ve explored how the North52 Formula Manager can be utilized in automating steps related to the sales process. This time we’ll be looking at a scenario related to service management, in an effort to make it easier to share knowledge and report on the resolutions of cases recorded in the Microsoft Dynamics CRM database.

Cases vs. Case Resolutions

Case_resolve_cancelIn theory it should be pretty straightforward how you work with a case record: they are support tickets that are initially open (active) when you create them and eventually they end up getting closed as either resolved (service was provided) or cancelled (duplicate ticket, customer never replied etc.).

The point where complexity raises its ugly head is how the resolution process works: instead of entering the case resolution details onto the case record itself, the user is presented with a window that creates a Case Resolution activity underneath the parent case. While this is a perfectly valid design in terms of the nature of the interactions, as there could be situations where the case gets re-opened and resolved again (thus being a 1:N relationship with the case), it does make it more complicated to work with the resolution data later on.


For example, say you’d want to study the resolved cases by using a view of the case records. In that view you can see the case subject, owner, status and other standard fields, but there’s no information visible on what the actual resolution to the case was. “Ok, so I’ll need to customize the view and show columns from a related record. That’s not too difficult, now is it?” Unfortunately that’s not going to work, because the information we’re interested in resides on the N side of the relationship. Since there can be several case resolutions for a single case, no columns from this entity can be displayed in a case view. So, we can’t construct a nice Q&A list that the service reps could leverage in scanning for similar cases on a new question from a customer. We can only see the problem, not the resolution.


You could build a view of case resolutions, the child entity in this relationship, but that’s not very convenient either. Although case resolution is an entity of its own, it’s not actually available in Advanced Find to directly build queries on. It is possible to access a list of case resolutions by crafting a view of activities with that specific type, but you’ll still be limited to only the generic activity fields. As an example, the field Billable Time (Time Spent) cannot be accessed in a view, which makes it rather difficult to report on this data entered as a part of the service process.


Using a Formula to Replicate the Case Resolution Fields on the Case Form

Luckily Dynamics CRM is a flexible platform that allows you to develop new business logic to fulfill the requirements for your service process. In this situation, if we could simply have the Resolve Case dialog fields copied over to the parent case, this would solve the aforementioned problems. So, how to proceed then?

If we want to alter the default behavior or the service entities, we should first have a look at the workflow process capabilities of Dynamics CRM to see if can  configure a workflow rule that is triggered when the case resolution is created. This time we won’t get very far with that idea, though, as neither the case resolution nor the activity entity can be used as a workflow trigger. Fair enough, we’ll then need to come up with a lower level solution to meet this requirement. Assuming we have access to a .NET developer who knows the Dynamics CRM SDK, creating a custom plug-in to copy the fields to the case record would be a worthy option to consider. Since the title of this post promised “no-code customizations”, let’s instead look at how we could achieve the same functionality with the Formula Manager.

First we need a place to host the case resolution data of course, so let’s add three custom fields on the case entity: Resolution (text field), Resolution Description (multiple lines) and Time Spent (whole number with duration format). On the case form they can be put into a section of their own and set as disabled, since the user shouldn’t directly update them.  Then we’ll create the three formulas that will populate these fields with corresponding values from the case resolution entity.


We’ll be using a formula of the type “Save – To Parent” and attach it to the create event of the case resolution entity. As our target (parent) entity we can select case, but notice that you’ll actually need to specify the relationship field value first before you’re able switch the value in the target entity field. Let’s take the Resolution Description field we just created as the target property. The formula itself in this case will be very simple, since all we need is a value directly from the source entity. We can use the source entity tree visible in the bottom left corner of the screen to browse through the available fields or just type in directly the value [incidentresolution.description].


The other two fields will get an identical treatment, which means we can click on the Clone Formula button on the ribbon and create two copies of the original formula. Just update the target property field and select a new source field value into the formula description window accordingly. After we’re done, we can publish the formulas and try them out by resolving an existing case.


After we’ve entered the details into the Resolve Case dialog fields and clicked OK, our formula will update the underlying case form in real time to reflect the same values presented directly on the resolved case record. Unlike asynchronous workflow processes, the formulas can perform their tasks right in front of the user, which makes the user experience more consistent.


Making Use of the Resolution Data

Now that the fields are available on the case entity, we still need to ensure that the user actually has access to them through all the necessary routes. First we should of course include them into the Resolved Cases view we talked about earlier.


Seeing the resolution field contents directly in the view is a great improvement, but an even more important feature is to enable the users to search for this information. You see, one of the peculiar default settings of Dynamics CRM is that the Quick Find view for case entities only covers active (open) cases. (more…)

No-code Customizations with North52 Formula Manager, Part 2: Line Items


Posted on 26th June 2013 by Jukka Niiranen in Tips

, , , , , , ,

In a previous post I wrote about how the North52 Formula Manager solution can help you build some common customizations that would otherwise require writing Javascript for Dynamics CRM entity forms. The scenarios included:

  • Setting default values for lookup fields
  • Dynamically changing the field requirement level
  • Displaying popup warnings
  • Launching dialog process windows

This time I will show a few examples of the Formula Manager capabilities that would typically fall into the domain of writing C# plugin code to enhance the business logic of Dynamics CRM. Being a functional CRM consultant that doesn’t normally deliver a single line of code (at least for production environments), these scenarios would traditionally require me to hand over the task to a CRM developer. What we’re about to see is an alternative method of creating just formulas with the graphical tools available in the solution package to get the same job done.

Updating Opportunity, Quote, Order or Invoice Products

In Microsoft Dynamics CRM 2011 you can trigger workflow processes on the line items of Opportunities, Quotes, Orders or Invoices. This allows you to execute custom logic when, for example, new Quote Product rows are added for a Quote record. Unfortunately what you cannot do with standard workflows is to actually update the line item record itself.


Well, this surely looks like a dead-end for most of the use cases you could think of for triggering the workflow process in the first place. If I wanted to, for example, pull information from the selected Product record and save it on the Quote Product’s fields, such as product description, vendor details or a line item number to sort the quote rows, this apparently cannot be achieved with CRM’s workflow functionality.

Let’s look at how we can solve the problem with Formula Manager instead. In our scenario we would like the description field of the Quote Product record to be updated with the description field contents of the selected Product record. What we therefore need is a “Save – To Current Record” type of formula that is triggered on the Create & Update events of the Quote Product entity. We can limit the update events only to the Existing Product field by setting the Source Property value. The Target Property of our formula should be the Description field.

Finally, we need the actual formula that will feed the values from the related Product record into the Quote Product’s description field. To retrieve the description field contents we’ll use the FindValue function and tell it to search for a Product record that has the same productid as the existing product selected on the Quote Product record. While we’re at it, let’s also ensure that the Quote Product actually has an existing product instead of a write-in product by verifying the productid field contents with the ContainsData function. The end result will look like this:

if(ContainsData([quotedetail.productid]), FindValue(‘product’, ‘productid’, [quotedetail.productid], ‘description’),’NoOp’)


Now when we navigate onto a Quote and add a new Quote Product record underneath it, the description field will inherit the value from the related Product and display it directly on the Quote Product form (assuming you’ve made this default field visible on the form in the first place).


Not that much more complicated than building a workflow rule, just as long as you familiarize yourself with how the formulas work and how to format the parameters required by the functions.

Creating Line Items

Let’s proceed with exploring the world of line item records in the sales process. Updating existing records automatically is a nice capability to have for sure, but what about automating the complete process of creating the line items for an Opportunity, Quote, Order or Invoice? For example, if we wanted to ensure that there is always a standard Delivery Fee or other persistent item included whenever we’re creating an Order of a specific type (or for a specific customer group), then this is another area where the out-of-the-box functionality of Dynamics CRM workflows can’t be used, since new Order Product records cannot be created through a workflow rule.

With Formula Manager we have no such limitations in place, rather we are free to create any type of records we want with the CreateRecord function. The example formula below was actually provided to me by North52′s John Grace to demonstrate the solution’s functionality. The Save – Perform Action formula is attached to the Create event of an Order record, which in turn triggers the creation of a new Order Product record. The contents of this auto-created line item can be defined either directly in the formula or alternatively queried with the FindValue function familiar to us from the previous example.

‘salesorderid.salesorder.’ + [salesorder.salesorderid],
‘productid.product.’ + FindValue(‘product’, ‘name’, ‘Bike’, ‘productid’),
‘uomid.uom.’ + FindValue(‘uom’, ‘name’, ‘Primary Unit’, ‘uomid’),


Now, as a limited time special offer, any new Order that we create (or a Quote that we convert) will get 10 Bike products added onto it with their unit price dynamically retrieved from the Price List selected on the Order. What a sweet deal!


Cloning Records

For a Dynamics CRM administrator these type of automated steps can be really handy in enforcing business rules and ensuring correct entry of data onto sales records. If we’d show them to your typical salesman, though, he might not be so impressed with this type of detailed process automation functionality.

“Sure, it looks like we could save a few clicks with these formulas. But here’s the deal: many of the quotations I make are practically identical. They contain the same line items every time, with only some variations in quantity and discounts given. What I’d really want to do is simply select a quote I’ve created for another customer and create a copy of it. You know, the “Save As” button that’s found in all the other Office applications. Why couldn’t CRM also have that? It would be a huge time saver for me.”

Ever had this kind of a discussion with your CRM users? If you have, then you’ll surely appreciate the fact that Formula Manager provides a Clone function that allows you to create a new copy of any existing record. Not only that, but the function also clones all the related 1:N & N:N records, which means you can create a copy of both the Quote and Quote Product line items with a single function!

To get an understanding of how the function could be leveraged in delivering the “Save As” functionality your sales users have been asking for so long, watch this video that demonstrates not only the formula in action but also how you can use the Ribbon Workbench to build a custom Clone button for the required entities.


Think these type of features would be useful to have in your Dynamics CRM organization? Then go ahead and grab the fully functional Standard Edition of Formula Manager that allows you to have 10 active formulas at a time, for test or production use.

Switch to our mobile site