I need some help to generate weekly avg. and ytd avg. The table looks like below

Office Employee Week Orders Errors
------ -------- ---- ------ ------
Off1 EmpID_1 1 10 1
Off1 EmpID_2 1 8 0
Off1 EmpID_3 1 12 2
Off2 EmpID_4 1 10 1
...
Off1 EmpID_1 2 10 0
...

I need a report that looks like below for 1
office (e.g. Off1).

Employee Company Avg
Office Employee Week Error % Error %
------ -------- ---- ------- -------
Off1 EmpID_1 1 10 % 8.5 %
Off1 EmpID_2 1 8 % 8.5 %
...
Week subtotal 1 9 % 8.5 %
...
Week subtotal 2 8.5 % 9.5 %
...
YTD Total 8 % 7.5 %

I am not sure how to get the company averages, especially the 7.5 % which is the error % for all the employees for the year.

This message has been edited. Last edited by: Kerry,

David, assuming you are using COMPUTE to calculate each average,
then for the weekly average you'll want to use
ON WEEK RECOMPUTE
and for the YTD average , you can see if the same ON YEAR RECOMPUTE works for you.
You always have the option of using a RECAP command.
Look up both RECOMPUTE and RECAP in your manuals.

-S.

I am using RECOMPUTE. Showing the Error % is not a problem. In order to show the weekly average, I have added 2 fields to each record in the table which contains the Company weekly average info. The same 2 values for the week shows up in all the records for that week. That worked fine too. The company ytd average is what I could not get to work. If I use RECOMPUTE, it is giving me wrong number. I can add 2 more fields to the table which contains company error info which will give me the number I want. However, I don't know how to make it show up as a column total for the Error %. The RECAP command seems to be just the ticket. However, it puts the output on a seperate line right below the subtotal line. What determines the RECAP output format?

ON fieldname RECAP somefieldname/D5.2=X/Y; NOPRINT
ON fieldname SUBFOOT
" YEARLY AVERAGE IS <somefieldname "

and... you can put prefixes on those recap fields
eg:

"yearly average is <FST.somefieldname "

There are alot of ways to do this.
You might investigate using multiple verbs..avoids having to actualize stuff right in the database...just create it on the fly.
SUM ISSUES AS TOT_ISSUES
ERRORS AS TOT_ERRORS
BY OFFICE BY MONTH
SUM ISSUES
ERRORS
BY OFFICE BY MONTH BY EMPLOYEE
{ON TABLE HOLD}
END
.. try this sort of thing, without the HOLD so you can see how multiple verbs work.
You can nest many verbs..just make sure the BY fields match as you move down the verbs...the lower one(s) must contain exactly the same sort order as the upper one(s)... just adding a deeper level of granularity.

This message has been edited. Last edited by: susannah,

Thank you for the info. To do what you suggest, I would have to include the data for all locations even though I just need to print the report for one location. The other thing is I don't know how to hide the other locations from printing. As you can probably see, I am still trying to learn the different verbs. Can you tell me how to hide sections of a report from printing but have the data included in calculating the report totals.

the NOPRINT command is one way
SET ASNAMES = ON
TABLE FILE whatever
SUM THINGS AS TOTTHINGS NOPRINT
ERRORS AS TOTERRORS NOPRINT

BY OFFICE BY MONTH
SUM THINGS ERRORS AND COMPUTE AVG/D5.1=100*ERRORS/THINGS;
AND COMPUTE TOTAVG/D5.1=100*C2/C1;
-* C2 and C1 are column references to the reporting matrix created by your query
C1 is column 1, the first column after your BY fields, in this case your TOTTHINGS column.
There are alot of ways to do this
You can do all your calculations into a HOLD file and then TABLE FILE HOLD to select the final columns you wish to present. The best way for beginners imho, to get a handle on whats going on.

I follow what you are saying. However, is there a way to hide some detail lines from printing. I did a Daily Sales Report. The report will show sales by day, week, and month for the current month. For prior months of the year, it will only show monthly totals. I could not figure out how to hide the sales by day and week for prior months and show up only for the current month. Is there a way to do that?

yeah, again, several ways.
1) the coolest is probably to SET EXPANDABLE=ON and read up on that...where you click on the month and get the daily detail for that month, leaving all the rest of the report still showing (aggregated by month, eg)
2) the old-coders-never-die way might be:
( i don't know how you're calculating week, so i'll must make it up as /I2 )
DEFINE FILE thing
WEEKSHOW/I2 MISSING ON =IF WEEK NE thisweek THEN MISSING ELSE WEEK;
DAYSHOW/I2 MISSING ON = IF WEEK NE thisweek
THEN MISSING ELSE DAY ;
END
TABLE FILE thing
SUM somevalues
BY YEAR BY MONTH BY WEEKSHOW AS 'WEEK'
BY DAYSHOW AS 'DAY'
...
END
..assuming you have a way to figure out what the current week is...

Thank you again for you help. I try testing your suggestion with the following example. What it did is replace all the first name that start with "J" to missing. There is actually a line on the report with a "." as first name. That is not what I am looking for. What I am looking for is almost like "conditional printing". Whether an output line that shows up or not depends on an expression. If the expression is true, the line prints otherwise it is skipped. However, it has nothing to do with data. The data is still in the report totals and subtotals, etc. The line just doesn't print on the report. Is there a way to do that?

DEFINE FILE EMPLOYEE
FN/A15 MISSING ON = IF FIRST_NAME LIKE 'J%' THEN MISSING ELSE FIRST_NAME;
END

TABLE FILE EMPLOYEE
SUM
CNT.FIRST_NAME
ED_HRS
BY FN AS 'FIRSTNAME'
ON TABLE SUMMARIZE AS 'TOTAL'
END

David,

Maybe this will set you on a direction that could give you what you need:

DEFINE FILE EMPLOYEE
FN/A15 MISSING ON = IF FIRST_NAME LIKE 'J%' THEN MISSING ELSE FIRST_NAME;
END

TABLE FILE EMPLOYEE
SUM 
CNT.FIRST_NAME
ED_HRS
SUM 
CNT.FIRST_NAME
ED_HRS
BY FN AS 'FIRSTNAME'
ON TABLE SUMMARIZE AS 'TOTAL'
WHERE TOTAL FN NE MISSING;
END 

It uses a multi set request where the total is calculated. This total is not affected by the WHERE TOTAL, which however does affect the subtotal, but there may be a way around that too.

Thank you all for the info. I am looking for ways to do conditional printing. IBI has that feature for subtotal lines (See the code below). However, then WHEN clause does not work on detail lines. The use of multi set request is actually along the same lines that I am talking about i.e. the seperation of data manipulation and report presentation.

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY FIRST_NAME
ON DEPARTMENT SUBTOTAL AS 'SUBTOTAL' WHEN DEPARTMENT NE 'MIS'
ON TABLE SUMMARIZE AS 'TOTAL'
END

Hi David,

The following code may be of help:

TABLE FILE AVE
PRINT ORDERS ERRORS
COMPUTE COMPPCT/P6.2%=(C2/C1)*100;  
-*COMPUTE COMPPCT/P6.2%=(C2/C1)*100; 
BY OFFICE RECOMPUTE AS 'OFFICE TOTAL'
BY EMPID
ON TABLE RECOMPUTE AS  'COMPANY TOTAL'
END
 TEST DATAFILE AVE.TXT
Off1 EmpID_1 1 10 1
Off1 EmpID_2 1  8 0
Off1 EmpID_3 1 12 2
Off1 EmpID_4 1 10 1
Off2 EmpID_1 1 10 0
Off2 EmpID_2 1  8 0
Off2 EmpID_3 1 12 2
Off2 EmpID_4 1 10 1
Off1 EmpID_1 2 10 1
Off1 EmpID_2 2  8 0
Off1 EmpID_3 2 12 2
Off1 EmpID_4 2 10 1
Off2 EmpID_1 2 10 0
Off2 EmpID_2 2  8 0
Off2 EmpID_3 2 12 2
Off2 EmpID_4 2 10 1
TEST MASTER
FILENAME=AVE, SUFFIX=FIX,$
FIELD=OFFICE, ,A5,A5,$
FIELD=EMPID, ,A7,A7,$
FIELD=WEEK,,I2,A2,$
FIELD=ORDERS,,I9,A4,$
FIELD=ERRORS,,I9,A2,$

If you still have questions, then the issue may be complicated enough that a case with Information Builders' Customer Support Services may be of better help. To open a case, you may either call at 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry