Come follow me on Twitter!
https://twitter.com/CRMOD_Reports
May 18, 2009
February 27, 2009
Filtering based on the results in another column
Suppose you are performing an account analysis and only want to list the accounts where the number of contacts is greater than the number of activities on the account (i.e., neglected accounts).
In our report we will have the Account Name, Number of Contacts, and Number of Activities columns built on the Accounts subject area.
First, copy the Analytic Field Reference of the Number of Activities column to your clipboard. Do this by clicking the Formula (fx) button on the column and highlighting the formula and pressing Ctrl-C. Close the window.
To add the filter, click the Filter button on the Number of Contacts column and then click the Advanced button. Select Convert this filter to SQL. You will see the Analytic Field Reference followed by the equal sign in the SQL window that appears. now, simply replace the = with > and paste in the Number of Activities reference. The resulting formula should look like this:
"Account Metrics"."# of Contacts" > "Account Metrics"."# of Activities"
Click OK to add the filter to the report. Now your report will only include the accounts where there are more contacts than activities. Now that you know which accounts are being neglected, start making some phone calls!
In our report we will have the Account Name, Number of Contacts, and Number of Activities columns built on the Accounts subject area.
First, copy the Analytic Field Reference of the Number of Activities column to your clipboard. Do this by clicking the Formula (fx) button on the column and highlighting the formula and pressing Ctrl-C. Close the window.
To add the filter, click the Filter button on the Number of Contacts column and then click the Advanced button. Select Convert this filter to SQL. You will see the Analytic Field Reference followed by the equal sign in the SQL window that appears. now, simply replace the = with > and paste in the Number of Activities reference. The resulting formula should look like this:
"Account Metrics"."# of Contacts" > "Account Metrics"."# of Activities"
Click OK to add the filter to the report. Now your report will only include the accounts where there are more contacts than activities. Now that you know which accounts are being neglected, start making some phone calls!
February 1, 2009
Prebuilt Reports
For the past few I have been on the road working with customers. This is my excuse for the delay in posting more to the blog, but something helpful has come from it.
One of the best practices that I always offer my customers is to examine the pre-built reports for reports that may be useful with no modification or very little modification. Review the pre-built reports for ideas on how you want to present data in your custom reports. Review pre-built report designs to determine how they were configured to do what they do. Here's the problem my customers are running up against, though... they have not imported any data into their On Demand instances yet, so the pre-built reports are returning no data.
I have documented each of the pre-built reports with a screen shot and have loaded those in the library on the Intelenex Web site. This makes a nice reference for reviewing the existing reports, leading discussions with stakeholders to discover their reporting needs, or to just get some ideas for the types of reports you want for your implementation.
I will continue to post materials on the Intelenex Web site for my blog reader's convenience and encourage you to visit often. http://www.intelenex.com/solutions/analytics.asp
I have documented each of the pre-built reports with a screen shot and have loaded those in the library on the Intelenex Web site. This makes a nice reference for reviewing the existing reports, leading discussions with stakeholders to discover their reporting needs, or to just get some ideas for the types of reports you want for your implementation.
I will continue to post materials on the Intelenex Web site for my blog reader's convenience and encourage you to visit often. http://www.intelenex.com/solutions/analytics.asp
January 10, 2009
Aggregating Percentages
Percentages are a common element in reports, but offer some unique challenges when summarizing data across multiple rows. Let's start with an example.
If we aggregate these values with a SUM aggregation rule on Quota and Revenue and an AVG aggregation rule on Quota, we get the following result.
It is at this point that most of us call it a day and assume our report is delivering accurate information - but there is a problem here. The Average of the averages (74%, 85.8% and 90%) is in fact 83.27%, but that is not the calculation we are really wanting here. What this report should be reporting is the overall % of Quota across all rows... in other words, we want the percentage of $950k that our closed revenue ($812.5k) represents. That value is actually 85.53%.
I have experimented with aggregation rules, pivot table settings, and calculated items and have found only one elegant solution to this problem. The key is in the formula for the percentage. One would naturally assume that the standard (simplified) formula of ((Closed Revenue / Quota) * 100) would do the trick... it it does if you are not going to aggregate the data.
Change the formula to ((SUM(Closed Revenue) / SUM(Quota)) * 100 ) and the aggregation will change to show the correct overall calculation while the individual rows continue to show the correct calculation for each user.
Here I show both versions side-by-side for comparison.
In essence, we have added the aggregation to the formula directly, and then used the pivot table to parse the data by user. This is counter to what you probably would typically do, which is to rely on the pivot table to provide the aggregation. Just remember, when you ask a table to show you the average across rows for a column containing percentages, you get just that... the average of the percentages rather than a calculation of the overall percentage.
NOTE: Thanks goes to Erin W. for presenting me with this challenge. If you have reporting questions that you would like to see addressed here, please send them my way!
If we aggregate these values with a SUM aggregation rule on Quota and Revenue and an AVG aggregation rule on Quota, we get the following result.
It is at this point that most of us call it a day and assume our report is delivering accurate information - but there is a problem here. The Average of the averages (74%, 85.8% and 90%) is in fact 83.27%, but that is not the calculation we are really wanting here. What this report should be reporting is the overall % of Quota across all rows... in other words, we want the percentage of $950k that our closed revenue ($812.5k) represents. That value is actually 85.53%.
I have experimented with aggregation rules, pivot table settings, and calculated items and have found only one elegant solution to this problem. The key is in the formula for the percentage. One would naturally assume that the standard (simplified) formula of ((Closed Revenue / Quota) * 100) would do the trick... it it does if you are not going to aggregate the data.
Change the formula to ((SUM(Closed Revenue) / SUM(Quota)) * 100 ) and the aggregation will change to show the correct overall calculation while the individual rows continue to show the correct calculation for each user.
Here I show both versions side-by-side for comparison.
In essence, we have added the aggregation to the formula directly, and then used the pivot table to parse the data by user. This is counter to what you probably would typically do, which is to rely on the pivot table to provide the aggregation. Just remember, when you ask a table to show you the average across rows for a column containing percentages, you get just that... the average of the percentages rather than a calculation of the overall percentage.
NOTE: Thanks goes to Erin W. for presenting me with this challenge. If you have reporting questions that you would like to see addressed here, please send them my way!
December 20, 2008
Reporting Progress Against Quota
Something that has come up time and time again is the need to report using sales quota information. The built-in forecasting module provides some visibility into the sales vs quota question, but provides only a single simple explanation of current quota, current sales, and the percentage of quota represented by current sales.
The quota mechanism in CRM On Demand is very open and flexible (security-wise) which makes it quite easy for salespeople to manage their own quotas. Unfortunately, many companies do not empower their sales people to set their own quotas, so this becomes a potential issue with the forecasting and quota tracking.
Adding to the challenge is the fact that On Demand currently does not expose the Quota table to analytics. Of course, not every company manages quotas exactly the same way, either, so a custom solution is often required.
When designing a custom solution for tracking and reporting quotas, consider the following:
A basic example of a custom object configuration for Quota involves creating columns/fields to track ownership of the quota, the time period for the quota record, the quota amount, and perhaps a name for the quota (as name is going to be a required field anyway).
Now, with this setup, you are able to create a quota record for each user for each time period. A set of reports built on the Custom Object and Opportunities subject area should provide all of the data you need to track opportunities and quotas.
I hope this gets you started on some quota tracking reports.
The quota mechanism in CRM On Demand is very open and flexible (security-wise) which makes it quite easy for salespeople to manage their own quotas. Unfortunately, many companies do not empower their sales people to set their own quotas, so this becomes a potential issue with the forecasting and quota tracking.
Adding to the challenge is the fact that On Demand currently does not expose the Quota table to analytics. Of course, not every company manages quotas exactly the same way, either, so a custom solution is often required.
When designing a custom solution for tracking and reporting quotas, consider the following:
- Do users have multiple quotas or a single quote for each time period?
- Are user quotas defined by month, quarter, year, or some other time period?
- Do users have separate quotas for different products or opportunity types?
- Does the user quota amount change from one period to the next?
- Who will maintain the quotas?
- Are you interested in historical quota data?
A basic example of a custom object configuration for Quota involves creating columns/fields to track ownership of the quota, the time period for the quota record, the quota amount, and perhaps a name for the quota (as name is going to be a required field anyway).
Now, with this setup, you are able to create a quota record for each user for each time period. A set of reports built on the Custom Object and Opportunities subject area should provide all of the data you need to track opportunities and quotas.
I hope this gets you started on some quota tracking reports.
December 12, 2008
Tip #1: Buy the Book
I know it seems a bit self-serving to advise my blog readers to buy my book, but I can honestly say to you that I really see very little benefit personally from the purchase of the book - no one ever got rich writing a computer book that not only focuses on a single technology, not only focuses on a single application, but is so laser-focused that it documents a single element within a single software application.
Here's the deal... I wrote the book for me. I needed a desk reference that I could rely on as I developed reports for my customers. I had long dreamed of authoring a book, but never imagined that I would eventually find it easier to write a book than to organize the hundreds of notes littering my desk and the hundreds more lost or never scribbled down. That is exactly how it happened... I went from trying to remember to trying to keep up with poorly penned notes to trying to create a Wiki site on my hard drive to applying order to the madness and write a book.
So anyway, my motivation for recommending that you purchase the book is that I am interested in helping people develop their reports, and by golly, the book is helpful! Be it a simple report or something that you are not even sure is possible, just having the descriptions of functions, explanation of features, and tables of codes is worth the purchase price.
You can find more information on the book at your favorite online bookstore by searching on author: Michael D. Lairson, title: Oracle CRM On Demand Reporting, or ISBN: 0071593047.
Going forward, my intention for this blog is to offer some tips, teach you a few tricks, and warn you of the occasional trap to help you with your report design and development. If you have any specific suggestions for a post on this blog, please email me and let me know.
mike@michaellairson.com
Here's the deal... I wrote the book for me. I needed a desk reference that I could rely on as I developed reports for my customers. I had long dreamed of authoring a book, but never imagined that I would eventually find it easier to write a book than to organize the hundreds of notes littering my desk and the hundreds more lost or never scribbled down. That is exactly how it happened... I went from trying to remember to trying to keep up with poorly penned notes to trying to create a Wiki site on my hard drive to applying order to the madness and write a book.
So anyway, my motivation for recommending that you purchase the book is that I am interested in helping people develop their reports, and by golly, the book is helpful! Be it a simple report or something that you are not even sure is possible, just having the descriptions of functions, explanation of features, and tables of codes is worth the purchase price.
You can find more information on the book at your favorite online bookstore by searching on author: Michael D. Lairson, title: Oracle CRM On Demand Reporting, or ISBN: 0071593047.
Going forward, my intention for this blog is to offer some tips, teach you a few tricks, and warn you of the occasional trap to help you with your report design and development. If you have any specific suggestions for a post on this blog, please email me and let me know.
mike@michaellairson.com
Subscribe to:
Posts (Atom)
Posts
