Re: How to code a complicated report

["Wallach, Jarod" <JWallach@KPMG.CO.NZ>]

I have found in the past that the best way to do this type of reporting
(especially if you need to display the results nicely)
is to create an SQR to generate the data needed and place this in a new
table (specifically created for the report or batch of reports) and then
design a crystal report to do the data fetching and displaying. this of
course means that you will need to update the reporting table regularly, say
with the overnight batch processing

hope this helps

> Jarod Wallach
> kpmg
> Enabling Technologies
> mailto:jwallach@kpmg.co.nz
> http://www.kpmg.co.nz
> Consultant, KPMG
> 135 Victoria Street
> P.O. Box 996
> KPMG Wellington         Phone    +64 4 3828800 x8886
> New Zealand     Fax       +64 4 8021221
>
> Disclaimer:
> The information in this electronic mail message is confidential and
> may be legally privileged.  It is intended solely for the addressee.
> Access to this Internet electronic mail message by anyone else is
> unauthorised.  If you are not the intended recipient, any disclosure,
> copying, distribution or any action taken or omitted to be taken in
> reliance on it is prohibited and may be unlawful. When addressed to
> our clients any opinions or advice contained in this Internet
> electronic mail message are subject to the terms and conditions
> expressed in the governing KPMG client engagement letter


-----Original Message-----
From: Vivek Pandian [mailto:vp25@OIT.GATECH.EDU]
Sent: Tuesday, 12 October 1999 10:17
To: Multiple recipients of list SQR-USERS
Subject: Re: How to code a complicated report


What is your database? I have done a lot of reports of this kind.

Vivek

At 04:50 PM 10/11/99 , you 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!
>

Jarod