Report Design: Select First Field

Print Page | Close Window

Select First Field

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17315
Printed Date: 01 May 2024 at 5:28pm

Topic: Select First Field

Posted By: dirtypotato
Subject: Select First Field
Date Posted: 14 Aug 2012 at 8:04am

So i am trying to show only one Payor for a service a client received. Most clients have more than 1 payor, and some payors may be expired. Here is my example:

ClientID   Service   Service Date Payor
1        IZ HPV 6/02/11 Blue Cross
1        IZ HPV 6/02/11 FPEP
1        IZ HPV 6/02/11 No Effective Payor

So basically at the service date the client had 3 payors but only 2 of them were effective (Blue Cross and FPEP). I only want to show one effective payor, or if all three were not effective and shown as "No Effective Payor", i want it to only show "No Effective Payor" once.

like this:
ClientID   Service   Service Date Payor
1        IZ HPV 6/02/11 Blue Cross

I've tried using the Minimum formula but it just goes through and repeats Blue Cross 3 times because it starts with the letter b.

-------------
Nate

Replies:

Posted By: hilfy
Date Posted: 15 Aug 2012 at 4:43am

Assuming you're grouping on ClientID, you could try this:

1. Create a formula that will combine any fields that you need to sort the payor you want to display to the top of the payor list for each client.

2. Sort on the formula you just created.

3. Put the payor data in the Client group header field.

This way you will only see the "first" payor for each client even though all of the payors are actually in the returned dataset. Let me know if you have to do any summaries in your report - there's a way to prevent the inflated numbers you'll get in summaries.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: dirtypotato
Date Posted: 15 Aug 2012 at 5:50am

Thanks for the advice! I am definitely in need of doing a summary of only the number of vaccines shown in the groups, but every time i do a count on Service or sum on Price, it does it for all records. How do i only get it to look at the results in the groups?

-------------
Nate

Posted By: hilfy
Date Posted: 15 Aug 2012 at 6:06am

You'll create running totals for these.

Field to Summarize: Whichever field you need to count or sum.

Evaluate for every record

Reset on change of: Payor group

Even though you're only displaying one payor, per client, this should get you the values that you'll need. Because they're running totals, you'll need to display them in a group footer section (either client or payor) in order to get the correct numbers.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Print Page | Close Window