Re: How to code a complicated report

[Kimberly Blumenberg <KBLUMEN@SEARS.COM>]

Thanks a lot! I think this will work.



 (Embedded
 image moved   Eric Dimick Eastman <erice@ONTKO.COM>
 to file:      10/11/99 04:18 PM
 pic08850.pcx)




Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Kimberly Blumenberg)
Subject:  Re: How to code a complicated report




Kimberly,

It is a little difficult to tell from your paragraph and the columns didn't come
out
well on my e-mail.  My first intuition is that you want to use an array.  I
hacked
out an example below as fast as I can type.  Your mileage may vary.

Hope this helps,
Eric

I would try something like:

begin-setup
create-array
   name=totals
   size=24
   field=total:number
end-setup

begin-program
do main
end-program

begin-procedure main
begin-select
dept_code  &main_dept_code () on-break print=never before=before_dept
after=after_dept
hours &main_hours
wage_code &main_wage_code
   evaluate &main_wage_code
      when = '101'
      when = '102'
         array-add &main_hours to totals(0) total
      when = '112'
         array-add &main_hours to totals(1) total
     . . .
   end-evaluate
from
  tables
order by dept_code
end-select
end-procedure main

begin-procedure before_dept
clear-array totals
move &main_dept_code to $dept_code
end-procedure before_dept

begin-procedure after_dept
print $dept_code (+1,5)
move 0 to #count
while #count < 24
   get #total from totals(#count) total
   let #col = #count * 5 +15
   print #total (,#col) edit 999.9
   add 1 to #count
end-while
end-procedure after_dept


Kimberly Blumenberg wrote:

> Hello all! I have been writing SQR programs for about ten months now. We have
an
> Informix database platform with SQR 4.3.2 , UNIX Op Sys. I am writing a
> complicated program for the accounting dept and I need help. The program
> generates a report file that sums the month to date department totals (wages)
> for hourly employees. Sounds simple right? Wrong! This is how the report
should
> look:
>
>      101
>      102  110  130  140  150  152  180   176  134  112  310etc...total of 24
> columns
>      109  111  131  141  151  153  181   177       113  310
>
> Dept Reg  Mitt Sunday    Hol  Early                Min
>                               Retire               Wage
>                               Incentive       Adjust
>                               Pay
>
> 3c1  319  555  738  837  2883 32737      9993      99438     8848      99893
> 3848
> 3c2  330  838  84849     4985 4858 484578     95495     9595      95965
> 54858     49595
> 3C4  4858 8585 85865     995  9966 8855  95968     95895     7577      734647
> 3646
>
> etc...more depts
>
> The numbers is bold are earnings codes. Those aren't printed on the report. I
> just want to show the that I use different codes for each column. The earnings
> codes have a month to date value associated with each row on the earnings_bal
> table. I have to sum the month to date amount for each field for every
> department. For example, the first column is the department field. The second
> column contains the earnings codes for regular wages. The month to date
regular
> wages sum for dept 3c1 is 319, which is the sum of the month to date wages for
> codes 101, 102 and 109. The third column contains the Mitt wages. The sum of
> codes 110 and 111 are the Mitt wages. etc, etc,.  My question is, what is the
> easiest way to code this program. Do I need to use arrays, load-lookup tables
or
> temp tables? I do know that it would be very inefficient to do 24 different
> select statements and changing the line in the where clause that says "and
erncd
> in ('101','102','109') for each column.
>
> The entire program is just an extract. The only calc's that I do are the sums.
> Also, the number of depts that will end up on the report is unknown due to
other
> criteria such as, only hourly employees, not in a certain location, etc. But,
> there will be exactly 24 columns across ( not including the dept column).
>
> Thanks in advance!

--
------------------------------------------------------------------------
Eric Dimick Eastman  | To match what we [humans] can do, there would
Ray Ontko & Co.      | have to 3 billion of them [computers] . . .
erice@ontko.com      | wired together, . . . talking incessantly.
http://www.ontko.com |           Lewis Thomas, The Lives of a Cell 1974

pic08850.pcx