This chapter from Oracle CRM On Demand Reporting outlines the various pre-built on-demand sales reports available in Oracle CRM On Demand. Read on to find an overview of each kind of report, including an explanation of what sales information is provided in each report and how that information is presented.
Oracle CRM On Demand Reporting
Chapter 1, Getting Started with Answers On Demand
Oracle CRM On Demand Reporting, Chapter 1
Table of contents:
Getting started with Oracle CRM Answers On Demand
Pre-built CRM On Demand reports for sales
Customer activity and marketing reports in CRM On Demand
Explore the Prebuilt Reports
As you begin to think about the types of reports that you would like to build, the prebuilt reports in CRM On Demand can be a great resource to you. The prebuilt reports are an excellent source for reporting ideas as well as for examples of many of the different functions and views that you will want to include in some of your own reports. By running the prebuilt reports or opening them in Answers On Demand, you can learn a great deal about report design.
The rest of this chapter introduces the various prebuilt reports and identifies what these reports do and, perhaps more importantly, how they do what they do. One thing that you will undoubtedly notice about many of the prebuilt reports is that there is a distinct difference in the report when you run the report from the Reports tab and the design of what appears to be the same report when opened in Answers to examine the design. This is because these reports use dashboard prompts and run in a dashboard view. The preconfigured Dashboard reports visible on the Dashboard tab use many of these reports. Currently, you cannot edit standard dashboards for the Dashboard tab. You can, however, create custom public dashboards, as discussed in Chapter 13.
When you run a prebuilt report and see a separate view at the top of the window with one or more drop-down lists allowing you to filter the report data, this is likely a dashboard prompt that you will not see when you edit the same report in Answers On Demand.
A recurring theme that you should notice as you examine the prebuilt reports is simplicity. Most of these reports are quite simple in structure, and do not attempt to cram as much data as possible onto a single report. There is a clear purpose, or business question, that drives the report. When it comes to reporting, simple is almost always better. The natural trend seems to drive us in the other direction. This personal desire, or corporate directive, to do as much as possible with as few reports as possible may prove to be more difficult for you to overcome than the actual design and development of reports in general. Chapter 2 revisits this concept. For now, let us have a look at the prebuilt reports in CRM On Demand.
The Quick Lists reports are the most simple of the prebuilt reports library. Their design meets the common needs of many businesses to see a logically organized list of records.
Opportunities By Account
This report provides a list of sales opportunities organized by the accounts to which those opportunities are related. The report uses the Opportunities Reporting subject area. The layout is a simple table, but there are a few features worth mentioning. The Account Name, Opportunity Name, and User Name columns are all action links. I will discuss how to format action links in a future chapter, but if you like to figure things out on your own, it would be easy enough to dig into this report. This report also demonstrates the green-bar formatting in the Table view. Green-bar formatting emulates the old green-bar paper that was once popular for printing vast amounts of data. Every other line has light-green background shading.
Opportunities By Sales Stage
This report is quite similar to the Opportunities By Account report, and also uses the Opportunities Reporting subject area. It contains a simple Table view with action links on the Account Name, Opportunity Name, and User Name columns. One distinct difference is the use of system variables in the report filters. This report only displays records falling within the current fiscal year and quarter. You can see these filters in the Answers Define Criteria window, and I will discuss the use of the system variables beginning in Chapter 5. Notice, too, that an Active Filter view is on the report layout. This view displays all the filters affecting the data in the report.
Activities By Opportunity
The Activities By Opportunity report employs the Activities Reporting subject area. It too is a simple table, as most list reports are. This report employs the ActionLink class and green-bar styling as well. You will also notice that the report uses a filter to display only those activities which take place in the current fiscal year. This particular report makes a nice starting point for a report that provides some aggregated totals of activities. Remove the Subject and Date columns, add a metric column, and you have a completely different but quite useful report that calculates the number of activities by opportunity and type for each user.
Accounts By Sales Rep
The Accounts By Sales Rep report provides sales managers with a list of the accounts that their subordinates own. There are action links to the account record as well as the user record in the table. One interesting feature of this report is the method of limiting the data to the sales reps who report to the manager running the report. Note the filter on the Manager Email column. This session variable, used on User Email and Manager Email columns, compares the current user's email address to the email address listed in the filtered database column.
Contact Mailing List
Sorted by account and built from the Contacts Reporting subject area, this simple report pulls your contact's email, address, and phone number information into a table. Action links will take you to the detail record for an account or contact.
Clicking the Employee List link on the Reports Homepage actually runs the Employees and Managers List report and the Employees List report. Running the Employee List report using the link on the Reports Homepage provides a list of all the users and their managers in the top table. The table below that is the Employee List report and provides just a list of the users, without listing the managers for each user.
This report does not appear on the Reports Homepage in the Quick Lists section; rather, to find it, open Answers, click Open Analysis, and look in the Quick List Reports folder. You will find this report listed further down the Reports Homepage in the Service section.
The Pipeline Analysis reports provide a great starting place for many of your sales reports. Often, these reports need only minor changes to customize them for your business. This section provides a brief summary of each report and calls out some of the more interesting features. Unlike the Quick Lists reports, the Pipeline Analysis reports tend to employ some more visually stimulating layout elements.
The Pipeline Analysis report is fantastically simple yet extremely insightful. The report itself contains only two columns. The real magic happens on the report layout. Open this report and have a look at the views in use here. The column selector view, which I describe in detail in Chapter 13, enables the user to specify exactly which two columns of data to examine with the report. By enabling columns with the column selector, this report essentially provides 20 separate reports in one simple report. The chart view below the column selector changes dynamically when you select columns from the selector, as does the table below it.
This report uses the active Opportunities Reporting subject area. Unless you have a real business need for seeing this type of data in up-to-the-second real time, I recommend you build a report like this from the Opportunity History Analytics subject area.
Opportunity Revenue Analysis
The Opportunity Revenue Analysis report is very much like the Pipeline Analysis report, only in this report, the Revenue column is fixed, and you are able to select one of four columns with which to analyze the revenue amounts. Again, this is a real-time report, but is likely just as effective for this type of analysis if built on the Opportunity History subject area.
Pipeline Quality Analysis
The Pipeline Quality Analysis report is a nice example of using the column selector to allow the user to choose the metric examined in the report. This report offers a horizontal bar chart with the sales stages listed in the vertical axis. The horizontal axis dynamically changes to reflect the metric selected in the column selector. This report could easily be adapted for multiple sales stages. You could also enable the Sales Stage column in the column selector view to allow the user to select other ways to organize the data.
Team Pipeline Analysis
The Team Pipeline Analysis report is almost identical to the Pipeline Quality Analysis report, only the fixed value in this report is users. If you run this report and see no results, it may be by design. Open the report and examine the filters. Notice that the Manager Email column filters to match the current user. What that means is that if there are no users reporting to you in the database, then you will see no results on this report. That also means that each manager will see only those users that report to him or her. Notice also that there is a filter on the Reporting Level column. By including only those records where the reporting level is equal to zero, the results of this report are limited to just those users who report directly to the individual running the report.
Top 10 Opportunities
The Top 10 Opportunities report is rather simple. The layout includes a horizontal bar chart and a basic table with revenue and expected revenue. The RANK function on the Revenue column is what limits the data to the top ten. You will find more detail on the RANK function in Chapter 6. For now, I will just point out that by building off this report, you can very easily modify the filter on the ranked column with any number you like to create your own Top n Opportunities report.
Historical Pipeline Analysis
The Historical Pipeline Analysis report takes advantage of the Pipeline History Analytics subject area, which retains historical snapshots of data in order to provide this type of comparative analysis over time. The report itself is quite simple, with a bar chart comparing the metrics chosen in the column selector.
Historical Expected Revenue Quarterly Analysis
This report is essentially a copy of the Historical Pipeline Analysis report with the revenue columns removed from the column selector in favor of including only the Expected Revenue for the previous and current quarter. The column selector allows you to group the data in a number of ways, but only shows the expected revenue.
Historical Opportunity Revenue Quarterly Analysis
This report is identical to the Historical Expected Revenue Quarterly Analysis report, only with the Revenue column rather than Expected Revenue column.
Quarterly Closed Revenue Analysis
The Quarterly Closed Revenue Analysis report allows for the comparison of closed revenue from last quarter to the closed revenue of the current quarter. Again, it is very similar to the previous Pipeline Analysis reports, only the Sales Stage column is not available in the column selector. Because it includes only closed revenues, the only sales stage represented is Closed/Won, so there is no point in including the column in the selector.
Opportunity vs. Expected Revenue vs. Closed Revenue
This report takes your revenue, expected revenue, and closed revenue for each week of the quarter and plots them on a line graph. This is a nice example of another chart type that allows you to compare values visually. Since this report is concerned only with revenues in the current quarter, there is no need to build the report using the Pipeline History Analytics subject area. This report relies on the Opportunity History Analytics subject area.
Sales Stage History Analysis
The Sales Stage History Analysis report draws from the Sales Stage History subject area, as you might expect. The Sales Stage History subject area is a particularly useful Analytics subject area, especially if you are interested in how long sales opportunities are in a particular sales stage.
When you run the Sales Stage History Analysis report, you are actually running the Sales Stage History Analysis in the Sales Stage History Analytics folder rather than a report from the Pipeline Analytics folder. Looking in the Sales Stage History Analytics folder, you will notice that there are actually three reports there. There are no direct links to those other two reports from the Reports Homepage. These other two reports, Team Sales Stage History Analysis and Sales Stage History Detail, are actually targets of navigation links. Clicking a Sales Stage column value in the Sales Stage History report will navigate you to the Team Sales Stage History Analysis report. To see how this is accomplished, you can examine the column properties for the Sales Stage column. On the Column Format tab, you will see that the Value Interaction is "Navigate" with the target report identified in the Target field. The Sales Stage value that you click passes to the target report as a filter.
Open the Team Sales Stage History Analysis report and you will see that there are filters on Sales Stage and Date set equal to "is prompted." This instructs the report to look for values passed from the initiating report. Have a look at the column properties on the User Name field and you will discover that clicking a value in the User Name column of the Team Sales Stage History report navigates you to the Sales Stage History Detail report.
I explain adding interactivity, like navigation between reports, in detail in Chapter 16.
The Sales Effectiveness reports provide insight into your sales teams. With these reports, you can examine how sales teams are performing using a variety of different metrics. If your organization is using the sales modules of CRM On Demand, you will surely find some useful reports here that you can use to build some custom reports of your own.
Top Performers List
The Top Performers List report is a simple list report that shows the top 20 sales people and their total revenue, closed revenue, number of opportunities, and number of wins. The ranking is on the Revenue column, so it is possible that the number of opportunities, for instance, can be a small number but still show on this report if the revenue associated with those opportunities is large enough to be in the top 20. If you prefer to measure your sales people with number of opportunities or number of wins, you would need only to modify the filter so that the RANK function applies to one of the other metric columns.
Quarterly Sales Effectiveness Analysis
This report provides a quarterly breakdown of opportunity win rate, average deal size, or average sales cycle. This is another example of using a column selector to provide users control over what data they see. Suppose you need to see this type of information broken down by month. You could simply enable the Fiscal Qtr/Yr column in the column selector and add the Fiscal Mth/Yr column. This would enable users to select the time unit. Another option would be to replace the Fiscal Qtr/Yr column in the report and save the report as a new analysis.
Team Sales Effectiveness Analysis
The Team Sales Effectiveness Analysis report is another example of using the column selector view. If you are not seeing data on this report, it is likely due to the filter on the Manager Name column. This report presents an opportunity to provide a caution about the column selector view, however. Notice that the columns available here are the same in both selectors, and contain different value types. The Opportunity Loss Rate and Opportunity Win Rate columns are percentage values. The Average Deal Size column is a currency value. Finally, the Average Sales Cycle column reports a number of days. The following are a couple of things that, in my opinion, you should avoid for clarity of the report's purpose and data reported:
When you provide the same values in multiple column selector columns, your users may select the same value in each column, eliminating the comparative function of the report.
When you select the same column in both column selector fields, the chart becomes no more useful than a chart of a single value, and the data table repeats the same value in multiple columns.
When there are differing value formats, take caution with the type of chart you use to illustrate the selected data. For instance, if you compare deal size to sales cycle, which is a perfectly valid analysis, you run the risk of having such a large variance between the two data elements that the smaller elements practically disappear from the chart. Suppose you have an average deal size of $800,000 with an average sales cycle of 24 days. These two measurements side by side on a chart would hardly provide a helpful visual comparison.
Team Activity Analysis
This team report shows the number of open and closed activities for each employee reporting to the manager running the report. This report employs the Activity History Analytics subject area. This means that any activities created today will not appear on this report. Any activity that closes today would still show as open on this report. If up-to-the-minute accuracy is important to your analysis, build a report like this using the Activities Reporting subject area.
Team Win Rate Analysis
The Team Win Rate Analysis report provides managers with a quick summary of their team members' win or loss rate. The Win Rate and Loss Rate columns are metric columns available in the Opportunity History Analytics subject area. These columns do not appear as fields in the database. The win and loss rates are calculated and stored as metric columns in the analytics data warehouse. While it would be possible to calculate these values with the data from the database, the Analytics subject areas provide a number of precalculated metric columns for your reports.
Team Average Sales Cycle Analysis
The Team Average Sales Cycle Analysis report provides managers with a quick summary of their team members' average sales cycle or average deal size. Again, these columns are metric columns available in the Opportunity History subject area.
Continue to the next section: Customer activity and marketing reports in CRM On Demand
Download Chapter 1, Getting Started with Answers On Demand
Read other excerpts and download more sample chapters from our CRM and call center bookshelf
This was first published in November 2008