Crystal Reports formula

Hello,

I need to do a distinct sum of participants for the meetings which ordered food and/or drinks. I have ResourceTypeName field stating "Food" or "Beverages" resource. Meeting can have more that one line for food and drinks but I need to count participants only once. I can not figure out how to put the calculation correctly.
Galina BesselyanovaSenior Software Developer/EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
One more thing: I can not use subreports for this query because it is an outside report and I have very limited ability to modify it. So the formula has to come from withing the report as I have it.
mlmccCommented:
Does the report have groups?

Have you tried using

DistinctCount({ParticipantField})

mlmcc
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Yes, it has groups. Participants is a number (like 60 or 20) for each booking and each booking can have few food lines in it. I group by department and count how many bookings each department has.  I can not distinctcount participants. I have to sum them so it will have a number of participants for specific department who ordered food.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

mlmccCommented:
Try this then

Add a formula

If {OrderFoodField} then
     1
Else
     0

Try summing that up.  

Replace OrderFoodField with whatever test you use to determine they ordered food.

Can you do a distinctCount for the group or are there participants who don't order food?

mlmcc
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Yes, there are participants who do not order food. I need a sum number of participants not count of them. For example I have 5 bookings with 2000 members attended for internal department. booking1-500 members;booking2-150;booking3-100;booking4-250 and booking5-1000. Out of this meetings  only 3  who ordered food (like booking1,3,4).  The number I need is 500+100+250=850. If I will distinctcount them I will not get the right number. And each booking can have more than 1 line for food. So I can not just grab the value on the food line.
mlmccCommented:
WHat data do you have that gives the member info and food info?  Would a member be in more than 1 food line?

mlmcc
James0628Commented:
If I understand the situation, you could create a formula like this:

if {ResourceTypeName field} = "Food" then
  {Participants field}
else
  0

 Put that formula in the detail section and create a summary on it.  You can remove it from the detail section after you create the summary.

 You'd use a similar formula for "Beverages".

 James
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Each booking can have several lines such as "Space",  "Staff" etc. Each booking can have more than one line for food and beverages. So if I will put it in the detail section it will count all lines for this booking for the food and I need only one line for the food per booking.
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
BookingID      DepartmentID       ResourceTypeName      ParticipantsNumber
1787                     44                             Staff                                          90
1787                       44                               Beverages                                  90
1787                       44                                Food                                         90
1787                       44                               Food                                          90
1787                       44                                Food                                          90
1787                       44                               Space                                        90
1787                       44                               Sales Tax                                  90
1787                       44                               Misc.                                         90
1787                       44                              Space                                        90

This is a sample of the booking. I need to pick up only one line for the food or Beverages and count only once. So it will be only number 90.
mlmccCommented:
That must be a simplified data example because you could solve the issue though duplicate records.

How about using MAX as the summary with it grouped by department and booking id

FOrmula like - IsFoodBeverage
If {ResourceTypeName} IN ['Beverages', 'Food'] then
    {ParticipantsNumber}
Else
    0

The overall total would have to be a formula like

WHilePrintingRecords;
Global NumberaVar Total_Count;
TotalCount := TotalCount + Max({@IsFoodBeverage},{DeptID});
''

That would be in the Group2 footer assuming it is grouped by Booking ID then Department.

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
How about a running total?  Assuming that the report is sorted by BookingID, create a running total on ParticipantsNumber, and in the Evaluate section, select "On change of field" BookingID.  Then ParticipantsNumber should only be added once for each BookingID.

 James
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Thank you very much to both of you. I will try to check all of your suggestions.
James0628Commented:
I just realized that I forgot the "Food" and "Beverages" part.  You could use a formula like the one I posted earlier

if {ResourceTypeName field} = "Food" then
  {Participants field}
else
  0

 and then do a running total on that formula that is evaluated when BookingID changes.

 If you need a separate total for "Food" and "Beverages", just create another formula for "Beverages" and do a running total on that too.  If you're looking for a total that includes both, you can add "Beverages" to the "Food"  formula above.

 James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.