Arrays and report GROUP...WHERE

[David Z.]

Hi to all,

RHEL 5.6
GDC 2.32.08
$ fglrun -V
fglrun 2.30.06 build-1815.7
Built Dec  1 2010 15:16:07
IDS 11.50.FC7IE

Can I use an array in an AFTER GROUP OF clause in a 4GL style report? My program is substantially more complex then my example below, but I think the example shows the problem. Previously, I had a static number of tran_type(s). I originally used Method 1. Always worked. Now I have an unknown number of tran_type(s). In the real program, I load the array, pa_taxrelieftype, from a table. In the example, I hard code the array. Method 2 uses pa_taxrelieftype[1].tran_type, pa_taxrelieftype[2].tran_type, etc. Not really any better then Method 1. Method 3 is my goal. I want to be able to add a new tran_type record to my table and not need a program change. I want to use a FOR loop. The program compiles without error. I get run time error:

Program stopped at 'test10.4gl', line number 106.
FORMS statement error number -1326.
An array variable has been referenced outside of its specified dimensions.
NOTE: Line 106 is the "format" line in test10_rpt()

When I run it through a debugger, on the first call to "output to report test10_rpt(p_rpt.*)", it does the PAGE HEADER, and then crashes with the above error. It never gets to the ON EVERY ROW or AFTER GROUP OF.

Is this a bug? Should this work? Can I use arrays this way?

Thanks for any help,
David


   define
      pa_taxrelieftype  dynamic array of record
         tran_type      char(2)
      end record,
      taxrelieftype_cnt integer

main

   defer interrupt
   defer quit

   call testit()

end main


function testit()

   define
      p_rpt       record
         tran_type   char(2),
         amt         integer
      end record

   close window screen

   start report test10_rpt to screen

   let pa_taxrelieftype[1].tran_type = "AA"
   let pa_taxrelieftype[2].tran_type = "BB"
   let pa_taxrelieftype[3].tran_type = "CC"

   let p_rpt.tran_type = "AA"
   let p_rpt.amt       = 10
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "AA"
   let p_rpt.amt       = 11
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "AA"
   let p_rpt.amt       = 12
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "BB"
   let p_rpt.amt       = 20
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "BB"
   let p_rpt.amt       = 21
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "BB"
   let p_rpt.amt       = 22
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "CC"
   let p_rpt.amt       = 30
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "CC"
   let p_rpt.amt       = 31
   output to report test10_rpt(p_rpt.*)

   let p_rpt.tran_type = "CCB"
   let p_rpt.amt       = 32
   output to report test10_rpt(p_rpt.*)

   finish report test10_rpt

end function



report test10_rpt(p_rpt)

   define
      p_rpt       record
         tran_type   char(2),
         amt         integer
      end record,
      i        integer

   output
      left margin 0
      top margin 0
      bottom margin 0
      page length 60

   order external by p_rpt.tran_type

   format

      page header
         print column  1,  "Type   Amount"

      on every row

         print column  2,  p_rpt.tran_type clipped,
               column 10,  p_rpt.amt using "###"
               
      after group of p_rpt.tran_type

# Method 1
# Hard code the tran_type
{
         if (group count(*) where p_rpt.tran_type = "AA") > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = "AA" using "###"
         end if

         if (group count(*) where p_rpt.tran_type = "BB") > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = "BB" using "###"
         end if

         if (group count(*) where p_rpt.tran_type = "CC") > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = "CC" using "###"
         end if
}


# Method 2
# Hard code index of pa_taxrelieftype[?].tran_type
{
         if (group count(*) where p_rpt.tran_type = pa_taxrelieftype[1].tran_type) > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype[1].tran_type using "###"
         end if

         if (group count(*) where p_rpt.tran_type = pa_taxrelieftype[2].tran_type) > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype[2].tran_type using "###"
         end if

         if (group count(*) where p_rpt.tran_type = pa_taxrelieftype[3].tran_type) > 0 then
            print column  1, "Total",
                  column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype[3].tran_type using "###"
         end if
}


# Method 3
# Use for loop for index of pa_taxrelieftype[?].tran_type
{
$ fglrun test10
Program stopped at 'test10.4gl', line number 106.
FORMS statement error number -1326.
An array variable has been referenced outside of its specified dimensions.
NOTE: Line 106 is the "format" line in test10_rpt()
}
         for i = 1 to pa_taxrelieftype.getlength() 
            if (group count(*) where p_rpt.tran_type = pa_taxrelieftype.tran_type) > 0 then
               print column  1, "Total",
                     column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype.tran_type using "###"
            end if
         end for

         skip 1 line

end report

[Christophe M.]

Hello,

I think there's a trouble with line :

print column  1, "Total",
                     column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype.tran_type using "###"

I would add the index of pa_taxrelieftype.tran_type, it gives :
print column  1, "Total",
                     column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype.tran_type using "###"

I'm not at work, so I can't test it to know if it solves the trouble.

[David Z.]

Sorry. When I post to the forum, it drops the [index]. I had an i within square brackets. When I preview or post it drops it. I have replaced the i with [myi]. I don't know why the forum can accept [myi] but not an i within square brackets. Looks like it did it to your reply as well.

define
    myi   integer
.......
         for myi = 1 to pa_taxrelieftype.getlength()   
            if (group count(*) where p_rpt.tran_type = pa_taxrelieftype[myi].tran_type) > 0 then
               print column  1, "Total",
                     column 10, group sum(p_rpt.amt) where p_rpt.tran_type = pa_taxrelieftype[myi].tran_type using "###"
            end if
         end for

[Reuben B.]

Sorry. When I post to the forum, it drops the [index]. I had an i within square brackets. When I preview or post it drops it. I have replaced the i with [myi]. I don't know why the forum can accept [myi] but not an i within square brackets. Looks like it did it to your reply as well.
...

Have a look at https://4js.com/fjs_forum/index.php?action=help;page=post#bbcref, and you should hopefully see why your i in square brackets is not appearing

When posting code, use the Code Syntax drop down to indicate that code is code e.g.

Code (genero) Select Expand


   FOR i = 1 TO arr.getLength()
      DISPLAY arr[i].*
   END FOR


Reuben

[Reuben B.]

You or I may have oversimplified your example but wouldn't

Code (genero) Select Expand

after group of p_rpt.tran_type
   print column  1, "Total", column 10, group sum(p_rpt.amt)

achieve what you wanted?



As for the cause of the run-time error, I suspect a two pass report is being created https://4js.com/online_documentation/fjs-fgl-manual-html/User/Reports.html#RPT_TWOPASS, when it shouldn't.  I know a while back I had a discussion with the developers as a simple example REPORT with ORDER EXTERNAL and GROUP SUM was attempting to connect to a database when it shouldn't need to.  My suspicion is that a temp table is being created and pa_taxrelieftype[myi].tran_type is being evaluated with myi = 0 at the point of this temp table being created.

Wether you can use such a complex expression in the GROUP SUM WHERE I'm not sure.  It probably has to be stand-alone and be able to be evaluated in isolation.

Reuben

[David Z.]

Reuben,

You are correct. Your example code works in my test program. Unfortunately, my real program is substantially more complex and your idea will not work. What I really need is an AFTER GROUP OF within an AFTER GROUP OF. In my program, I don't break on p_rpt.tran_type (AFTER GROUP OF). I break on a higher level variable (p_rpt.system_type), but I need to GROUP p_rpt.tran_type within p_rpt.system_type. Adding an AFTER GROUP OF p_rpt.tran_type screws up other AFTER GROUP OFs. At the end of my AFTER GROUP OF p_rpt.system_type, I use code like yours to total the p_rpt.system_type. Using the WHERE code for the p_rpt.tran_type was the only way I could see to do it.

PS I now see what I did wrong when posting my code example and why I lost the i within square brackets.

David

[Tim S.]

Hi David,

Since your tran_type data exists in a table, have you considered declaring a cursor and looping through it rather than using an array?
This might allow you to bypass any "two-pass report" issues.

Tim S

[David Z.]

Tim,

Thanks for your response. I tried your idea, but it doesn't help. I'm unsure exactly how Genero processes my report. My AFTER GROUP OF variable is not the one I want to total, I must use the WHERE clause. When I watch it through a debugger, p_rpt.tran_type != pa_taxrelieftype[1].tran_type but yet it runs the code in the IF as if they are equal. When I tried the cursor, p_rpt.tran_type != p_taxrelieftype.tran_type and it doesn't run whats in the IF. It seems to me, somehow, Genero must keep a running total of pa_taxrelieftype[1].tran_type, but when I use pa_taxrelieftype[myi].tran_type it can't because it doesn't know the value of myi. In other words, its trying to keep a running total but can't because myi is null or 0. Same for when I tried the cursor. Genero doesn't know what p_taxrelieftype.tran_type is to keep a running total since it is undefined until I define the cursor.

David

[Tim S.]

David,

I'm guessing you have already contacted tech support so they can look into the specifics for you...

It sounds like you are down to the old fashioned method of building your own summary data from ON EVERY ROW info.
Wish I could have provided more help...I realize I don't have your database, etc to build and run the program but if you would like to send me the live report source code, I will look through it and let you know if something stands out to me.

Tim S

[Reuben B.]

David,

Just so I'm clear.  You have

ORDER EXTERNAL BY system_type, something_else

... you can't have ...

ORDER EXTERNAL BY system_type, tran_type,  something_else

... as that would not give you the breaks/totals etc you desire at the something_else level.

I'd try the approach to use a temp table to calculate the tran_type summary per system_type, and have something like ...

Code (genero) Select Expand

FIRST PAGE HEADER
   CREATE TEMP TABLE trantype_sum(trantype CHAR(2), amt DECIMAL(11,2))

BEFORE GROUP OF system_type
   DELETE FROM trantype_sum WHERE 1=1

ON EVERY ROW
   ...
   UPDATE trantype_sum SET amt= p_rpt.amt WHERE trantype = p_rpt.trantype
   IF rows_updated() = 0 THEN
      INSERT INTO trantype_sum(trantype, amt) VALUES (p_rpt.trantype,p_rpt.amt)
  END IF

AFTER GROUP OF system_type
   ...
   DECLARE trantype_sum_curs CURSOR FOR SELECT * FROM trantype_sum ORDER BY trantype

   FOREACH trantype_sum_curs INTO x.tran_type, x.amt
      PRINT column1, "Total ", x.trantype, COLUMN 10, x.amt
   END FOREACH

[Rene S.]

Hello,
some details about the evaluation of aggregates within a REPORT. The compiler replaces "GROUP COUNT(*) WHERE expression" by an internal variable. This variable will be updated after any OUTPUT TO REPORT by evaluating the expression with the current variables. Later, when performing AFTER GROUP, "GROUP COUNT(*) WHERE expression" is not evaluated again. In other words: the result of GROUP COUNT(*) WHERE expression is evaluated before the AFTER GROUP block will be executed. That's why you can not use variables in expression as your'e doing in "Method 3".

Back to your original example. The code is useless. See "Method 1". Only one of the IF .. THEN statement will be executed.
Replace the code by:

    after group of p_rpt.tran_type
         print column  1, "Total", column 10, group sum(p_rpt.amt) using "###"

Remark: GROUP SUM  (*) is always > 0.

If you want to limit the print statement to some values in the global array pa_taxrelieftype, you must replace the code by:
    after group of p_rpt.tran_type
        for myi = 1 to pa_taxrelieftype.getlength()   
            if  p_rpt.tran_type == pa_taxrelieftype[myi].tran_type 
                print column  1, "Total", column 10, group sum(p_rpt.amt)  "###"
            end if
        end for


Rene

   

[David Z.]

Tim S,

I contacted Lily (U.S. tech support) before posting to this forum. She was unsure if Genero could handle an array in such a way. Her solution is similar to other solutions suggested here....Keep totals in ON EVERY ROW code, or some other method by hand. I could do that. My original question was..."Should an array work as in Method 3?". Based on Rene's post, it would appear not. This is what I concluded, but didn't know how to put it in words.

I can send you my program, but I would prefer to do it through email and not the forum. If necessary, I could probably provide a working program (with other module functions) and a small data set, if that would help.

Reuben,

Yes, I do use
ORDER EXTERNAL BY system_type, something_else
Another reason I don't do an AFTER GROUP OF p_rpt.tran_type is that I use similar code in the ON LAST ROW section.

David

[Tim S.]

David,

Now that you have answers from Lily, Rene and Reuben indicating that method 3 probably won't work, I will leave it up to you on emailing your code. My email address is tim<at>parrishent.com. In the interest of full disclosure, I do not work for 4Js. However I have been coding with Informix for about 20 years and Genero for the last 3+ years.

Tim S

[David Z.]

Everyone,

Thanks for the help. I will consider the suggestions for changing the program. I think I have my answer.

David