Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: averages at the group level based on report total Post Reply Post New Topic
Page  of 2 Next >>
Author Message
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Topic: averages at the group level based on report total
    Posted: 14 May 2011 at 3:50am
I cant seem to find much on how to accomplish this task.  It appears that the average is not calculated after the report is completed but as the groups are completed.   
 
I need to have an average for each group based on a running total that is a count for entire set of data, lets call it total.  I also have a running total that is for each group.  starting over at the group and displays in the group footer.  Lets call it Group Total.  I then created a formual to average with these too feilds called average but it is not working correctly.  Can someone help me out?
 
Also Could anyone recommend some ready or books to purchase that not only cover basics but advanced tasks as well.
 
Thanks
Nick
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 May 2011 at 3:48am
if you can use the insert summary function then you can accomplish it. IF you MUST use Running totals then you would need to use subreports.
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Posted: 16 May 2011 at 4:40am
I dont think i can use the insert/ summary becouse it needs to be based of the number of records found.  I am using a count.
 
Can you explain more with the subreport and how this may be accomplished.
 
I am attempting to create a table like summary showing the following
 
JobNAME :: Number of Instances ::  Percentage of total
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 May 2011 at 5:03am
can you show sample data grouped and how you want it calculated?
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Posted: 16 May 2011 at 5:35am
Here is some sample data:
 

5/16/2011 JOBID Job Title Group Total Group Average

294 294 GET GETSETTLEA01 SQL TRANLOG 7,644 294.00

435 435 SLTECH MLMPSQL01A SQL TRANLOG 1,808 435.00

529 529 FNTS DB01 FS 17:30 Differential 169 529.00

900 900 FNTS DB01 FS 17:30 Differential 374 900.00

Total Count: 9,995

I hide the details so the report is not cluttered, but i still have the data.

output template i am trying to get to:

Client Name

Job Title

Count

% Total

MFF_PIAND002

MFF PIAND002 ORA-AQPROD ARCHLOG

20

26.67%

MILLSOMAPROD01

MFF MILLSOMAPROD01 SQL RA TRANLOG

11

14.67%

BIJOMADB01

Lawson BIJOMADB01 SQL 22:00 RA-Tuesday Differential

7

9.33%

MILLSOMAPROD01

MFF MILLSOMAPROD01 SQL 01:30 Tuesday Daily RA Full

4

5.33%

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 May 2011 at 5:47am
sorry,
having trouble 'seeing' your design here.
Is it like this...?
G1 = client name
G2 = Job Title
details
Gf2 Count of job title / client name and percentage of count(job title,cleint name) total jobs per client
 
???


Edited by DBlank - 16 May 2011 at 5:47am
IP IP Logged
sharona
Senior Member
Senior Member
Avatar

Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
Quote sharona Replybullet Posted: 16 May 2011 at 5:55am
group your report by clietn id, job id
place them in the group footer
 
use manual running totals to get your count
 
MANUAL RUNNING TOTALS

 

RESET

The reset formula is placed in a group header report header to reset the summary to zero for each unique record it groups by.

 whileprintingrecords;

Numbervar  X := 0;

 

CALCULATION

The calculation is placed adjacent to the field or formula that is being calculated.

(if there are duplicate values; create a group on the field that is being calculated on. If there are not duplicate records, the detail section is used.

         whileprintingrecords;

Numbervar  X := x + {field}; ( or formula)

 

DISPLAY

The display is the sum of what is being calculated. This is placed in a group, page or report footer. (generally placed in the group footer of the group header where the reset is placed.)

         whileprintingrecords;

Numbervar  X;

X

sharona
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Posted: 16 May 2011 at 6:08am
Dblank --- You are correct.
IP IP Logged
nbritton
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
Quote nbritton Replybullet Posted: 16 May 2011 at 6:23am
I am a little lost on the calculation part.  I have attempted to substitute the + for the devide to get the agerage but that does not seem to work when i have x / total
 
where total is a running total for the entire report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 May 2011 at 6:25am
I assume you have a pk per row so maybe try this...
use the insert summary function (sigma sign)
field to summarize = your PK field (likeley a jobnum)
type = distinct count
location = gf2 this should be your count (it can be moved to group header 2)
 
insert another summary
field to summarize = your PK field
type = distinct count
In Options set it to show as a percentage of and select group level 1
location = gf2
SHould be your %
(can be movede to group header 2)


Edited by DBlank - 16 May 2011 at 6:25am
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.