Hello again,

We're having a problem with a general total calculation giving us blank results in the COLUMN-TOTALs. See below simplified test-case.

The row labeled 'TOTAL' contains the value '-' for every column in the report, while we expected the sum of those columns in there instead! What's going wrong here? Any suggestions?

I left the HEADING in there because that has the problem I was originally trying to solve, namely that I couldn't print the value for the product line. This value is unique on this page, as it's the text value for something directly related to the ID we use as the input value for this report. It seems plausable that it's empty for the same reason our column totals remain empty though.

TABLE FILE VOI00028H
SUM
     BUDGET/D10S AS 'Budget'
     DEMAND/D10S AS 'Demand'
   	 COMPUTE STOCK/D10S=-1; AS 'Stock'
BY CCGR AS 'Group'
BY CART AS 'Article'
BY CSTG AS 'Stg'
SUM
     BUDGET/D10S AS 'Budget'
     DEMAND/D10S AS 'Demand'
   	 COMPUTE STOCK/D10S=-1; AS 'Stock'
BY CCGR AS 'Group'
BY CART AS 'Article'
BY CSTG AS 'Stg'
ACROSS YEAR AS 'Year'
ACROSS PERIOD AS 'Period'
HEADING
"Productline  : <FST.PLPRLD"
WHERE TOTAL STOCK LT 0;
WHERE YEARPERIOD GE 201001 AND YEARPERIOD LE 201013;
WHERE PRLC EQ 'AR';
ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

The output we get is like:

Productline :  
 
      Year 
      2010 
      Period                                                        1                     2
Group Article         Stg  Budget  Demand Stock  Budget  Demand Stock  Budget  Demand Stock
                                -        -    -       -       -     -       -       -     -
SB    Article1        OPV       -        -   -1       -       -    -1       -       -     -
      Article2        OPV       -        -   -1       -       -    -1       -       -     -
SW    Article3        OPV     400      339   -1     400     339    -1       -       -     -
      Article4        OPV       -       47   -1      47            -1       -       -     -
...
TOTAL                           -        -    -       -       -     -       -       -     -

Where we expected the last row to be:

TOTAL                         300      386   -5     447     339    -5       -       -     -

[B]Edit{/B]: Updated the first row in the sample data; originally it showed -1 for the values of Stock, but on closer inspection in our production data those values aren't there and it's probably significant to explain our empty column totals.

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

Ther doesn't appear to be anything wrong with your code, and to test it I created a temporary sample file using your data. The structure might not be exactly like yours but you might be able to spot why your code works with my sample data as opposed to yours -

APP FI VOI00028H DISK VOI00028H.MAS (LRECL 80
-RUN
-WRITE VOI00028H
-WRITE VOI00028H FILE=VOI00028H,SUFFIX=FOC
-WRITE VOI00028H SEGNAME=SEG1
-WRITE VOI00028H FIELD=CCGR,   ,A2   ,A2   ,$
-WRITE VOI00028H FIELD=PLPRLD, ,A10  ,A10  ,$
-WRITE VOI00028H FIELD=PRLC,   ,A2   ,A2   ,$
-WRITE VOI00028H FIELD=CART,   ,A10  ,A10  ,$
-WRITE VOI00028H FIELD=CSTG,   ,A3   ,A3   ,$
-WRITE VOI00028H FIELD=YEARPERIOD,   ,A6YYM   ,A6   ,$
-WRITE VOI00028H FIELD=BUDGET, ,I9   ,I9   ,$
-WRITE VOI00028H FIELD=DEMAND, ,I9   ,I9   ,$
-WRITE VOI00028H DEFINE YEAR/YY = YEARPERIOD; $
-WRITE VOI00028H DEFINE PERIOD/M = YEARPERIOD; $
-RUN

CREATE FILE VOI00028H
MODIFY FILE VOI00028H
FREEFORM CCGR PLPRLD PRLC CART CSTG YEARPERIOD BUDGET DEMAND
DATA
,,AR,,,201001,,,$
SB,,AR,Article1,OPV,201001,,,$
SB,,AR,Article2,OPV,201001,,,$
SW,,AR,Article3,OPV,201001,400,339,$
SW,,AR,Article4,OPV,201001,,47,$
SB,,AR,Article1,OPV,201002,,,$
SB,,AR,Article2,OPV,201002,,,$
SW,,AR,Article3,OPV,201002,,,$
SW,,AR,Article4,OPV,201002,,,$
END
-RUN

TABLE FILE VOI00028H
SUM
     BUDGET/D10S AS 'Budget'
     DEMAND/D10S AS 'Demand'
   	 COMPUTE STOCK/D10S=-1; AS 'Stock'
BY CCGR AS 'Group'
BY CART AS 'Article'
BY CSTG AS 'Stg'
SUM
     BUDGET/D10S AS 'Budget'
     DEMAND/D10S AS 'Demand'
   	 COMPUTE STOCK/D10S=-1; AS 'Stock'
BY CCGR AS 'Group'
BY CART AS 'Article'
BY CSTG AS 'Stg'
ACROSS YEAR AS 'Year'
ACROSS PERIOD AS 'Period'
HEADING
"Productline  : <FST.PLPRLD"
WHERE TOTAL STOCK LT 0;
WHERE YEARPERIOD GE 201001 AND YEARPERIOD LE 201013;
WHERE PRLC EQ 'AR';
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

T

This message has been edited. Last edited by: Tony A,

It sounds quite similar to this problem

quote:

Originally posted by Tony A:
Ther doesn't appear to be anything wrong with your code, and to test it I created a temporary sample file using your data. The structure might not be exactly like yours but you might be able to spot why your code works with my sample data as opposed to yours -

Thanks Tony,

It looks like the differences might be related to the data involved... Or maybe it's a bug that's been fixed between 7.6.11 and 7.7?

I'll do some more testing tomorrow, maybe I can figure out what's happening here.

Good news is that with Tony's test data I get the correct results - so it's not a difference in versions that's causing it.

In the meantime, I noticed that the first row in our data actually gives an entire row of '-', and not (as I originally thought) -1 for stock. I edited my original post to reflect that. I think the first row being empty is probably significant to our problem.

The table file in question is generated nightly by an agent, it's not a physical database table.
Next to that, it is left outer joined to a table hold just before it's printed - however, it's on the left side of the outer join and the joined table file only contains two fields, so that shouldn't cause a row of NULL-values in the result.

Edit: We found an error in our data where a foreign key reference wasn't resolved, fixed it, lost the empty row in the output, but still get blank column-totals. Apparently that wasn't the cause either.

For now we've worked around the issue by pre-calculating most of our values into a temporary hold file that we then use to generate the final (printed) version. We'd still like to know what's going on here though.

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