Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     Getting blanks with general total
 Go New Search Notify Tools Reply
 Getting blanks with general total
Virtuoso
 posted September 10, 2010 05:40 AM
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'
"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,

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

 Posts: 1654 | Location: Enschede, Netherlands | Registered: August 12, 2010 IP
Expert
 posted September 10, 2010 10:21 AM Hide Post
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'
"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 STYLE *
GRID=OFF, SIZE=9, \$
ENDSTYLE
END```

T

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

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5637 | Location: United Kingdom | Registered: April 08, 2004 IP
Guru
 posted September 10, 2010 12:03 PM Hide Post
It sounds quite similar to this problem

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

 Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008 IP
Virtuoso
 posted September 13, 2010 11:46 AM Hide Post
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.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

 Posts: 1654 | Location: Enschede, Netherlands | Registered: August 12, 2010 IP
Virtuoso
 posted September 14, 2010 03:48 AM Hide Post
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,

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

 Posts: 1654 | Location: Enschede, Netherlands | Registered: August 12, 2010 IP