Reporting Revenue by Product Type in Microsoft Dynamics CRM

    businessman-report

    Reporting Revenue by Product Type in Microsoft Dynamics CRM

    Many businesses want to examine revenue for various product lines or item types such as units versus accessories separately. However, by default Dynamics CRM (Customer Relationship Management) uses Closed Opportunities when reporting revenue. The opportunity closing dialog allows the user to accept the system-calculated revenue from the opportunity or enter a different number. While this gives an organization some flexibility in choosing how to record the revenue from won opportunities, it does not allow for more fine-grained reporting by product type sold. In order to get reporting at this level of detail, you need to derive it off order products rather than opportunity. This article demonstrates how to create views and charts to get this information out of the system.

    Step 1: Create a New View from an Existing View

    Open Customizations and navigate to the Order Product entity. Select Views and choose the Default Public View of All Order Products. Select Save As and give your new view a descriptive name.

    Microsoft-Dynamics-CRM-Order-Products

    Step 2: Edit Filter Criteria

    Select Edit Filter Criteria to open the query window.

    Microsoft-Dynamics-CRM-Edit-Filter-Criteria

    Open the drop-down and scroll past Fields to the section called Related. Choose Order. In the next drop-down that appears below, choose the field Status and then set it Equal to Fulfilled;Invoiced. This will ensure your view is pulling revenue only from fulfilled or invoiced orders.

    Microsoft-Dynamics-CRM-Edit-Filter-Select-Products

    Microsoft-Dynamics-CRM-Order-Select-Values

    Microsoft-Dynamics-CRM-Order-Edit-Filter-Criteria

    Next, go back to the top of the window and hit Select to reveal another drop-down menu. Choose the related entity of Existing Product from this menu. It’s important that you use the drop-down menu at the top of the window, as this will allow you to select entities related to Opportunity Product. If you choose the drop-down below the Order query, it will give you options to choose entities related to Order instead. By using Existing Product in your query, you will be able to access fields on the Product entity related to the items in your Product Catalog.

    Microsoft-Dynamics-CRM-Existing-Product-Edit-Filter-Criteria

    Below Existing Product, choose the field Product Type from the drop-down menu and then set it Equal to whichever product type you want in your revenue report. The Product Types can be customized by editing the option set in the Product Type field on the Product form, or you can define additional fields to use in slicing your data. Once you have your query defined, hit Okay to save the query and return to the Edit Columns screen.

    Microsoft-Dynamics-CRM-Edit-Filter-Criteria-Existing-Product

    Step 3: Edit Columns

    On the Edit Columns screen, select the columns that you want to see in your list view by choosing Add Columns from the menu on the right. You can add fields from Order Product, Order, or Existing Product. Suggested columns to add from Order are: Customer, Name, and Date Fulfilled. From the Order Product: Existing Product, Price Per Unit, Quantity, and Extended Amount. From Existing Product, Product Type.

    Microsoft-Dynamics-CRM-View-Product-Sales

    Microsoft-Dynamics-CRM-View-Product-Sales-Type

    Step 4: Create a Chart

    Depending on how you want to view your data, you may want a list or a chart. In order to create a chart from your new view, navigate to Charts under Order Product and select New. Choose the view you just created under View used for chart preview. For the Legend Entries (Series), select an option such as Extended Amount or Quantity. For the Horizontal (Category) Axis, select the category by which you want to split the results, such as Customer (Order) or Date Fulfilled (Order). A suggested chart title will populate and a preview will display. Click Save & Close.

    Microsoft-Dynamics-CRM-Order-Product-Charts

    Microsoft-Dynamics-CRM-Chart-New

    Microsoft-Dynamics-CRM-Chart-Extended-Amount-Customer-Order

    Step 5: Create Additional Views and Charts

    To create additional views for different product groups, open the view you’ve created, choose Save As, and then modify the query by substituting a different selection following Product Type Equals. To create additional charts, you can either Save As or create New, depending on how similar the requirements are.

    Comparison Chart

    You can create a chart that compares revenue by product type side-by-side by omitting the Product Type parameter from your query and using Product Type as the Horizontal (Category) Axis of your chart.

    Microsoft-Dynamics-CRM-Chart-Extended-Amount-Product

    Step 6: Add Views and Charts to a Dashboard

    Dashboards are a great way to get a quick, up-to-date overview of the sales revenue status. To add a new dashboard, go to Sales, Dashboards, and select New. Choose a layout and select Create. Give the dashboard a name and click Save. Click List or Chart to add a component. For a list, choose Record Type: Order Products and one of the views you created. For a chart, select Order Products, a View, and a Chart. Save and close the dashboard to view the results. You can drill into charts or select an item from the list to go to that record.

    Microsoft-Dynamics-CRM-Add-Component-extended-amount

    Microsoft-Dynamics-CRM-Dashboards

    Microsoft-Dynamics-CRM-Product-Sales

    Microsoft-Dynamics-CRM-drilldown-sample

    I hope you have found this helpful in getting detailed, high-quality data out of your CRM system. If you have any questions about this or other Dynamics CRM functionality, leave a comment or contact us.

    Suggested

    Stuck on your project? We can help:

    Paul Arthur

    Paul Arthur

    Vice President of Solution Engineering, Paul is an experienced strategic business development professional. He is highly regarded for his ability to implement continuous process improvements to reduce costs, maximize profits and improve quality with increased throughput.

    CRM User Group Summit is Coming to St. Louis What Should Manufacturers Expect from ERP Standardization?

    Contact Us

    Categories

    see all

    Recent Posts