Print Page | Close Window

averages at the group level based on report total

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13207
Printed Date: 29 Apr 2024 at 5:13am


Topic: averages at the group level based on report total
Posted By: nbritton
Subject: averages at the group level based on report total
Date 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


Replies:
Posted By: DBlank
Date 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.

Posted By: nbritton
Date 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

Posted By: DBlank
Date Posted: 16 May 2011 at 5:03am
can you show sample data grouped and how you want it calculated?

Posted By: nbritton
Date 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%


Posted By: DBlank
Date 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
 
???

Posted By: sharona
Date 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

Posted By: nbritton
Date Posted: 16 May 2011 at 6:08am
Dblank --- You are correct.

Posted By: nbritton
Date 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.

Posted By: DBlank
Date 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)

Posted By: nbritton
Date Posted: 16 May 2011 at 6:29am
I have tried that with the jobid, the problem i ran into was it added the job numbers togther and then devided by the count.  Giving me an average that mactched the job number.  Thats why i have been trying to use a set of running totals.  One for the group and another for the report total.

Posted By: DBlank
Date Posted: 16 May 2011 at 6:33am
you can change the level at which is shows the percentage by changing the group level in the percentage option section of teh summary.
Am I missing something else there?

Posted By: nbritton
Date Posted: 16 May 2011 at 7:02am
All i have there is a grand total of job id.  IT appears to be adding the job ids together to get the total.

Posted By: DBlank
Date Posted: 16 May 2011 at 7:06am
try adding another insert summary as...
 
field to summarize = your PK field
type = distinct count
location = gf1 this should be your count of all jobs per customer
 
now use this as your level to get the percentages per job
This gf1 summary field can be suppressed

Posted By: nbritton
Date Posted: 16 May 2011 at 7:40am
I dont think that will work.  There are multiple instances of the same job as it will retry in the autoscheduler.  To do so i would need to find away to use the masterserver, jobid, instance combined to accomplish that.   I dont think that would give me what i am fully looking for.

Posted By: DBlank
Date Posted: 16 May 2011 at 7:41am
Disticntcount at teh group level should

Posted By: DBlank
Date Posted: 16 May 2011 at 8:03am

I was trying to give you a way to avoid sub reports.

If you can garner your numbers via an insert summary function (however you figure it out) these can be used as values BEFORE the last record in calculation is printed. If you cannot do this then you would have to use sub reports to get your value for the percentage.
You can create the subreport in the section before the value is needed (e.g. a GH1a) then pass the values from the subreport back to the main report via a shared variable. This has to be after the subreport is run (e.g GH1b). Then that value (in the shared variable)can be used in any calculation formula in the main report you want anywhere 'below' GH1b.

Posted By: nbritton
Date Posted: 16 May 2011 at 8:13am
I apprciate the assitacnce DBlank.  I am working to attempt the idea on the job instance but i am not sure its working.  I am going to play with a few options there, but it seems to not update the total count correctly.  It still appears to be adding up the sum of the job ids and then averaging that. 
 
Are you aware of any other way i may be able to precent the data.  I have thought about cross-tab but i am not sure if it would work either in the way i am needing.
 
I thought this would be a pretty striaght approch or simple thing to do.  Bottom line all i need is the group average of the overall record count.  I want to know what average of the failures reported by that that jobid are in relation to the overall count from all jobs.

Posted By: DBlank
Date Posted: 16 May 2011 at 8:21am

Hmm.

Make a formula to do the percentage for you.
DistinctCount ({table.pk}, {table.jobtitle}) % DistinctCount ({table.pk}, {table.client})
 
 

Posted By: DBlank
Date Posted: 16 May 2011 at 8:21am
the formula should work because it is using the summary values of the distinctcount of PK @ both Group 1 and Group 2


Print Page | Close Window