Need count of unique field values by month

Question

I have a table called Referral Entry, which records a specific referral of one patient from one physician to another physician. Each record has a MRN value, which indicates the patient number. Since a patient may be referred any number of times, several Referral Entry records will have the same MRN.

In a report, I would like to display the Count of Unique MRNs in a month. Which means that if MRN 12345 had two referrals in January, and 5 referrals in February, it would count as 1 Unique MRN in January, and 1 Unique MRN in February. The report will be grouped by Referring Physician, Service Physician, or Department, all information on the Referral Entry object. Here's the most basic view:

Jan 2011 Unique Count: 4
MRN     Date
1       1/5/2011
1       1/5/2011
2       1/7/2011
2       1/7/2011
3       1/10/2011
4       1/15/2011

Feb 2011 Unique Count: 3                
MRN     Date
1       2/5/2011
1       2/5/2011
2       2/9/2011
3       2/15/2011
3       2/15/2011

One reason for using a report is that the dataset is pretty large. In some cases, we may need summary totals on 2 million records in one display table. Since we need to be able to summarize based on several criteria, it's impractical to use summary database records. If you have any other ideas on how we could create this data view, please throw them out there. Thanks in advance!

Edit: The MRN field is a text field on Referral Entry. For the purposes of this question, Referral Entry is essentially a standalone object.

Answer 1

You could create a formula field that output text and took in your date and formatted it down to "Month/Year". Then you could group on that in a summary report, and if you didn't care about the details you could hide them to only show the summary rows.

MONTH(Date_Field__c) + '/' + YEAR(Date_Field__c)

In addition, if you were looking for strings instead of numbers for months:

CASE(MONTH(Date_Field__c),
1, "January",
2, "February",
3, "March", 
4, "April", 
5, "May", 
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
"None")

Salesforce - Useful Advanced Formulas

Answer 2

The best approach I've found is using a custom field to summarize on, as outlined on one of their blogs - http://blogs.salesforce.com/analytics/2006/08/the_power_of_on.html. There is also a request to allow this in reporting.

Once the custom field is present, you can create a summary report and group the date field by calendar month and then summarize the number of ones present.

Update:

I've tried out the following in my dev org:

1. Created a custom object, Referral Entry (RE)
2. Added a Master-Detail from the RE to Accounts, called Doctor
3. Added a Lookup from the RE to Contacts, called Patient
4. Added a Referral Date field on RE
5. Added a One field (see link above) on the Contact
6. Created a Custom Report Type (CRT), Patients with Referrals
   1. Based on Contacts
   2. With Referral Entries

I then created a new report on the CRT, Patients with Referrals.

1. Changed to Summary Format
2. Grouped by Referral Date and then grouped the dates by Calendar Month
3. Had three columns, Full Name, Referral Entry Name (the referral id), MRN and Contact.One__c
4. Summarized Contact.One__c, choosing Sum.

The result was:

Answer 3

The SOQL documentation discusses GROUP BY and GROUP BY CUBE, which you could possibly use to get count subtotals by month and MRN.

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_groupby_cube.htm

AggregateResult queries are pretty powerful.