Counting Running Totals

[AyJayEL]

I have to create a report showing what the percentage of children is that are currently taught in different sizes of groups but I am a bit stumped on how to count the result from a running total.

I have counted how many children are in each group (simple). How do I count how many of the groups have the result 2-4, 5-10 and more than 11? Once I have done that I should be able to do the percentage. For your information there are 10 groups.



Learn something new every day *:->*
AyJayEl

 

[KKond]

can u post some sample data. and results u want then it will be more clear.

 

[AyJayEL]

Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7etc
3 2 6 1 10 4 2

The Groups are running totals counting how many students are having tuition in that group.

Now I need to count how many groups have 2 - 4 students in them, 5 - 10 and over 10.

Learn something new every day *:->*
AyJayEl

 

[Madawc]

Do summary totals for each group. You ought to be able to count those using running totals for the whole report.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.
Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Madawc Williams (East Anglia, UK)

 

[lbass]

Some questions:

What is it that defines your original group (where you are counting students), i.e., what is the group field?

Do you have the option of using a SQL expression to return the counts per group? That would be the easiest solution in this situation, since you would be able to group on the counts.

What record selection criteria are you using?

-LB

 

[AyJayEL]

Group is confusing I think! In the database there are Groups, a child can be assigned to a group. I am counting how many children have been assigned to the group. (Groups 1 to 10). Any number can be assigned to them. I am doing a distinct count of the Student_id with formula of {MUS_STUDENT_TUITION.MUS_GROUP} = "001" for Group "002" for Group 2 etc.
LB I'm not sure about the SQL expression. How would I do it?

I have come to the conclusion that I will have to use a subreport. I was attempting to do it in the main report but I obviously need to Group on the Group (I told you it was confusing!)



Learn something new every day *:->*
AyJayEl

 

[AyJayEL]

Ok thanks to everyone for their help. I know have a solution. This is what I did (without resorting to a subreport). A formula called Groups -

WhilePrintingRecords;
numbervar group2;
numbervar group5;
numbervar group11;

if {#Group 1} in 2 to 4 then group2:=group2+1 else if {#Group 1} in 5 to 10 then group5:=group5+1
else if {#Group 1}>=11 then group11:=group11+1;

if {#Group 2} in 2 to 4 then group2:=group2+1 else if {#Group 2} in 5 to 10 then group5:=group5+1
else if {#Group 2}>=11 then group11:=group11+1;

if {#Group 3} in 2 to 4 then group2:=group2+1 else if {#Group 3} in 5 to 10 then group5:=group5+1
else if {#Group 3}>=11 then group11:=group11+1;

if {#Group 4} in 2 to 4 then group2:=group2+1 else if {#Group 4} in 5 to 10 then group5:=group5+1
else if {#Group 4}>=11 then group11:=group11+1;

if {#Group 5} in 2 to 4 then group2:=group2+1 else if {#Group 5} in 5 to 10 then group5:=group5+1
else if {#Group 5}>=11 then group11:=group11+1;

if {#Group 6} in 2 to 4 then group2:=group2+1 else if {#Group 6} in 5 to 10 then group5:=group5+1
else if {#Group 6}>=11 then group11:=group11+1;

if {#Group 7} in 2 to 4 then group2:=group2+1 else if {#Group 7} in 5 to 10 then group5:=group5+1
else if {#Group 7}>=11 then group11:=group11+1;

if {#Group 8} in 2 to 4 then group2:=group2+1 else if {#Group 8} in 5 to 10 then group5:=group5+1
else if {#Group 8}>=11 then group11:=group11+1;

if {#Group 9} in 2 to 4 then group2:=group2+1 else if {#Group 9} in 5 to 10 then group5:=group5+1
else if {#Group 9}>=11 then group11:=group11+1;

if {#Group 10} in 2 to 4 then group2:=group2+1 else if {#Group 10} in 5 to 10 then group5:=group5+1
else if {#Group 10}>=11 then group11:=group11+1;

Then three formulae like this one for Groups 2-4 which I put in the section below.

WhilePrintingRecords;
numbervar group2


Learn something new every day *:->*
AyJayEl

 

[orb353]

I have created a report pulling from a dataset.The report is based on transactions coming through the dataset from a fox pro database. These are some of the fields i am showing in my report.

Transactions Clientid headerid appttype
10028 5001 1007 2
10029 5001 1007 2
10030 5001 1007 2
10031 4772 1008 1
10032 4772 1008 1
10033 5525 1009 -2
10034 5525 1009 -2
10035 6005 1010 3
10036 6005 1010 3
10037 4228 1011 2
10038 4228 1011 2
10039 4228 1011 2

notice each clientid has several tranactions attached

appttypes
2 = repeat clients
3= new client
1= non request
-2= new client

I have counted all clients within the time frame i specified with a distinctcount for a total client count but i want to be able to count how many of these clients were reapeat or new. I have not been able to count how many repeat clients , new clients , etc.
because there is miltiple listings for each client- because most clients get more than one service.
how can i report how many of the client were repeat, new etc.
I would be grateful for help
thanks
orb353

 

[AyJayEL]

Hi Orb

I would post this as a new query if I were you. More people are likely to see it and you're more likely to get an answer!

Andy

Learn something new every day *:->*
AyJayEl