How to pull a value when a field equals a value - Crystal 8.5

[envirodata]

In Crystal I'm struggling with how to design a report that will pull from a field when the field is a particular value. More specifically, I'm trying to find the percentage of gifts that came in from the no. of people solicited. The problem is that a person could have received up to 5 solicitations.

For example, I have a list of gifts made in June and each gift is assigned an appeal and a package. The appeal for each of these gifts is June 2008 and the package will be 1st, 2nd, 3rd, 4th, or 5th based on which appeal they responded to. I also have no. solicited next to each package. What I want is the number of total gifts that came in as a percentage of just the 1st notice (which is a true count of people not letters sent). So, how do I find this percentage of gifts received based on no. solicited in package 1st notice but exclude (or even just hide the results) of other notices.

Let me type some example data rows to make this clearer:

Gift Date Appeal ID Package ID Package No. Solicited
6/1/08 R0608 1st Package 10
6/1/08 R0608 1st Package 10
6/1/08 R0608 2nd Package 5
6/1/08 R0608 3rd Package 4

So, I would total the number of gifts. Then I would like to find the response rate as total number of gifts/1st package.

So, in this example, it would be 4/10 or 40%. And to complicate things more, I would actually like to take the no. solicited from 2 packages on some months(so, the total of no. solicted in package 1A and in package 1B but exclude other packages).

Thank you for any guidance you may have in setting this up.

 

[lbass]

You need to have a group on appeal ID and then also go to report->sort records and sort by package ID. Please tell me exactly what you have done and show a sample of the results.

-LB

 

[envirodata]

This is what I did:

1. In the Field Explorer, created a new running total field.
2. Named the running total field 1Stor50.
3. Summarized on the no. solicited formula field, the formula is: tonumber({GfApls_1ApApP_1.GfApls_1ApApP_1_No_solicited})
4. Evaluated using a formula: (
onfirstrecord or
{GfApls_1.GfApls_1_Appeal_ID} <> previous({GfApls_1.GfApls_1_Appeal_ID}) or
{GfApls_1ApApP_1.GfApls_1ApApP_1_Package} <> previous({GfApls_1.GfApls_1_Appeal_ID})
) and
{GfApls_1ApApP_1.GfApls_1ApApP_1_Package} in ["1st Notice","50+ Notice"]
5. Reset on change of group: Group #1 Name: GfApls_1.GfApls_1_Appeal_ID
6. Then, created a formula: count({GfApls_1.GfApls_1_Appeal_ID},{GfApls_1.GfApls_1_Appeal_ID})% {#1Stor50}
7. Then, inserted this as the footer.
8. When I try to sort the report, it doesn't allow me. I chose Report - sort records. Within the box, it has fields to the left and sort fields to the right - these include the Group #1 GfApls_1.GfApls_1_Appeal_ID - A and below that Group #2 GfApls_1ApApP_1.GfApls_1ApApP_1_Package - A. (I added this group earlier but can remove it if it's messing things up). Everything is greyed out thus not allowing me to add or remove fields from here.

9. The result is 1.53% in the group footer #1.

 

[lbass]

Please show the detail data that is resulting in the 1.53% so I can troubleshoot. Also place a copy of the running total in the detail section so we can see how it is accumulating.

-LB

 

[envirodata]

When I looked at the results - I saw that gifts were repeating themselves. When I looked further, I see that this field GfApls_1ApApP_1.GfApls_1ApApP_1_Package will repeat for every gift with every appeal (regardless if that package was the one assigned to that gift. I found that I had to use this field {GfPks_1.GfPks_1_Package_ID} to get just the gifts assigned to the particular package. However, the no. solicited field is attached to the former field. Meaning if I want to get a count of no. solicited per package - {GfApls_1ApApP_1.GfApls_1ApApP_1_No_solicited} I have to summarize by the former field but this is duplicating gifts. Actually both ways are duplicating gifts - but just more when done the former way.

I'll attach both reports. Thank you.

http://www.box.net/shared/lqitygogsw

 

[nDAa]

Is there anyway that i could summary data with Crystal report as below
Data sort by : Supp/Site/Due/Chq/FC. The result would be :


Chq# FC# Chq_Due Chq_Amt PO# PO_Amt
------------------------------------------------------------ 13917 : ABC Co,.LTD
Site A65 : Landscape

0000334 FC487070 19/07/2005 1,605 PO494128 1,605

Summary by : 19/07/2005 1,605

6880865 FC501179 24/01/2007 26,981 PO492418 8,560
PO492337 4,815
Summary by :24/01/2007 26,981

Summary by : A65 Landscape 28,586


Site A77 : Parking

0090944 FC499014 14/09/2007 3,959 PO496087 963
PO496129 2,996
0127250 FC49C011 14/09/2007 5,350 PO499103 5,350

Summary by : 14/09/2007 9,309

0012526 FC501171 24/01/2008 3,745 PO495033 3,745

Summary by : 24/01/2008 3,745

Summary by : A77 Parking 13,054


Summary by : 13914 ABC Co.,LTD 41,640



Any help is greatly appreciated

 

[lbass]

nDAa--Please start a new thread.

envirodata,

Neither report shows the field that is causing the repeating. You need to add the other ID fields that will help determine how the rt needs to be set up.

-LB

 

[envirodata]

LB - I'm sorry, I'm not sure what you mean, I do believe that I showed all of the ID fields that I exported from the database - do you mean that I should export more fields? I showed all the fields that I exported - the only remaining fields in the field explorer are the link and key fields - do you mean any of these? Thank you.

 

[lbass]

I don't know, but try them.

-LB

 

[simplicity30]

I am using crystal reports v9 for Peachtree I am trying to write a customized balance sheet. i have gotten to the point of grouping the the id but I don't know how to write the formula to get monthly totals. every time i try I just get the current balances. help my boss is on my back

 

[lbass]

simplicity30--please start a new thread. Your topic is unrelated to the current thread. When you do, show the formula you tried for the monthly totals.

-LB