4

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.

Improve this question
2
  • Do you need all of the entries and the count in the same report? That would be hard even in straight SQL.
    – Mike Chale
    Sep 6, 2012 at 23:18
  • I don't need the entries themselves, just the summary count of unique MRN would do.
    – Jeremy Nottingham
    Sep 7, 2012 at 4:08

3 Answers 3

Reset to default
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

Improve this answer
1
  • Thanks for your answer. However, I can already create a report that groups referral entries by date by month. What I need is a way to count how many different values there are for the MRN field during that month. I'm getting the impression that this is impossible through reports.
    – Jeremy Nottingham
    Sep 8, 2012 at 6:05
1

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:

enter image description here

Improve this answer
5
  • Thanks for the response. However, this won't work for me, as I need to know how many unique MRNs appear in each month, and formula fields won't be able to determine that.
    – Jeremy Nottingham
    Sep 6, 2012 at 19:13
  • Assuming that Referrals are linked to Contacts where the MRN is (or something similar). If you did a Custom Report Type of Referrals with Contacts, you could then count the custom field on Contacts. Would that work? If so, I'll update my answer.
    – Mike Chale
    Sep 6, 2012 at 19:20
  • MRN is a text field associated with each Referral Entry. The Report Type is essentially just Referral Entries with no other objects.
    – Jeremy Nottingham
    Sep 6, 2012 at 19:25
  • I love what you've done here. However, MRN is a text field that has no relationship to Contact. So a Referral Entry on "First Test" Contact could be MRN of 1, 2, 3, 4, 5; likewise with "Second Test" Contact. You are summarizing how many different Contacts occur in a month, but I need to summarize how many unique MRN text values occur in a month. Thanks for your work!
    – Jeremy Nottingham
    Sep 13, 2012 at 3:54
  • So the MRN is on the referral itself? If so, then I have no idea.
    – Mike Chale
    Sep 13, 2012 at 10:21
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .