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.
The latest version of PowerQuery (2.11.3625.144) appears to fix the error using OData to connect to the ProductPriceLevelSet in CRM Online
Hi Jukka,
I stumble upon a nice little problem that look like limitation. I do not know how to overcome.
I want to create 1 Products launch campaign with 3 sub campaigns title Pre-Launch, Lauch, Post launch. Each with their specific price list
The potential Customer will specify a selection on a landing page. In a campaign response, I would like to show the product and the price from the specific price list included in the campaign. I know the name of the campaign will follow the response. But if I have 5 products, it will not tell the price of the item selected without opening the campaign, the price list, then checking the item to finding the price. This is time consuming in a grand scale. Since I will have the same product with different price in time. I may have a lot of error too.
I though of a sub-grid like the Opportunities product but I ran into problem there too.
So how do I connect the dot between Response and the price list Item. Am I boxing with the shadows of the elusive price item?
Your advice is most welcome
Joan
Joan, since the price list items are such a locked down entity in Dynamics CRM, I’m not sure if there’s any creative way to be discovered that would allow showing the price information in the required context without some custom plugins. If you want to do it without the need to write C# code, then a formula based solution like North52 Business Process Activities might be an option to consider here. There’s a bit of a learning curve there, but copying values from one entity to another shouldn’t be an overly complex formula to create.
Create an on-demand workflow on the Price List Item entity that updates List Price field on the related Product form. Select any Price List Item view and run the on-demand workflow just created for all Products. Then, create a custom Product view that shows Cost and List Price. You can export to Excel if needed. Took me a while to figure it out but it works like a charm!
Hello, i have a customer who has about 19 000 products priced in 3 currencies !!!
He has new pricing for the same products each month(some prices get updated & remaining stays as it is), I have suggested that we need to create a new price list for each month.
As a result, the pricelist & pricelistitems tables will handle a lot of records !!!
Please advice if this approach is safe to use
Best Regards
Ali, in terms of whether CRM can handle millions of records in the database in general, it should scale to large volumes – especially if they mostly just sit there in the DB. Now, as for the usability of the OoB price lists feature with a high number of records, that’s something you’ll need to validate with a representative sample data set.
Due to the numerous limitations in how the standard product / price list / price list item can be presented in the CRM UI (and the hardcoded business logic in them), often people end up building custom entities and plugins to replace the OoB features. If the standard feature looks like a good fit for the business requirements, then feel free to go for it of course, but just don’t assume that you could perform similar customization and extension for these entities as you could for custom entities.
Is there a way to add country based prices to a pricelist item and conditionally retrieve the price from the column based on a filter. For eg. I have only 3 priceists in 3 different currencies. But within 1 pricelist say, I have 5prices for each item for 5 different countries. I want to avoid building so many pricelists as it will eat up our storage and will lead to performance issues. If the user specifies the country at Quote as USA, the quoteproduct should fill the price from Price(USA) field, or if canada it should take from Price(Canada). Is this achievable?
Sameera, I don’t quite understand what the number of Price Lists has to do with storage or performance in your scenario. If you need to have the different price variations for the 5 countries stored in CRM database anyway as Price List Items, then having 5 different Price Lists for them is not going to really consume any more space than having the country names in a “Country” custom entity, for example. I’d recommend that you would look at automating the selection of the Price List based on the chosen country on the Quote level, rather than trying to develop something that happens on the Price List Item level.