Solved

Crystal Reports Subtotalling

Posted on 2004-10-07
40
305 Views
Last Modified: 2008-01-09
Help! I'm new and stuck!

I have created a report that contains customer survey responses to 17 questions (columns) with the rating (0 to 5). With Expert-Exchange assistance I created formulas to calculate the total number of responses of each column (ignoring any fields containing a zero).  

if{fieldname} > 0 then 1 else 0

The report is grouped by branch and then by region (region consists of one or more branches)

Next I created an group total average for each column:
if sum({@CountVals Follow Thru},{Rolodex_Entity.Branch_Name})= 0 then 0
else sum({SurveyResults.LO_Followthru},{Rolodex_Entity.Branch_Name})/sum({@CountVals Follow Thru},{Rolodex_Entity.Branch_Name})

Finally, I included a grand total average for each column:
sum ({fieldname}) / sum ({@countvals fieldname})

All of these formulas appear to be working correctly but now I want to calculate the average by region and am at a loss - I have tried everything but obviously not the one thing that will make it work! Can you direct me? Thank you



0
Comment
Question by:maston57
  • 22
  • 17
40 Comments
 
LVL 22

Expert Comment

by:_TAD_
ID: 12253278
Certainly... the solution is easy enough, it just looks a little ugly.

You will need to create at least 3 functions. My example will use 4
functions. 1 function to create two variables, 1 function to
manipulate the value of the two variables, and 2 functions to display
the results (1 function to display each variable).


func1: InitVars
func2: CalcVars
func3/4: Disp<variable name>


<InitVars - This goes in header section where you want your var set
to 0 (report header for grand totals, header 1/2/3 for subtotals).
This field is typically hidden.>

whileprintingrecords;
shared numbervar myVar1 := 0;
shared numbervar myVar2 := 0;


<CalcVars - This goes in the section where you calculate something,
usually the details section. This field is typically hidden>

whileprintingrecords;
shared numbervar myVar1;
shared numbervar myVar2;

if {DBFIELD} = <something> then
myVar1 := myVar1 + {DBFIELD}
else
myVar2 := myVar2 + {DBFIELD};



<DispVar1, DispVar2 - This goes in the footer section where you want
to display the variable. This field is usually visible.>

<DispVar1>
whileprintingrecords;
shared numbervar myVar1;
myVar1;


<DispVar2>
whileprintingrecords;
shared numbervar myVar2;
myVar2;




Now for a scenario:

Suppose I wanted to have a financial report with 2 subtotals (one for
each day, and another for each week) and then one grand total (for
the period).

I would require 3 Init Functions, 1 Calculation function and 3
display functions.


<InitDailySubTotal>(group header 2)
WhilePrintingRecords;
Shared NumberVar daily := 0;

<InitWeeklySubtotal>(group header 1)
WhilePrintingRecords;
Shared NumberVar weekly := 0;

<InitPeriodGrandTotal>(report header)
WhilePrintingRecords;
Shared NumberVar grandTotal := 0;


<CalcTotals> (details)
WhilePrintingRecords;
Shared NumberVar daily;
Shared NumberVar weekly;
Shared NumberVar grandTotal;

daily := daily + {dbfield};
weekly := weekly + {dbfield};
grandTotal := grandTotal + {dbfield};



<DispDailySubTotal>(group footer 2)
WhilePrintingRecords;
Shared NumberVar daily;
daily;

<DispWeeklySubtotal>(group footer 1)
WhilePrintingRecords;
Shared NumberVar weekly;
weekly;

<DispPeriodGrandTotal>(report footer)
WhilePrintingRecords;
Shared NumberVar grandTotal;
grandTotal;



Sorry for being so long winded. I hope this helps.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12253303
Maybe i misunderstood but You have  a group already Why dont you insert a summary field use average function on the field that you want to summarize

Regards
Emre
0
 

Author Comment

by:maston57
ID: 12253719
Averaging doesn't work because it includes the responses with a zero. Any other ideas? Thank you
0
 

Author Comment

by:maston57
ID: 12254015
Wow! Variables are new to me so I'm lost.

1) For the first function do I enter it in exactly as you have it?
2) For the second function is the <dbfield> one of my calculations, groups, or fields?

Haven't made it to number 3 and 4. Thank you for your help
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12254076


>>> 1) For the first function do I enter it in exactly as you have it?

Yes.  Your init function should look *exactly* like this.  Well.. You can change the variable names, but you get the idea.  The entire function should be like 2 lines long.  line 1 should *always* be "WhilePrintingRecords;"

-----------------------

<InitVars - This goes in header section where you want your var set
to 0 (report header for grand totals, header 1/2/3 for subtotals).
This field is typically hidden.>

whileprintingrecords;
shared numbervar myVar1 := 0;
shared numbervar myVar2 := 0;

-----------------------

This function is going to be placed (hidden/suppressed) in the header section where you want to reset you variable to 0.  If you are running a very simple report and merely want a grand total, then this would go in the page header.  If you are running a complex report and want all kinds of sub-totals, then you will have many init functions (initDayTotal, initWeekTotal, initMonthTotal) and each of them will be placed in one of the group headers.




>>>2) For the second function is the <dbfield> one of my calculations, groups, or fields?

The <dbField> is exactly what it sounds like, the database field that you want to do the calculations on.  This could be another variable if you'd like, it doesn't really matter.  This is the field where you are getting your numbers from (for subtotaling).

0
 

Author Comment

by:maston57
ID: 12254271
I must be doing it wrong because all it is doing is calculating the total which is incorrect.  Here's a scenario...

I have 5 surveys....the response ratings are (5,4,5,0,3).  The total for the ratings is 17 but to calculate the correct overall rating (excluding surveys where the response is zero) means my grand total rating is 4.25 (17 / 4).  I have this working properly for the grand total and branch total but it does not work correctly when I try to calculate the total by region.

I entered in the <InitiVars exactly as you said and placed it in the  Header sections.  

I entered the <CalcVars in exactly as you indicated and entered the {dbfield} as the column dbfield I am trying to calculate. Maybe what I have incorrect is <something>... what is that suppose to be zero? 1? I entered this in the calculation field as indicated.

I then entered the <DispVar1> and <DispVar2> and placed them in the "Region" and "Branch" footer sections.

When I run the report I get a total for the column (ex 17) but that's it. The other functions all say zero.

Thanks for your patience -


0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12254293


I'll try and use your data as my example.  It may not be quite right, but perhaps it will make more sense.


As I see it you have 17 questions/data columns
1 Report Header/Footer
1 Page Header/Footer <not used here>
1 Region Header/Footer
1 Branch Header/Footer
1 Details Section


Each of the Following will go into the ReportHeader, region header and branch header respectively

<InitGrandTotalAve>
whileprintingrecords;
shared numberVar gtCount :=0;
shared numbervar gtQuestion1 :=0;
shared numbervar gtQuestion2 :=0;
...
shared numbervar gtQuestion17 :=0;


<InitRegionAve>
whileprintingrecords;
shared numberVar raCount :=0;
shared numbervar raQuestion1 :=0;
shared numbervar raQuestion2 :=0;
...
shared numbervar raQuestion17 :=0;


<InitBranchAve>
whileprintingrecords;
shared numberVar baCount :=0;
shared numbervar baQuestion1 :=0;
shared numbervar baQuestion2 :=0;
...
shared numbervar baQuestion17 :=0;




Details Section:

<IncVariables>
whileprintingrecords;
shared numberVar gtCount;
shared numbervar gtQuestion1;
shared numbervar gtQuestion2;
...
shared numbervar gtQuestion17;

shared numberVar raCount;
shared numbervar raQuestion1;
shared numbervar raQuestion2;
...
shared numbervar raQuestion17;

shared numberVar baCount;
shared numbervar baQuestion1;
shared numbervar baQuestion2;
...
shared numbervar baQuestion17;


gtCount := gtCount +1;
raCount := raCount+1;
baCount := baCount+1;

if {MyTable.dataField_Q1} > 0
   gtQuestion1 : = gtQuestion1 + {MyTable.dataField_Q1};

if {MyTable.dataField_Q1} > 0
   raQuestion1 : = raQuestion1 + {MyTable.dataField_Q1};

if {MyTable.dataField_Q1} > 0
   baQuestion1 : = baQuestion1 + {MyTable.dataField_Q1};


if {MyTable.dataField_Q2 > 0
   gtQuestion2: = gtQuestion2+ {MyTable.dataField_Q2;

if {MyTable.dataField_Q2 > 0
   raQuestion2: = raQuestion2+ {MyTable.dataField_Q2;

if {MyTable.dataField_Q1} > 0
   baQuestion2: = baQuestion2+ {MyTable.dataField_Q2;

... [etc.]



Finally, for the display variables.  You will need 17x3 functions for this (51 functions).

WhilePrintingRecords;
shared NumberVar gtCount;
shared NumberVar gtQuestion1;

gtQuestion1/gtCount;

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12254317


There are also ways to streamline this by using arrays, but I won't get into that right now.


Paste in your formulas so I can see them.  You proabably are just miss an If Then statement.
0
 

Author Comment

by:maston57
ID: 12254348
Thanks -

I placed this is in the headers:

WhilePrintingRecords;
shared numbervar myVar1 := 0;
shared numberVar myVar2 := 0;

I placed this in details:

WhilePrintingRecords;
shared numbervar myVar1;
shared numbervar myVar2;

if {SurveyResults.LO_Followthru} = 1 then
myVar1 := myVar1 + {SurveyResults.LO_Followthru}
else
myVar2 := myVar2 + {SurveyResults.LO_Followthru}

I placed one of these each in my two footer sections (Region and Branch):......is this where I'm wrong?

WhilePrintingRecords;
shared numbervar myVar1;
myVar1;

WhilePrintingRecords;
shared numbervar myVar2;
myVar2;
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12254349

you probably need to include a count variable.  

WhilePrintingRecords;
Shared NumberVar myCount;
Shared NumberVar myTotal;

// IF my Column value > 0, add 1 to the counter, else do nothing
myCount := IIF({MyTable.Column1}>0, myCount+1, myCount);

myTotal := myTotal + {MyTable.Column1};


Then in your Disp function

WhilePrintingRecords;
Shared NumberVar myCount;
Shared NumberVar myTotal;

myTotal/myCount;
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12254388


Here is one of your errors:


I placed this is in the headers:

WhilePrintingRecords;
shared numbervar myVar1 := 0;
shared numberVar myVar2 := 0;


// This says that you reset all variables to 0 each and every time this header gets called.  i think you want to two header functions

//Region Header
WhilePrintingRecords;
shared numbervar myVar1 := 0;

//Branch Header
WhilePrintingRecords;
shared numberVar myVar2 := 0;





//branch footer
WhilePrintingRecords;
shared numbervar myVar2;
myVar2;


//region footer
WhilePrintingRecords;
shared numbervar myVar1;
myVar1;




Your details section is a bit confusing:

if the value of {SurveyResults.LO_Followthru} = 1 then you add 1 to your region total, otherwise you add some number to your branch total?

-------------------------------------------
WhilePrintingRecords;
shared numbervar myVar1;
shared numbervar myVar2;

if {SurveyResults.LO_Followthru} = 1 then
myVar1 := myVar1 + {SurveyResults.LO_Followthru}
else
myVar2 := myVar2 + {SurveyResults.LO_Followthru}
---------------------------------------------


I think it should look more like:

WhilePrintingRecords;
shared numbervar myVar1;
shared numbervar myVar2;

myVar1  := iif ({SurveyResults.LO_Followthru} > 0, myVar1 + {SurveyResults.LO_Followthru}, myVar1);

myVar2  := iif ({SurveyResults.LO_Followthru} > 0, myVar2 + {SurveyResults.LO_Followthru}, myVar2);



note:  If you don't like the IIF notation you can always do it the long way:

If ({SurveyResults.LO_Followthru} > 0) Then
   myVar2  := myVar2 + {SurveyResults.LO_Followthru}
else
   myVar := myVar2;



0
 

Author Comment

by:maston57
ID: 12254471
If you couldn't already tell....I don't have any experience in this - I went to a class and am now trying to apply what I know because our IT dept is too busy. The way the detail section is suppose to work is the customer enters a rating from 0 to 5. To  calculate an overall rating branch, region and company I total the responses and divide by the number of surveys received except if a borrower doesn't answer a question (0) we do not "ding" the branch, etc for the customer not responding.  In my original scenario where I had 5 responses with a total of 17 divided by the number of responses (4) the overall rating is 4.25. If I counted the (0) response in the overall rating the branch rating would be 3.40.  Does that make sense?

All these words are a little greek to me right now but I'm learning a lot so please bear with all my questions. So here's another...

In the new formulas you sent me when I am creating the new formula (<InitGrandTotalAve>) do I enter it in just like you have it...shared numberVar gtQuestion1 :=0; (does it know what question 1 is? or am I suppose to put my dbfield in place of that?) Sorry - I told you I'm a novice! Thank God, there are people like you out there!!!!
0
 

Author Comment

by:maston57
ID: 12254626
I'm creating the <incVariables> but when it gets to the

raCount := raCount+1; says that the remaining text does not appear to be part of the formula...here's what I have...

WhilePrintingRecords;
shared numberVar gtCount :=0;
shared numbervar gtQuestion1 :=0;
shared numbervar gtQuestion2 :=0;
shared numbervar gtQuestion3 :=0;
shared numbervar gtQuestion4 :=0;
shared numbervar gtQuestion5 :=0;
shared numbervar gtQuestion6 :=0;
shared numbervar gtQuestion7 :=0;
shared numbervar gtQuestion8 :=0;
shared numbervar gtQuestion9 :=0;
shared numbervar gtQuestion10 :=0;
shared numbervar gtQuestion11 :=0;
shared numbervar gtQuestion12 :=0;
shared numbervar gtQuestion13 :=0;
shared numbervar gtQuestion14 :=0;
shared numbervar gtQuestion15 :=0;
shared numbervar gtQuestion16 :=0;
shared numbervar gtQuestion17 :=0;

shared numbervar raQuestion1 :=0;
shared numbervar raQuestion2 :=0;
shared numbervar raQuestion3 :=0;
shared numbervar raQuestion4 :=0;
shared numbervar raQuestion5 :=0;
shared numbervar raQuestion6 :=0;
shared numbervar raQuestion7 :=0;
shared numbervar raQuestion8 :=0;
shared numbervar raQuestion9 :=0;
shared numbervar raQuestion10 :=0;
shared numbervar raQuestion11 :=0;
shared numbervar raQuestion12 :=0;
shared numbervar raQuestion13 :=0;
shared numbervar raQuestion14 :=0;
shared numbervar raQuestion15 :=0;
shared numbervar raQuestion16 :=0;
shared numbervar raQuestion17 :=0;

shared numberVar baCount :=0;
shared numbervar baQuestion1 :=0;
shared numbervar baQuestion2 :=0;
shared numbervar baQuestion3 :=0;
shared numbervar baQuestion4 :=0;
shared numbervar baQuestion5 :=0;
shared numbervar baQuestion6 :=0;
shared numbervar baQuestion7 :=0;
shared numbervar baQuestion8 :=0;
shared numbervar baQuestion9 :=0;
shared numbervar baQuestion10 :=0;
shared numbervar baQuestion11 :=0;
shared numbervar baQuestion12 :=0;
shared numbervar baQuestion13 :=0;
shared numbervar baQuestion14 :=0;
shared numbervar baQuestion15 :=0;
shared numbervar baQuestion16 :=0;
shared numbervar baQuestion17 :=0;

gtCount := gtCount+1;
raCount := raCount+1;
baCount := baCount+1;

If {SurveyResults.LO_Followthru} > 0
gtQuestion1 : = gtQuestion1 + {SurveyResults.LO_Followthru};
If {SurveyResults.LO_Followthru} > 0
raQuestion1 : = raQuestion1 + {SurveyResults.LO_Followthru};
If {SurveyResults.LO_Followthru} > 0
baQuestion1 : = baQuestion1 + {SurveyResults.LO_Followthru};

If {SurveyResults.LO_Calls} > 0
gtQuestion1 : = gtQuestion2 + {SurveyResults.LO_Calls};
If {SurveyResults.LO_Calls} > 0
raQuestion1 : = raQuestion2 + {SurveyResults.LO_Calls};
If {SurveyResults.LO_Calls} > 0
baQuestion1 : = baQuestion2 + {SurveyResults.LO_Calls};

If {SurveyResults.LO_Skills} > 0
gtQuestion1 : = gtQuestion3 + {SurveyResults.LO_Skills};
If {SurveyResults.LO_Skills} > 0
raQuestion1 : = raQuestion3 + {SurveyResults.LO_Skills};
If {SurveyResults.LO_Skills} > 0
baQuestion1 : = baQuestion3 + {SurveyResults.LO_Skills};

If {SurveyResults.LO_Needs} > 0
gtQuestion1 : = gtQuestion4 + {SurveyResults.LO_Needs};
If {SurveyResults.LO_Needs} > 0
raQuestion1 : = raQuestion4 + {SurveyResults.LO_Needs};
If {SurveyResults.LO_Needs} > 0
baQuestion1 : = baQuestion4 + {SurveyResults.LO_Needs};

If {SurveyResults.LO_Informed} > 0
gtQuestion1 : = gtQuestion5 + {SurveyResults.LO_Informed};
If {SurveyResults.LO_Informed} > 0
raQuestion1 : = raQuestion5 + {SurveyResults.LO_Informed};
If {SurveyResults.LO_Informed}> 0
baQuestion1 : = baQuestion5 + {SurveyResults.LO_Informed};

If {SurveyResults.LO_Image} > 0
gtQuestion1 : = gtQuestion6 + {SurveyResults.LO_Image};
If {SurveyResults.LO_Image} > 0
raQuestion1 : = raQuestion6 + {SurveyResults.LO_Image};
If {SurveyResults.LO_Image}> 0
baQuestion1 : = baQuestion6 + {SurveyResults.LO_Image};
0
 

Author Comment

by:maston57
ID: 12254662
I figured out my error in the last formual so you don't need to go through it but I think I should be adding in "Then" on all of these should I since  I have the "IF" statement?
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12255854


Yes absolutely!


I looked through my samples and noticed that I was missing a "Then" in all my statements as well.

IF <something THEN
   <command>;
0
 

Author Comment

by:maston57
ID: 12258029
Maybe some of this is sinking in!  Now, one more question.....Do include all of the formulas you sent me from the beginning of this or is the last set the ones that I use in place of all of them? Does that make sense? Thanks again.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12258762
Oy.... I thnk there is a slight problem with your calculations.  I've re-read your questions and examples and I think there are some problems.

1) You have 17 questions
2) You want to sum up the scores for each question
3) You want to display the average score for each question
   3a) The average score should not take 0's in as part of the average
       (i.e.  Ave(3+4+1+6+0) == Ave(3+4+1+6) )
4) You want to display 3 "averages" for each question, 1) Branch, 2) Region 3) Grand Total


Are the 4 statement above accurate?  If so, then there is a bit of re-tooling that needs to go on.
0
 

Author Comment

by:maston57
ID: 12258907
They are all correct - just to be sure that we're saying the same thing....

3A) the average for that example ahould be 3.5

Thank you
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12259068

What version of Crystal Reports do you have?
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12259754

Okay, one last question...

Do you need to find out what the average answer was for questions 1 (for example)? or are you just looking for the average score for a particular branch/region?

Shown below are the equations I created for a report that:

1) collects scores for each branch in a particular region
2) sums the scores
3) finds an average by branch/region and Grand total

It does not
1) show the average score per question
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 22

Expert Comment

by:_TAD_
ID: 12259835
<GrandTotalInit> // supressed, goes in report header
WhilePrintingRecords;
shared NumberVar gtCount :=0;
shared NumberVar gtTotal :=0;

<RegionInit> // suppressed, goes in group Header1
WhilePrintingRecords;
Shared NumberVar rCount :=0;
Shared NumberVar rTotal :=0;

<BranchInit> //Suppressed, goes in group Header2
WhilePrintingRecords;
Shared NumberVar bCount :=0;
Shared NumberVar bTotal :=0;



<Calc> // Supressed, goes in details section
WhilePrintingRecords;
Shared NumberVar gtCount;
Shared NumberVar gtTotal;
Shared NumberVar rCount;
Shared NumberVar rTotal;
Shared NumberVar bCount;
Shared NumberVar bTotal;

Local NumberVar rowSum := 0;
 
rowSum  := {Survey.Q1}+{Survey.Q2}+{Survey.Q3}+{Survey.Q4}+
    {Survey.Q5}+{Survey.Q6}+{Survey.Q7}+{Survey.Q8}+{Survey.Q9}+
    {Survey.Q10}+{Survey.Q11}+{Survey.Q12}+{Survey.Q13}+{Survey.Q14}+
    {Survey.Q15}+{Survey.Q16}+{Survey.Q17};

gtTotal := gtTotal + rowSum;
rTotal  :=  rTotal + rowSum;
bTotal  :=  bTotal + rowSum;

gtCount := gtCount + IIF ({Survey.Q1}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q1}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q1}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q2}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q2}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q2}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q3}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q3}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q3}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q4}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q4}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q4}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q5}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q5}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q5}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q6}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q6}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q6}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q7}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q7}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q7}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q8}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q8}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q8}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q9}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q9}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q9}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q10}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q10}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q10}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q11}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q11}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q11}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q12}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q12}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q12}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q13}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q13}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q13}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q14}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q14}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q14}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q15}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q15}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q15}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q16}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q16}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q16}>0, 1,0);

gtCount := gtCount + IIF ({Survey.Q17}>0, 1,0);
rCount  :=  rCount + IIF ({Survey.Q17}>0, 1,0);
bCount  :=  bCount + IIF ({Survey.Q17}>0, 1,0);




<BranchDisp> //visible, goes in Footer2
WhilePrintingRecords;
Shared NumberVar bCount;
Shared NumberVar bTotal;

bTotal/bCount;


<RegionDisp>// visible, goes in Footer1
WhilePrintingRecords;
Shared NumberVar rCount;
Shared NumberVar rTotal;

rTotal/rCount;


<GrandTotalDisp> //visible, goes in Report Footer
WhilePrintingRecords;
Shared NumberVar gtCount;
Shared NumberVar gtTotal;

gtTotal/gtCount;



0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12259917

You should see 7 functions above.
Just to clarify, the report will look something like:

Branch1 was asked 17 questions and had an average score of 3.2
Branch2 was asked 17 questions and had an average score of 2.7
Branch3 was asked 17 questions and had an average score of 2.9

RegionA, consisting of Branches 1,2,7,9, and 15, had an average score of 3.3
RegionB, consisting of Branches 3,8,11,12, and 20, had an average score of 2.1

etc.




However, If you would like to find out the average response per question:

i.e.

Question1 for RegionA had an average of 3.5
Question2 for RegionA had an average of 3.9
Question3 for RegionA had an average of 4.1

I have those equations as well, but after re-reading your question I don't think you were looking for that. So they are not included above.
0
 

Author Comment

by:maston57
ID: 12260075
Version 9


Here's what I end up needing:

1. Branch average for each question
2. Region average for each question
3. Company average for each question (The average score per question would be the Grand Total Average for that question I would think)

To complicate matters at some point I will also be needing the sales person's average for each question (I can probably figure that one out once you save the day!) (Not promising though!) The sales person average isn't as important as the 1st three though at this point in time.
Here's the general idea.....

NORTHEAST REGION                              
Branch 1      Q1      Q2      Q3      Q4      Q5
Customer      5      5      5      5      5
Customer      5      5      4      5      5
Customer      4      4      4      4      4
Customer      5      3      5      5      2
Customer      5      5      5      5      5
Branch Average      4.80      4.40      4.60      4.80      4.20
                              
Branch 2                              
Customer      5      5      4      5      5
Customer      4      5      5      5      3
Customer      0      0      0      0      0
Customer      4      4      4      2      4
Customer      5      5      5      5      5
Branch Average      4.50      4.75      4.50      4.25      4.25
                                    
NORTHEAST REGION AVERAGE      4.65      4.58      4.55      4.53      4.23      
                                    
SOUTHEAST REGION                                    
Branch 3                                    
Customer      5      5      5      4      5      
Customer      0      4      4      4      0      
Customer      5      5      0      5      5      
Customer      2      4      3      5      3      
Customer      5      5      5      5      5      
Branch Average      4.25      4.60      4.25      4.60      4.50      
                                    
SOUTHEAST REGION AVERAGE      4.83      4.79      4.78      4.76      4.61      
                                    
COMPANY AVERAGE      4.74      4.68      4.66      4.64      4.42      (Average of NE and SE Regions)

Hopefully this makes sense!  Thanks again.




0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12261078
Okay... so then I did have it backwards.  er... something like that.


Anyway, here Is what you are looking for:

***********<gtQInit>***********// supresed, report header  --GRAND TOTAL--
WhilePrintingRecords;
Shared NumberVar gtQ1 := 0;
Shared NumberVar gtQ2 := 0;
Shared NumberVar gtQ3 := 0;
Shared NumberVar gtQ4 := 0;
Shared NumberVar gtQ5 := 0;
Shared NumberVar gtQ6 := 0;
Shared NumberVar gtQ7 := 0;
Shared NumberVar gtQ8 := 0;
Shared NumberVar gtQ9 := 0;
Shared NumberVar gtQ10 := 0;
Shared NumberVar gtQ11 := 0;
Shared NumberVar gtQ12 := 0;
Shared NumberVar gtQ13 := 0;
Shared NumberVar gtQ14 := 0;
Shared NumberVar gtQ15 := 0;
Shared NumberVar gtQ16 := 0;
Shared NumberVar gtQ17 := 0;

Shared NumberVar gtCntQ1 := 0;
Shared NumberVar gtCntQ2 := 0;
Shared NumberVar gtCntQ3 := 0;
Shared NumberVar gtCntQ4 := 0;
Shared NumberVar gtCntQ5 := 0;
Shared NumberVar gtCntQ6 := 0;
Shared NumberVar gtCntQ7 := 0;
Shared NumberVar gtCntQ8 := 0;
Shared NumberVar gtCntQ9 := 0;
Shared NumberVar gtCntQ10 := 0;
Shared NumberVar gtCntQ11 := 0;
Shared NumberVar gtCntQ12 := 0;
Shared NumberVar gtCntQ13 := 0;
Shared NumberVar gtCntQ14 := 0;
Shared NumberVar gtCntQ15 := 0;
Shared NumberVar gtCntQ16 := 0;
Shared NumberVar gtCntQ17 := 0;

***********<rQInit>***********// header 1, suppressed   --REGION--
WhilePrintingRecords;
Shared NumberVar rQ1 := 0;
Shared NumberVar rQ2 := 0;
Shared NumberVar rQ3 := 0;
Shared NumberVar rQ4 := 0;
Shared NumberVar rQ5 := 0;
Shared NumberVar rQ6 := 0;
Shared NumberVar rQ7 := 0;
Shared NumberVar rQ8 := 0;
Shared NumberVar rQ9 := 0;
Shared NumberVar rQ10 := 0;
Shared NumberVar rQ11 := 0;
Shared NumberVar rQ12 := 0;
Shared NumberVar rQ13 := 0;
Shared NumberVar rQ14 := 0;
Shared NumberVar rQ15 := 0;
Shared NumberVar rQ16 := 0;
Shared NumberVar rQ17 := 0;

Shared NumberVar rCntQ1 := 0;
Shared NumberVar rCntQ2 := 0;
Shared NumberVar rCntQ3 := 0;
Shared NumberVar rCntQ4 := 0;
Shared NumberVar rCntQ5 := 0;
Shared NumberVar rCntQ6 := 0;
Shared NumberVar rCntQ7 := 0;
Shared NumberVar rCntQ8 := 0;
Shared NumberVar rCntQ9 := 0;
Shared NumberVar rCntQ10 := 0;
Shared NumberVar rCntQ11 := 0;
Shared NumberVar rCntQ12 := 0;
Shared NumberVar rCntQ13 := 0;
Shared NumberVar rCntQ14 := 0;
Shared NumberVar rCntQ15 := 0;
Shared NumberVar rCntQ16 := 0;
Shared NumberVar rCntQ17 := 0;

***********<bQInit>***********// header 2, suppressed --BRANCH--
WhilePrintingRecords;
Shared NumberVar bQ1 := 0;
Shared NumberVar bQ2 := 0;
Shared NumberVar bQ3 := 0;
Shared NumberVar bQ4 := 0;
Shared NumberVar bQ5 := 0;
Shared NumberVar bQ6 := 0;
Shared NumberVar bQ7 := 0;
Shared NumberVar bQ8 := 0;
Shared NumberVar bQ9 := 0;
Shared NumberVar bQ10 := 0;
Shared NumberVar bQ11 := 0;
Shared NumberVar bQ12 := 0;
Shared NumberVar bQ13 := 0;
Shared NumberVar bQ14 := 0;
Shared NumberVar bQ15 := 0;
Shared NumberVar bQ16 := 0;
Shared NumberVar bQ17 := 0;

Shared NumberVar bCntQ1 := 0;
Shared NumberVar bCntQ2 := 0;
Shared NumberVar bCntQ3 := 0;
Shared NumberVar bCntQ4 := 0;
Shared NumberVar bCntQ5 := 0;
Shared NumberVar bCntQ6 := 0;
Shared NumberVar bCntQ7 := 0;
Shared NumberVar bCntQ8 := 0;
Shared NumberVar bCntQ9 := 0;
Shared NumberVar bCntQ10 := 0;
Shared NumberVar bCntQ11 := 0;
Shared NumberVar bCntQ12 := 0;
Shared NumberVar bCntQ13 := 0;
Shared NumberVar bCntQ14 := 0;
Shared NumberVar bCntQ15 := 0;
Shared NumberVar bCntQ16 := 0;
Shared NumberVar bCntQ17 := 0;


***********<colCalc>***********// Details, suppressed
WhilePrintingRecords;
Shared NumberVar gtCntQ1;
Shared NumberVar gtCntQ2;
Shared NumberVar gtCntQ3;
Shared NumberVar gtCntQ4;
Shared NumberVar gtCntQ5;
Shared NumberVar gtCntQ6;
Shared NumberVar gtCntQ7;
Shared NumberVar gtCntQ8;
Shared NumberVar gtCntQ9;
Shared NumberVar gtCntQ10;
Shared NumberVar gtCntQ11;
Shared NumberVar gtCntQ12;
Shared NumberVar gtCntQ13;
Shared NumberVar gtCntQ14;
Shared NumberVar gtCntQ15;
Shared NumberVar gtCntQ16;
Shared NumberVar gtCntQ17;
Shared NumberVar gtQ1 := gtQ1 + {Survey.Q1};
Shared NumberVar gtQ2 := gtQ2 + {Survey.Q2};
Shared NumberVar gtQ3 := gtQ3 + {Survey.Q3};
Shared NumberVar gtQ4 := gtQ4 + {Survey.Q4};
Shared NumberVar gtQ5 := gtQ5 + {Survey.Q5};
Shared NumberVar gtQ6 := gtQ6 + {Survey.Q6};
Shared NumberVar gtQ7 := gtQ7 + {Survey.Q7};
Shared NumberVar gtQ8 := gtQ8 + {Survey.Q8};
Shared NumberVar gtQ9 := gtQ9 + {Survey.Q9};
Shared NumberVar gtQ10 := gtQ10 + {Survey.Q10};
Shared NumberVar gtQ11 := gtQ11 + {Survey.Q11};
Shared NumberVar gtQ12 := gtQ12 + {Survey.Q12};
Shared NumberVar gtQ13 := gtQ13 + {Survey.Q13};
Shared NumberVar gtQ14 := gtQ14 + {Survey.Q14};
Shared NumberVar gtQ15 := gtQ15 + {Survey.Q15};
Shared NumberVar gtQ16 := gtQ16 + {Survey.Q16};
Shared NumberVar gtQ17 := gtQ17 + {Survey.Q17};

Shared NumberVar rCntQ1;
Shared NumberVar rCntQ2;
Shared NumberVar rCntQ3;
Shared NumberVar rCntQ4;
Shared NumberVar rCntQ5;
Shared NumberVar rCntQ6;
Shared NumberVar rCntQ7;
Shared NumberVar rCntQ8;
Shared NumberVar rCntQ9;
Shared NumberVar rCntQ10;
Shared NumberVar rCntQ11;
Shared NumberVar rCntQ12;
Shared NumberVar rCntQ13;
Shared NumberVar rCntQ14;
Shared NumberVar rCntQ15;
Shared NumberVar rCntQ16;
Shared NumberVar rCntQ17;
Shared NumberVar rQ1 := rQ1 + {Survey.Q1};
Shared NumberVar rQ2 := rQ1 + {Survey.Q2};
Shared NumberVar rQ3 := rQ1 + {Survey.Q3};
Shared NumberVar rQ4 := rQ1 + {Survey.Q4};
Shared NumberVar rQ5 := rQ1 + {Survey.Q5};
Shared NumberVar rQ6 := rQ1 + {Survey.Q6};
Shared NumberVar rQ7 := rQ1 + {Survey.Q7};
Shared NumberVar rQ8 := rQ1 + {Survey.Q8};
Shared NumberVar rQ9 := rQ1 + {Survey.Q9};
Shared NumberVar rQ10 := rQ1 + {Survey.Q10};
Shared NumberVar rQ11 := rQ1 + {Survey.Q11};
Shared NumberVar rQ12 := rQ1 + {Survey.Q12};
Shared NumberVar rQ13 := rQ1 + {Survey.Q13};
Shared NumberVar rQ14 := rQ1 + {Survey.Q14};
Shared NumberVar rQ15 := rQ1 + {Survey.Q15};
Shared NumberVar rQ16 := rQ1 + {Survey.Q16};
Shared NumberVar rQ17 := rQ1 + {Survey.Q17};

Shared NumberVar bCntQ1;
Shared NumberVar bCntQ2;
Shared NumberVar bCntQ3;
Shared NumberVar bCntQ4;
Shared NumberVar bCntQ5;
Shared NumberVar bCntQ6;
Shared NumberVar bCntQ7;
Shared NumberVar bCntQ8;
Shared NumberVar bCntQ9;
Shared NumberVar bCntQ10;
Shared NumberVar bCntQ11;
Shared NumberVar bCntQ12;
Shared NumberVar bCntQ13;
Shared NumberVar bCntQ14;
Shared NumberVar bCntQ15;
Shared NumberVar bCntQ16;
Shared NumberVar bCntQ17;
Shared NumberVar bQ1 := bQ1 + {Survey.Q1};
Shared NumberVar bQ2 := bQ2 + {Survey.Q2};
Shared NumberVar bQ3 := bQ3 + {Survey.Q3};
Shared NumberVar bQ4 := bQ4 + {Survey.Q4};
Shared NumberVar bQ5 := bQ5 + {Survey.Q5};
Shared NumberVar bQ6 := bQ6 + {Survey.Q6};
Shared NumberVar bQ7 := bQ7 + {Survey.Q7};
Shared NumberVar bQ8 := bQ8 + {Survey.Q8};
Shared NumberVar bQ9 := bQ9 + {Survey.Q9};
Shared NumberVar bQ10 := bQ10 + {Survey.Q10};
Shared NumberVar bQ11 := bQ11 + {Survey.Q11};
Shared NumberVar bQ12 := bQ12 + {Survey.Q12};
Shared NumberVar bQ13 := bQ13 + {Survey.Q13};
Shared NumberVar bQ14 := bQ14 + {Survey.Q14};
Shared NumberVar bQ15 := bQ15 + {Survey.Q15};
Shared NumberVar bQ16 := bQ16 + {Survey.Q16};
Shared NumberVar bQ17 := bQ17 + {Survey.Q17};

gtCntQ1 := gtCntQ1 + IIF({Survey.Q1}>0,1,0);
gtCntQ2 := gtCntQ2 + IIF({Survey.Q2}>0,1,0);
gtCntQ3 := gtCntQ3 + IIF({Survey.Q3}>0,1,0);
gtCntQ4 := gtCntQ4 + IIF({Survey.Q4}>0,1,0);
gtCntQ5 := gtCntQ5 + IIF({Survey.Q5}>0,1,0);
gtCntQ6 := gtCntQ6 + IIF({Survey.Q6}>0,1,0);
gtCntQ7 := gtCntQ7 + IIF({Survey.Q7}>0,1,0);
gtCntQ8 := gtCntQ8 + IIF({Survey.Q8}>0,1,0);
gtCntQ9 := gtCntQ9 + IIF({Survey.Q9}>0,1,0);
gtCntQ10 := gtCntQ10 + IIF({Survey.Q10}>0,1,0);
gtCntQ11 := gtCntQ11 + IIF({Survey.Q11}>0,1,0);
gtCntQ12 := gtCntQ12 + IIF({Survey.Q12}>0,1,0);
gtCntQ13 := gtCntQ13 + IIF({Survey.Q13}>0,1,0);
gtCntQ14 := gtCntQ14 + IIF({Survey.Q14}>0,1,0);
gtCntQ15 := gtCntQ15 + IIF({Survey.Q15}>0,1,0);
gtCntQ16 := gtCntQ16 + IIF({Survey.Q16}>0,1,0);
gtCntQ17 := gtCntQ17 + IIF({Survey.Q17}>0,1,0);

rCntQ1 := rCntQ1 + IIF({Survey.Q1}>0,1,0);
rCntQ2 := rCntQ2 + IIF({Survey.Q2}>0,1,0);
rCntQ3 := rCntQ3 + IIF({Survey.Q3}>0,1,0);
rCntQ4 := rCntQ4 + IIF({Survey.Q4}>0,1,0);
rCntQ5 := rCntQ5 + IIF({Survey.Q5}>0,1,0);
rCntQ6 := rCntQ6 + IIF({Survey.Q6}>0,1,0);
rCntQ7 := rCntQ7 + IIF({Survey.Q7}>0,1,0);
rCntQ8 := rCntQ8 + IIF({Survey.Q8}>0,1,0);
rCntQ9 := rCntQ9 + IIF({Survey.Q9}>0,1,0);
rCntQ10 := rCntQ10 + IIF({Survey.Q10}>0,1,0);
rCntQ11 := rCntQ11 + IIF({Survey.Q11}>0,1,0);
rCntQ12 := rCntQ12 + IIF({Survey.Q12}>0,1,0);
rCntQ13 := rCntQ13 + IIF({Survey.Q13}>0,1,0);
rCntQ14 := rCntQ14 + IIF({Survey.Q14}>0,1,0);
rCntQ15 := rCntQ15 + IIF({Survey.Q15}>0,1,0);
rCntQ16 := rCntQ16 + IIF({Survey.Q16}>0,1,0);
rCntQ17 := rCntQ17 + IIF({Survey.Q17}>0,1,0);

bCntQ1 := bCntQ1 + IIF({Survey.Q1}>0,1,0);
bCntQ2 := bCntQ2 + IIF({Survey.Q2}>0,1,0);
bCntQ3 := bCntQ3 + IIF({Survey.Q3}>0,1,0);
bCntQ4 := bCntQ4 + IIF({Survey.Q4}>0,1,0);
bCntQ5 := bCntQ5 + IIF({Survey.Q5}>0,1,0);
bCntQ6 := bCntQ6 + IIF({Survey.Q6}>0,1,0);
bCntQ7 := bCntQ7 + IIF({Survey.Q7}>0,1,0);
bCntQ8 := bCntQ8 + IIF({Survey.Q8}>0,1,0);
bCntQ9 := bCntQ9 + IIF({Survey.Q9}>0,1,0);
bCntQ10 := bCntQ10 + IIF({Survey.Q10}>0,1,0);
bCntQ11 := bCntQ11 + IIF({Survey.Q11}>0,1,0);
bCntQ12 := bCntQ12 + IIF({Survey.Q12}>0,1,0);
bCntQ13 := bCntQ13 + IIF({Survey.Q13}>0,1,0);
bCntQ14 := bCntQ14 + IIF({Survey.Q14}>0,1,0);
bCntQ15 := bCntQ15 + IIF({Survey.Q15}>0,1,0);
bCntQ16 := bCntQ16 + IIF({Survey.Q16}>0,1,0);
bCntQ17 := bCntQ17 + IIF({Survey.Q17}>0,1,0);


***********<BranchQ1Disp>***********//footer 2, visible
WhilePrintingRecords;
Shared NumberVar bQ1;
Shared NumberVar bCntQ1;

bQ1/bCntQ1;


***********<BranchQ2Disp>***********//footer 2, visible
WhilePrintingRecords;
Shared NumberVar bQ2;
Shared NumberVar bCntQ2;

bQ2/bCntQ2;


***********<BranchQ3Disp>***********//footer 2, visible
WhilePrintingRecords;
Shared NumberVar bQ3;
Shared NumberVar bCntQ3;

bQ3/bCntQ3;


{ETC. - 17 in total}



***********<RegionQ1Disp>***********//footer 1, visible
WhilePrintingRecords;
Shared NumberVar rQ1;
Shared NumberVar rCntQ1;

rQ1/rCntQ1;


***********<RegionQ2Disp>***********//footer 1, visible
WhilePrintingRecords;
Shared NumberVar rQ2;
Shared NumberVar rCntQ2;

rQ2/rCntQ2;


***********<RegionQ3Disp>***********//footer 1, visible
WhilePrintingRecords;
Shared NumberVar rQ3;
Shared NumberVar rCntQ3;

rQ3/rCntQ3;


{ETC. - 17 in total}



***********<GTotalQ1Disp>***********//report footer, visible
WhilePrintingRecords;
Shared NumberVar gtQ1;
Shared NumberVar gtCntQ1;

gtQ1/gtCntQ1;


***********<GTotalQ2Disp>***********//report footer, visible
WhilePrintingRecords;
Shared NumberVar gtQ2;
Shared NumberVar gtCntQ2;

gtQ2/gtCntQ2;


***********<GTotalQ3Disp>***********//report footer, visible
WhilePrintingRecords;
Shared NumberVar gtQ3;
Shared NumberVar gtCntQ3;

gtQ3/gtCntQ3;


{ETC. - 17 in total}

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12261428


Drat... I have an error in my colCalc function.



***********<colCalc>***********// Details, suppressed
...
..
.   //notice here---------------\/
Shared NumberVar rQ1 := rQ1 + {Survey.Q1};
Shared NumberVar rQ2 := rQ1 + {Survey.Q2};
Shared NumberVar rQ3 := rQ1 + {Survey.Q3};
Shared NumberVar rQ4 := rQ1 + {Survey.Q4};
Shared NumberVar rQ5 := rQ1 + {Survey.Q5};
Shared NumberVar rQ6 := rQ1 + {Survey.Q6};
Shared NumberVar rQ7 := rQ1 + {Survey.Q7};
Shared NumberVar rQ8 := rQ1 + {Survey.Q8};
Shared NumberVar rQ9 := rQ1 + {Survey.Q9};
Shared NumberVar rQ10 := rQ1 + {Survey.Q10};
Shared NumberVar rQ11 := rQ1 + {Survey.Q11};
Shared NumberVar rQ12 := rQ1 + {Survey.Q12};
Shared NumberVar rQ13 := rQ1 + {Survey.Q13};
Shared NumberVar rQ14 := rQ1 + {Survey.Q14};
Shared NumberVar rQ15 := rQ1 + {Survey.Q15};
Shared NumberVar rQ16 := rQ1 + {Survey.Q16};
Shared NumberVar rQ17 := rQ1 + {Survey.Q17};



I copied and pasted this... it needs to look like this:

Shared NumberVar rQ1 := rQ1 + {Survey.Q1};
Shared NumberVar rQ2 := rQ2 + {Survey.Q2};
Shared NumberVar rQ3 := rQ3 + {Survey.Q3};
Shared NumberVar rQ4 := rQ4 + {Survey.Q4};
Shared NumberVar rQ5 := rQ5 + {Survey.Q5};
Shared NumberVar rQ6 := rQ6 + {Survey.Q6};
Shared NumberVar rQ7 := rQ7 + {Survey.Q7};
Shared NumberVar rQ8 := rQ8 + {Survey.Q8};
Shared NumberVar rQ9 := rQ9 + {Survey.Q9};
Shared NumberVar rQ10 := rQ10 + {Survey.Q10};
Shared NumberVar rQ11 := rQ11 + {Survey.Q11};
Shared NumberVar rQ12 := rQ12 + {Survey.Q12};
Shared NumberVar rQ13 := rQ13 + {Survey.Q13};
Shared NumberVar rQ14 := rQ14 + {Survey.Q14};
Shared NumberVar rQ15 := rQ15 + {Survey.Q15};
Shared NumberVar rQ16 := rQ16 + {Survey.Q16};
Shared NumberVar rQ17 := rQ17 + {Survey.Q17};

sorry about that.
0
 

Author Comment

by:maston57
ID: 12262023
So far so good - I'm still placing the region and grand totals in the footers. The branch section seems to be fine except when all of the responses for a question are a "zero" - I receive an error message:

division by zero

It happened on this formula and the last line was highlighted.

WhilePrintingRecords;
Shared NumberVar bQ16;
Shared NumberVar bCntQ16;

bQ16/bCntQ16;

I ran into this problem previously and that's where I came up with the following formula which seemed to work (but then I was stumped):

if sum({@CountVals Follow Thru},{Rolodex_Entity.Branch_Name})= 0 then 0
else sum({SurveyResults.LO_Followthru},{Rolodex_Entity.Branch_Name})/sum({@CountVals Follow Thru},{Rolodex_Entity.Branch_Name})

Does my hero have any suggestions?




0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12262970


yes... in you Display functions type the following:


***********<BranchQ1Disp>***********//footer 2, visible
WhilePrintingRecords;
Shared NumberVar bQ1;
Shared NumberVar bCntQ1;


//TRY THIS:

IIF(bCnt>0,bQ1/bCntQ1,0);

// IF THAT FAILS, USE THE FOLLOWING
If bCnt>0 Then
  bQ1/bCntQ1
else
  0;



I prefer the IIF construct (in case you didn't notice), but I am pretty certain that it evaluates the entire command before determining if it's true or false.   So if you use the IIF command you *might* still get a divide by 0 error.  If you do, the Second long IF THEN command will most assuridly work for you.  




0
 

Author Comment

by:maston57
ID: 12263133
When I type in either of the formulas

IIF(bCnt>0,bQ1/bCntQ1,0);

or

If bCnt>0 Then
  bQ1/bCntQ1
else
  0;

it says I missing a )

Am I clueless or what? I thought I paid attention in class!
0
 

Author Comment

by:maston57
ID: 12263236
When I put this in it seems to work - is there anything wrong with it that you can see?

WhilePrintingRecords;
Shared NumberVar bQ16;
Shared NumberVar bCntQ16;


if shared numbervar bQ16 = 0 then 0 else
shared numbervar bQ16 /shared numbervar bCntQ16;

If not it looks like the report is running correctly but I need to double check the numbers. Then that leaves me with one more calculation I forgot to tell you I needed (hate me yet?) Since that is a separate question should I put it in as a new question> You have certainly earned the points!
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12263554


I'm a bit concerned about the "shared numbervar" in the equation.  

if shared numbervar bQ16 = 0 then 0 else
shared numbervar bQ16 /shared numbervar bCntQ16;


The compiler may ignore it, so then there isn't a problem.  But I'd take it out just to be safe

if bQ16 = 0 then 0 else
bQ16 /bCntQ16;



Nah... don't worry about a new question (unless this is a completely new report).  

Ask away.  As far as hating you yet?  nah... not yet.  We'll see what this next question is first.
;-P
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12263645


ps>  I'd add that If/Then to *EVERY* display function.  Any function that does a divide you should add that.  I should have added it to begin with, but didn't.  Sloppy programming.


For future programming reference:

Anytime you enter a loop or do a calculation the *VERY* first thing you should do is check to see if the parameter passed is a valid number.  All those security alerts that Microsoft puts out... most of them are because people take advantage of sloppy programming and not checking if the numbers are in a valid range.
0
 

Author Comment

by:maston57
ID: 12289202
The report seems to be working great - thank you!   I have two further questions that may or may not pertain to this report....

1) If I want a report that breaks the scores down even further to the sales person level would I use the same report and add a group for Salesperson"? Does it change any of the formulas or do I just need to add to the ones here?

2) Here's the one where you may hate me! In addition to the report I have now (thanks to you) I also want to create a scorecard that provides the same info but in another format. It would look like this....

Q1.  "The first question......."              Branch  "rating
                                                       Region "rating"
                                                       Company "rating"

Q2.  "The first question......."              Branch  "rating
                                                       Region "rating"
                                                       Company "rating"

and so on.....the scorecards would be for all the same levels of reporting...branch, region, company and salesperson. I worked on it all weekend using a copy of the report but I can't get the headers and footers to work properly - suiggestions?  

Let me know if I need to close this question out - that you've done a superlative job helping me (or should I say creating for me!) and start a new. Thanks again!
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12291547


Nah... it should work pretty much the same way.

If I understand the question correctly, you currently have:

A report that takes a customer survey and breaks down the ranking for each question based on:
1: The Branch
2: The Region:
3: An overall Grand total


So your report looks like:

               Q1     Q2     Q3
branch1     3        4       2  
branch2     4        2       5
...
Region1    3.5      3     3.5

branch7     4        3       4  
branch8     4        2       5
...
Region2     4       2.5    4.5
...
Total       3.75    2.75     4



If I understand you correctly, you now want to add a Salesperson group as well.  The question is: Is this salesperson a member of a Branch? Or are they selling to a region?  That is to say, is the new salesperson group going to be group 1 or group 3?


Assuming your report is going to look like:


                          Q1     Q2     Q3
branch1
   salesPerson1     3        3       2  
   salesPerson2     1        5       3  
   salesPerson3     4        4       1  
Branch1 Mean:     3        4       2

branch2  
   salesPerson1     3        4       2  
   salesPerson2     3        4       2  
   salesPerson3     3        4       2  
branch2  Mean:     #       #      #

<etc>


Then you will add "SalesPerson" as group 3 (or group 4??)  It will be a new group closest to the "Details" Section.


You will want to create a new Init Formula
<SalesInit> // supressed; goes in Header 3 (4??)
WhilePrintingRecords;
Shared NumberVar spQ1 :=0;
Shared NumberVar spQ2 :=0;
Shared NumberVar spQ3 :=0;
...
Shared NumberVar spCntQ1 :=0;
Shared NumberVar spCntQ2 :=0;
Shared NumberVar spCntQ3 :=0;



Then you will want to EDIT your <colCalc> function:

<ADD THE FOLLOWING>
***********<colCalc>***********// Details, suppressed


Shared NumberVar spCntQ1;
Shared NumberVar spCntQ2;
Shared NumberVar spCntQ3;
...
Shared NumberVar spQ1 := spQ1 + {Survey.Q1};
Shared NumberVar spQ2 := spQ2 + {Survey.Q2};
Shared NumberVar spQ3 := spQ3 + {Survey.Q3};
...
spCntQ1 := spCntQ1 + IIF({Survey.Q1}>0,1,0);
spCntQ2 := spCntQ2 + IIF({Survey.Q2}>0,1,0);
spCntQ3 := spCntQ3 + IIF({Survey.Q3}>0,1,0);
...





You will now need to Create 17 *NEW* Functions


***********<SalesPersonQ1Disp>***********//footer 3, visible
WhilePrintingRecords;
Shared NumberVar spQ1;
Shared NumberVar spCntQ1;

if spCntQ1>0 then
   spQ1/spCntQ1
else
  0;



***********<SalesPersonQ2Disp>***********//footer 3, visible
WhilePrintingRecords;
Shared NumberVar spQ2;
Shared NumberVar spCntQ2;

if spCntQ2>0 then
   spQ2/spCntQ2
else
  0;



***********<SalesPersonQ3Disp>***********//footer 3, visible
WhilePrintingRecords;
Shared NumberVar spQ3;
Shared NumberVar spCntQ3;

if spCntQ3>0 then
   spQ3/spCntQ3
else
  0;


{ETC. - 17 in total}

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12291830


As for your second question:

>>create a scorecard that provides the same info but in another format.<<

It's definately do-able.  I'll just need a little while to come up with an easy way to do it so we don't have to re-write a lot of what you already have.

Off the top of my head, I think it can be done by just adding 1 new group and 6 new variables (3 accumulators and 3 counters)
0
 

Author Comment

by:maston57
ID: 12304577
The sales person break down is working great. Thank you.  Not that I haven't given you enough to think about but I have one more question (for now).......

I have 17 questions on this survey and now I have an average rating for each question (either by sales person, branch, region or grand total) - now if I want to obtain the average overall score of all 17 questions for the sales person, branch, region, or grand total (company) - is that do-able?

Thank you so much for all of your help - until you helped me it took me on average 5 separate reports and approximately 3 full days of calculating to prepare these reports. I'm not sure where you work but they are very fortunate to have you.  

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12311551
Yes, you can do that with 4 functions

Here is one:

<DispTotalSpAve>// visible, in the footer of group 4 (salesPerson Group)
WhilePrintingRecords;

local NumberVar spTotalScore := spQ1 + spQ2 + ... + spQ17;
local NumberVar spTotalCnt := spCntQ1 + spCntQ2 + ... + spCntQ17;


if spTotalCnt >0 Then
   spTotalScore/spTotalCnt
Else
   0;



There others will look exactly like this, except instead of sales person it will be region (rCnt, rQ1), or branch, etc.


<DispTotalBranchAve>// visble in the footer of the branch group
WhilePrintingRecords;

local NumberVar bTotalScore := bQ1 + bQ2 + ... + bQ17;
local NumberVar bTotalCnt := bCntQ1 + bCntQ2 + ... + bCntQ17;


if bTotalCnt >0 Then
   bTotalScore/bTotalCnt
Else
   0;
0
 
LVL 22

Accepted Solution

by:
_TAD_ earned 500 total points
ID: 12311588


btw... Thanks for the compliment.  Oddly enough I don't actually ever get to use Crystal Reports.  Crystal is left to the BATs (business analysts), and they produce 3 or 4 reports that they have to merge in excel to get what they want.

Some businesses just don't take full advantage of the staff they have.

:-/
0
 

Author Comment

by:maston57
ID: 12312984
Boy, it's a shame your BATs aren't using it - I've come from the cut and paste world and don't ever want to go back! I've been bragging on you all week! Thanks - I'll work on adding these tonight and let you know.

 
0
 

Author Comment

by:maston57
ID: 12343395
The reports appear to be working great! If I close out these questions (you've certainly earned the points!) Is there any way to get you again if I need to ask another question related to these reports so I don't have to start all over? Thank you for all of your assistance. You have improved the quality of my work day (and night) more than you'll ever realize!
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 12344318


You can email me.  My email address is in my profile.  Just be sure to include

Experts-Exchange

in the subject of your email.  

EE frowns on help outside of the forum, but at least you can get direct me towards your question
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now