| Author |
Message |
nbritton
Newbie
Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
nbritton
Newbie
Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 May 2011 at 5:03am |
|
can you show sample data grouped and how you want it calculated?
|
|
IP Logged |
|
nbritton
Newbie
Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
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 Logged |
|
nbritton
Newbie
Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
|
Posted: 16 May 2011 at 6:08am |
|
Dblank --- You are correct.
|
|
IP Logged |
|
nbritton
Newbie
Joined: 30 Sep 2009
Online Status: Offline
Posts: 25
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
|
|
