The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Sales Pipeline Report

Sales Pipeline Report

  • Comments 5

We used to have a long list of reports in MSCRM version 1.2. For MSCRM version 3.0 (v3), when we moved to SQL Reporting Services (SRS), many of these reports were consolidated and the number of reports that v3 shipped with was dramatically reduced. One of the reports became a consolidated version of many old reports – the Sales Pipeline Report. Though we lost the pretty pipeline to show the sales pipeline report when we moved away from Crystal Reports, we also incorporated interesting functionality using the power of SRS into this report. I’ve seen a few messages on the newsgroups asking about what happened to the old sales report where people could see the sales by each user etc. So here’s how one may slice and dice this report to view the sales pipeline of an organization.

There are multiple grouping areas on the report:

Group By Sales Process – this will show the Sales Processes that are active and have at least one opportunity with that sales process running on it. The opportunities that do not have a sales process running on them will be displayed under the “None” bucket.

Group By – The options here are

  • Sales User – This groups the Sales Pipeline by a user. So on the X-axis of the graph, one would see a list of sales users and on the Y-axis will be the revenue. The next grouping (Revenue) explains how the revenue is calculated. Drilling down into the detailed report (by clicking on the individual bar in the bar chart) would display a detailed opportunities list that combines to make up the total revenue for the user. Clicking on the line items of this detailed report would open the actual opportunity. If any changes are made to the opportunity, refreshing the report will update the report to incorporate these changes. Each drill down detailed report displays the opportunity name, sales stage, probability to close, revenue (calculated based on the “Revenue” selection as explained below) and the owner. This is true for all the detailed reports mentioned below.
  • Sales Territory – This would become useful if the salespeople are organized by Sales Territories. In that case this would enable the sales managers/VPs to see the pipeline based on the territories. Since each sales user can have one territory, this report would group the revenues of those sales people together to give a consolidated territories view. Drilling down into the detailed report shows the opportunities grouped by sales territories.
  • Customer Territory – This would become useful if the opportunities have potential customers that already are “Accounts” in CRM and those accounts have Territories defined on them (Territories can be specified only for accounts in the “Details” tab of an account). Each bar is also a drill down detailed report that can be opened by clicking on the bar.
  • Date – This displays the sales pipeline report by the estimated close date of the opportunities. Each bar is a drilled down detailed report.
  • Rating – This displays the sales pipeline report based on the rating of the opportunity. Opportunity ratings are customizable through the Customization area. If the ratings are customized, the new (customized) names would show up in this report. Each bar is also a drill down detailed report.
  • Sales Stage – Sales stage grouped report would be useful if the opportunities have Sales Processes defined on them and the “Grouped by sales process” has a sales process selected. In that case, the report will display the opportunity grouping based on the sales stages of the selected sales process. Each bar is also a drill down detailed report.

Revenue – The options here are

  • Estimated Revenue – This is the revenue that is displayed on the Opportunity detail form as the revenue amount. The revenue may either be calculated or user provided. When this option is selected, all the drill down detailed reports (mentioned above in the “Group by” area) and the bar chart y-axes will display the Estimated Revenue amount.
  • Weighted Revenue – This is calculated as [(Estimated Revenue * Probability to Close)/100]. When this option is selected, all the drill down detailed reports and the bar chart y-axes will display the Weighted Revenue amount.

Here are two sample screenshots

Grouped by Rating and using Estimated revenue:

Same set of opportunities Grouped by Weighted Revenue

Overall, the sales pipeline report is a complex report that could help slice and dice your data in different ways. There may be some reasons why this report works or doesn’t work for you and I’d love to hear them.

Manisha Powar

  • I’m glad you brought this report up because I’ve had nothing but complaints about it since upgrading to 3.0. Here’s the problems our staff have experienced:

    First, when they group by Sales User and five or six users appear on the chart, no totals appear for the bars on the left and right ends, just in the middle. Since the detail reports don’t contain a total either, they have to manually add up all the individual amounts to get the two missing totals.

    I also had complaints that there’s no obvious way to tell that you can drill down into information on the totals as well as the detail reports. Worse, if the report is filtered by a specific month and a salesperson had a hard time that month, his or her graph may only be a few pixels high. That means our users have to zoom way in on the report in order to click the bar. Everyone expected to be able to click on a salesperson’s name to get the detail, but they can’t.

    Our users have also said that the detail report isn’t useful because it doesn’t show the name of the company the opportunity is for. All they have to go on is the Topic field shown in the opportunity column. As the salespeople don't understand the purpose of Topic as they’re already filling out the Customer and Opportunity Type fields, what’s entered in it tends to be useless on a report.

    Finally, they're frustrated that they have to first print the totals, then click on each salesperson and print each of those reports. It could take more than a dozen clicks and nearly as many pieces of paper to get what could fit on a single sheet. They expected to both see and be able to print the totals and details at the same time.

    They’re all coming to me for answers, so any help you can provide would make my life a lot easier.

  • Hi,

    The report's great, but there is one thing that is bothering me about it: is it possible to somehow make the report sorted by the sales stage and not by alphabetical order?

    Thanks,

    Or

  • IS there a way to place the quotas on teh port and show for each sales person not only sales but compare it to the quota?

  • My apologies for not coming back to check the comments. To Ryan and Orca - I have collected the feedback and I will put it for the future revisions.

    Eli - it is possible to edit all built-in reports in the SQL Reporting Services editor in Visual Studio and add any MSCRM fields that are accessible through filtered views. We don't have a quota+pipeline report. that is a good feedback I can take back to the feature team. Thank you for your comments and feedback!

  • We encountered the same problems regarding some value totals not appearing on the coloured bands, and not sorted by Sales Stage. So we edited the Sales Pipeline Report in Visual Studio to correct the 2 issues.

    I am writing an article on how to do this in the hope that partners can resolve this for their customers.

    http://shieldwulf.blogspot.com/2008/06/tweaking-sales-pipeline-report-in.html

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post