*TOTAL with SUMMARIZE

[rollim]

Hi everybody,

My code is like this:

SUM
  VAR1 AS 'Label1'
  VAR2 AS 'Label2'
  COMPUTE VAR3/D9.2= 100*VAR2/VAR1; AS 'Label3'
BY BYVAR1
BY BYVAR2
BY BYVAR3
ON BYVAR2 SUMMARIZE AS 'SumLabel'
ON TABLE COLUMN-TOTAL AS 'TotalLabel'

and the report looks like this:

BYVAR1 BYVAR2 BYVAR3 Label1 Label2 Label3
------ ------ ------ ------ ------ ------
A      K      X           2      1  50.00
              Y           3      1  33.33

SumLabel1 K               5      2  40.00

A      L      X           2      1  50.00
              Y           3      1  33.33

SumLabel1 L               5      2  40.00
*TOTAL BYVAR1 A          10      4  40.00
 
B      K      X           4      2  50.00
              Y           6      2  33.33

SumLabel1 K               10     4  40.00

B      L      X           2      1  50.00
              Y           3      1  33.33

SumLabel1 L               5      2  40.00
*TOTAL BYVAR1 B          15      6  40.00

TotalLabel               25     10  40.00

and the question is:
How can I get Labels for "*TOTAL BYVAR" ?

Has anybody an idea?

Thanks a lot,
Roland

 

[GrSw]

Roland,

If memory serves me, it is impossible when using SUMMARIZE or SUB-TOTAL. The labels for the automaticaly SUMMARIZEd columns are always of the form '*TOTAL column_name column_value'. The solution is to use RECOMPUTE, instead of SUMMARIZE and SUBTOTAL instead of SUB-TOTAL, so your report could look like this:

SUM
VAR1 AS 'Label1'
VAR2 AS 'Label2'
COMPUTE VAR3/D9.2= 100*VAR2/VAR1; AS 'Label3'
BY BYVAR1
BY BYVAR2
BY BYVAR3
ON BYVAR2 RECOMPUTE AS 'SumLabel'
ON BYVAR1 RECOMPUTE AS 'AnotherLabel'
ON TABLE COLUMN-TOTAL AS 'TotalLabel'

Hope this helps
Grzegorz

 

[rollim]

Hi Grzegorz,

I tried your suggestion but it did not work. In this case, the Report looks like this:

BYVAR1 BYVAR2 BYVAR3 Label1 Label2 Label3
------ ------ ------ ------ ------ ------
A      K      X           2      1  50.00
              Y           3      1  33.33
       L      X           2      1  50.00
              Y           3      1  33.33


AnotherLabel  A          10      4  40.00
 
B      K      X           4      2  50.00
              Y           6      2  33.33
       L      X           2      1  50.00
              Y           3      1  33.33

AnotherLabel  B          15      6  40.00

TotalLabel               25     10  40.00

So there is no "SumLabel" in the report.
Do you have any other ideas ?

Thanks a lot,
Roland

 

[GrSw]

Roland,

I am currently using WebFOCUS 5.2 (NT & Solaris), and the sample report:

TABLE FILE MOVIES
ON TABLE SET PAGE-NUM OFF
SUM COPIES 
COMPUTE SALES/D12.2 = COPIES * LISTPR;
 BY CATEGORY
 BY RATING
 BY MOVIECODE
 ON RATING RECOMPUTE AS 'SumLabel'
 ON CATEGORY  RECOMPUTE AS 'AnotherLabel'
 ON TABLE COLUMN-TOTAL AS 'TotalLabel'
WHERE CATEGORY LIKE 'C%'
ON TABLE SET STYLEMODE FIXED
END

produces the following results on both platforms:

CATEGORY  RATING  MOVIECODE  COPIES           SALES
 --------  ------  ---------  ------           -----
 CHILDREN  G       043DIS          2           89.90
                   306DIS          2           59.90
                   732DIS          3           80.97
 
 SumLabel G                        7          713.23
 
           NR      031KKV          1           19.95
                   093WOR          1           19.95
                   309RAN          1           14.95
                   387PLA          1           14.98
                   476DIS          1           29.95
 
 SumLabel NR                       5          498.90
 AnotherLabel CHILDREN            12        2,420.04
 
 CLASSIC   G       284MGM          3          269.85
 
 SumLabel G                        3          269.85
 
           NR      005WAR          1           24.98
                   020TUR          3          119.97
                   231VES          1           29.95
                   243MGM          1           19.99
                   259MGM          2           39.98
                   354RCA          2           39.90
                   444MGM          2          179.98
                   505MGM          2           39.98
                   600MGM          2           39.96
                   688MGM          2           59.90
 
 SumLabel NR                      18        5,665.68
 AnotherLabel CLASSIC             21        8,498.91
 
 COMEDY    PG      196MCA          4           79.80
                   305PAR          2           29.90
                   518MGM          2           39.90
                   658CBS          4           79.92
 
 SumLabel PG                      12          897.96
 
           PG13    651PAR          3          179.97
 
 SumLabel PG13                     3          179.97
 
           R       733CBS          4           79.92
 
 SumLabel R                        4           79.92
 AnotherLabel COMEDY              19        2,941.20
 
 
 TotalLabel                       52       39,581.36

I think it should work the same way on the 4.3.x WebFOCUS versions. What version do you have ? Are you able to check this 'MOVIE' example in your environment ? Could you send your code exactly as it is run?
..., no other ideas, at least now.

Grzegorz

 

[rollim]

Hi Grzegorz,

I tried your code with some "hand-made" test-data. In my output there is no 'SumLabel'. The other Labels are printed correct.
We use Webfocus 4.3.5.

Hope, you have some good ideas.

Thanks a lot,
Roland

 

[GrSw]

Hi Roland,

It simply should work, so I do not have any idea why it does not work for you.
Could you post the code of your report ?
If you already solved it, post the solution, just letting the other know.

Regards
Grzegorz