Working with Price List Items in Dynamics CRM

Despite of the recently refreshed user interface of Dynamics CRM 2013 that offers a much more fluid user experience than previous versions, there are still areas in the application that are not very user friendly. Many of these revolve around product and price information, regarding how it is presented and what actions are allowed on it. In this blog post I will drill into a common scenario that organizations who use CRM for managing price list data may run into and present a few options on how to make their lives easier.

Price List and Price List Item Views

A pet peeve of mine in Dynamics CRM has always been the UI that the Price List entity offers to the end user. As many of the readers of this blog will surely know, price list items are the way how products, units, price lists and the all important price figures come together in the CRM data model. If you want to leverage the product catalog and any price calculation features in the sales module, you’ll need to work with price list items and create at least one of them per each product you plan to include as line items on your opportunities, quotes, orders and invoices.

Unless you’ve built a custom integration to a back-end system that will automatically provide the latest pricing information for CRM, there’s quite a bit of work involved in maintaining individual price list item records when prices change or new products or lists are introduced as a normal part of the day to day business. When a CRM user opens a price list record, a reasonable assumption to make would be that he or she is interested in reviewing the pricing information given to the included products. Unfortunately the Dynamics CRM UI does not make such an assumption, rather it thinks the user is interested in only viewing a list of products and their units but not the actual price information in the amount field. Here’s what the default associated view of the price list items gives us:

Well, that sure looks like a good candidate for some entity customization work. Yes, it does, but there’s a “but”. When you open the customization UI and navigate to the price list item entity, you discover that the views are actually not customizable. Nor can you add any of your own views for that matter, which means you’re stuck with the default UI. If you think that the price list item entity should allow view customization, then there’s a suggestion on Microsoft Connect that you definitely should go and vote for (if you need help in registering to Connect itself, see this post).

Exporting the Price List Item Data to Excel

With this limitation in mind, what are our options of producing a true price list view with product and price information shown side by side? For any Dynamics CRM power user the first thing to come to mind will surely be to export the data into Excel. Unfortunately the uncustomizability of the Price List Item entity also means it has been blocked from showing up in Advanced Find, which would normally be our tool of choice for preparing a CRM data export.

Luckily there’s still an Export to Excel button visible in the ribbon of the price list form when we are viewing the associated price list items view. Clicking this will present us with an option to either export the data in static format (which would just give us the same columns as the current view) or to create a dynamic Excel sheet in two possible formats. Both of the latter options, pivot table and worksheet, present a follow-up dialog where choosing the required columns from the price list item entity and even any parental entity like product is possible.

When you export the view into a dynamic Excel sheet in an on-premises CRM environment, you can actually go and look at the SQL query that the view is using for pulling the data from CRM to Excel. Just click “Change Data Source – Connection Properties – Definition” and copy the query from the Command Text window into Notepad. With a little tweak that removes the reference to the currently viewed price list record we can use the same dynamic Excel sheet to retrieve price list item data for all the price lists in the system.

In the SQL query you’ve copied to Notepad you’ll find a reference to the price list from under which we exported the related price list items. It will look something like this: where  (“productpricelevel0”.pricelevelid = N’CEA84006-AD7B-E311-9405-00155D6214FA’) . Just remove this whole where clause, thus expanding the query to retrieve all records from the price list items table in CRM, regardless of the associated price list. Then with the Excel pivot table tools you can group and filter the data any way you please, effectively creating a price list report that views the latest information from CRM in a layout that best suits our purposes.

If you’re using CRM Online then direct SQL queries into the cloud database are not allowed for understandable reasons. Thankfully the Power Query component in Excel 2013 has recently been expanded to support connecting to CRM Online OData feeds. The method outlined in this blog post would therefore allow you to replicate the information of the aforementioned pivot table via an alternative data retrieval mechanism. There’s one caveat to be aware of, though: when I tested retrieving the required entities via OData (price list item, price list, product), I received an error message in the price list item entity query editor that stopped me from formatting the output. So, whether or not the approach would work in practice is something I’ll leave to other CRM bloggers to experiment and report back with.

Going Beyond Excel

Hey, now that you mentioned the word “report”, wouldn’t that also be one option for retrieving data from Dynamics CRM when the application’s user interface and customization tools fall short? Yes, that’s a great observation you made there, my inner voice / dear reader. Since all we’re really after in this case is a list of data from a couple of entities, it should not even be a very complex report to build. We should definitely create one!

Now, that of course assumes you already know how to build SSRS reports in the first place, meaning you’ve set up a workstation with the correct version of Visual Studio, installed the CRM report authoring extensions, gotten familiar with using FetchXML queries (in CRM Online that’s all you can use for reports) and learned the tricks of the trade when it comes to authoring SQL Server Reporting Services reports for not just your generic database but rather for usage inside the Dynamics CRM application. To use Microsoft’s terminology, this doesn’t really fall within the “self-service BI” category anymore, which is focused on using Excel add-ons to build reports on various corporate data sources. Rather you’ll start to to slip into the “pro developer” territory if you choose to take this route and fire up Visual Studio.

As with most things in the world of IT, custom CRM report creation is only difficult if you’ve never done it before – and don’t yet know the right search terms to use in finding the instructions. I’ve personally never been a professional SSRS report writer nor am I ever likely to graduate into being one, but I thought this sounded like a good exercise to freshen up the skills and build a simple FetchXML based report. So, that’s what I did and here it is:

Like I said, this is not a complex output from a report writing perspective. All I’ve done is pull in fields from the price list item entity (productpricelevel), added a multi value filter for selecting one or more price lists, enabled sorting the data based on any column and built a drill-down feature for opening up either the product, price list or price list item records by clicking on the report fields. This basically replicates the features that a native CRM view would offer us, with the added benefit that we could do whatever we want when it comes to data presentation by tweaking it further in Visual Studio.

If you want to get a report like this for your CRM environment, then feel free to grab the unmanaged solution file below. All it contains is the report rdl file and can be imported to both CRM 2011 and CRM 2013 organizations, no matter if it’s on-premises or CRM Online.

PriceListItemsReport_1_0_0_0.zip

Updating the Prices

So, now we have a way to view and export the price list information. If we discover prices that should be updated, then how do we deal with this? Sure, the report allows you to click on the Price column, open the price list item record, update it and save it. This is all fine and dandy for individual price changes, but how about a big bulk update operation like increasing prices for all products in EMEA countries’ price lists by 5%?

One approach is to simply use the data you’ve extracted from the existing price lists via Excel or report, then perform the necessary updates onto that data in Excel and import them back into CRM as price list items under a new price list. So, to update the price list “Retail 2013 UK” with new increased prices, you’d first add a blank new “Retail 2014 UK” price list record into CRM. Then in the Excel sheet containing the prices you’d change the name of the price list to reference the new record and start the Import Wizard to bring in new price list item records into CRM. There’s a tutorial available on how to import price lists in the official help documentation from Microsoft.

If you’d like to update existing items instead, then the default CRM application won’t provide you tools for this. Sure, for many other entities you could use the export/re-import feature and do a bulk update in Excel, but since the Price List Items are neither available in Advanced Find nor are their views customizable, you can’t get the data exported with the necessary columns. (Don’t forget that Connect suggestion I mentioned earlier if you’d wish to see this change in a future release.)

As is often the case, feature gaps in the standard Dynamics CRM application can be filled with ISV solutions. A product I’ve successfully used with customers who need the ability to better manage the pricing related information in CRM is Price List Utilities by Dynamics Professional Solutions. Available for every imaginable CRM version, it will allow you to perform export, import, copy, increment etc. operations on price list item records in bulk. They have a free demo solution available that you can install into your own CRM environment and see if this tool meets your needs for pricing data management in Dynamics CRM.