Adding an average by group 1

[npp83]

Hi all, I posted previously on inserting averages into my report, and Lbass was kind enough to work with me through a solution to it. Building on that work, I now need to insert averages by my "Branch" group rather than by my Loan officer group. Please take a look at my website, which I have taken down and thrown on there two screenshots detailing my current report and the problem. (

http://www.nppetre.com)

In the first S/S is my report in design mode, where I have circled the averages that I need to change. I no longer need individual averages, but something that will keep a running total through the different loans and loan officers, perform the math and compute the average not by loan officer but by branch group.

To get my averages before I used the following code and split it up into the appropriate groups.

whileprintingrecords;
numbervar sumcnt := sumcnt + {#RTotal0};
numbervar cnt := cnt + 1;
---------------------------------------------
whileprintingrecords;
numbervar sumcnt;
numbervar cnt;
if cnt > 0 then sumcnt/cnt
---------------------------------------------
whileprintingrecords;
numbervar sumcnt;
numbervar cnt;
if not inrepeatedgroupheader then (sumcnt := 0; cnt := 0);

What can I modify above to get me the desired result?

 

[lbass]

You should be able to apply the same logic. If you are not using the former calculations, all you need to do is move some formulas. What aspect are you having difficulty with?

-LB

 

[npp83]

I am using the former calcualations In fact, I thought (and tried) before posting here that this was the logical approach. I basically moved the formula field (loanofficerfooter) containing this code:

whileprintingrecords;
numbervar sumcnt;
numbervar cnt;
if cnt > 0 then sumcnt/cnt

into the Branch footer instead of the Loan officer footer, and the math that comes out of it isn't right, and I am using double precision.

 

[lbass]

You have to move the reset to the branch group header.

-LB

 

[npp83]

Doh. I forgot to do that part. Now on a different report, which uses the same formulas, I am running into an error where even 0's are being treated as cnt + 1. For instance (and I have updated my website with a new S/S to reflect this problem) I have this:

1 27
3 28
6 29
7 30
9 31
0 32
------
See as it is iterating through and counting them up, it is counting 0 as the 32nd integer to be used, and that of course is messing up my math. How do I exclude zeros?

 

[lbass]

if value <> 0 then
cnt := cnt + 1 else
cnt := cnt;

But if the zero is a valid value, it should be factored in...

-LB

 

[npp83]

Isn't a valid value in this instance. It does need to be omitted. Now, given that I am new to programming and especially CR reports, where would I define what "value" is in this statement?

Furthermore, I am not sure how to merge these two statements:

whileprintingrecords;
numbervar sumcnt := sumcnt + {LOANXDB_N_02._CX_COUNT_COND_INCOME};
numbervar cnt := cnt + 1;

if value <> 0 then cnt := cnt + 1 else cnt := cnt;

 

[lbass]

whileprintingrecords;
numbervar sumcnt := sumcnt + {LOANXDB_N_02._CX_COUNT_COND_INCOME};
numbervar cnt;
if {LOANXDB_N_02._CX_COUNT_COND_INCOME} <> 0 then
cnt := cnt + 1 else
cnt := cnt;

-LB

 

[npp83]

Alright, I've been told that 0 is a valid number so if I have this:
1 1
0 2
0 3
----

My average should be 0.33 because those loans do need to be counted. Now, since .33 is a valid number, it needs to round up to the next whole integer. In this case .33 should be 1. If it were 2.21 it should be 3.0. I know there is rounding in the Format Editor, but to do this sort of operator I would need a custom formula. I am the only one at work who knows anything about Crystal Reports so I can't ask anyone, but what would I need to round even the slightest decimal to the next whole value (1.0, 2.0 etc..)?

 

[lbass]

You should start new threads on new topics. See if you have the roundup() function available. You haven't identified your version.

-LB

 

[npp83]

Thank you. I did start a new thread. And do have a roundup() function. My version is in my new thread.