Grand total count of a formula result required

[losthorizon]

There are actually three distinct formulas (all very similar) that I am seeking a solution for (all in the same report): 'Formula 300' is: {DistinctCount ({MEMBER.MEMBERNUMBER}, {BASICMASTER.BRANCH}) / 300}. 'Formula 350' is: {DistinctCount ({MEMBER.MEMBERNUMBER}, {BASICMASTER.BRANCH}) / 350}. Similarly, 'Formula 400' is: {DistinctCount ({MEMBER.MEMBERNUMBER}, {BASICMASTER.BRANCH}) / 400}. What we are trying to determine is which delegate count would be the best option (most representative) for our members attending a company convention. I'm not sure if a running total is what I need here, or what! I need a Grand Total to display, for each column...the columns are '300';'350';'400', respectively. The most representative formula result for the total will be the one we will use to determine how many delegates each branch is allowed to send to a convention. The correct answer for the 'Formula 300' should be somewhere around 98 to 100+ (delegates), if done on a manually on a calculator. The subtotals for each branch in the columns are whole numbers carried out to one decimal point (supposedly to aid in accuracy!): (i.e. 1.8, 0.3, and 3.2 delegates are examples). The board of directors will make the final determination, if a fraction is involved. The report prints three (3) pages, portrait layout. It is a "Recap" Report, by Branch office & business (dollar) amount breakdown. Is this possible on Crystal 8.0? (Others here have CRpts Version 8.5; but I have CRpts 8.0 on this machine). I've read most of Crystal's documentation on the subject of 'Running Totals'...but am struggling to understand it all. Is the 'Distinct Count' formula a problem here?

 

[kenhamady]

Your formula divides each group's subtotal by a fixed number. These formula's can't be summarized because they rely on a summary. You would have to use manual running totals with variables to get a grand total of these. But it isn't clear to me how or why you would 'count' these to get a grand total. Are you trying to determine how many groups meet a criteria? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[losthorizon]

Ken, Happy Holidays! & thanks for the prompt reply. Our "Convention Delegate Statistics" report shows BY BRANCH: total number of insurance certificates (read: policies) held, number of members per branch, the branch name, and the total amount (in dollars) of insurance held in each of those branches. Branch 1 has 1800 certs (policies), held by 1600 members. The delegate count for Branch 1, if the '300' formula is used: 5.4; if the '350' formula is used: 4.6; if the '400' formula is used: 4.0.
We want to summarize the columns for all 80-some Branches. I've tried using formulas in both the 'Evaluate' & 'Reset' sections in the Running Total Expert in CRpt 8.0. The result is not what I expected. Using whole numbers, the total for the '300' (formula) column equals exactly 98. Let me know if this is not adequate information to formulate an answer. Thanks.

 

[kenhamady]

To do any type of summary of these formulas you have to write running total formulas with variables. You can't use running total fields.

What do you mean by summarize? Add them up? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[losthorizon]

Ken, yes, we want a separate total of the distinct count for each of the three groups (300/350/400), if possible. This would be the total number of members that would be allowed to attend the convention, depending upon whatever criteria (divide by either 300, 350 or 400) is selected by the Board of Directors. Branch 1 currently shows 5 delegates allowed, if we divide by 300. 5 would be allowed to attend, if we divide by 350, BUT only 4 delegates if we divide by 400. Adding up the columns of all the 80+ branches attending the convention currently gives misleading results.

 

[kenhamady]

You will need a running total field. Use the 3-Formula technique described in faq149-182. The "accum" formula should be in the group footer.

I am puzzled as to how are you adding them now, and how it is misleading? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[losthorizon]

Ken, It's fixed using the variables idea. Rounding-off proved to be the problem here. We will check results manually with a calculator just to be certain, but it's done. In other words, we simply could have put a distinct count (of whatever field we wanted a count of) in the report footer, but since the client wanted to see rounded amounts in each group, the result, when rounded the same way, would not have matched the sum of the displayed values in the group section, especially since we were dividing the distinct count displayed in each group section by a certain factor.