[CLOSED] across with ROW-TOTAL formatted differently
Is it possible to have an across with ROW-TOTAL and have the ROW-TOTALs formatted differently? In the example below I would like the ROW-TOTAL ONLY to show a dollar sign ($37,853 instead of 37,853).
TABLE FILE CAR SUM DEALER_COST RETAIL_COST ACROSS SEATS BY COUNTRY ON TABLE ROW-TOTAL END
Would like to get: .........SEATS .........2.......................4.......................5.......................TOTAL COUNTRY..DEALER_COST RETAIL_COST DEALER_COST RETAIL_COST DEALER_COST RETAIL_COST DEALER_COST RETAIL_COST ENGLAND......11,719......13,978......14,940......17,850......11,194......13,491.....$37,853.....$45,319 etc.This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.10 Windows Excel, PDF, HTML
October 27, 2010, 10:07 AM
GamP
Something like this will do what you want. There may also be other (possibly better) solutions.
TABLE FILE CAR
SUM
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
BY COUNTRY
SUM
DEALER_COST
RETAIL_COST
BY COUNTRY
ACROSS SEATS AS ''
COMPUTE DC_TOTAL/D12M = C1; AS 'Total DEALER_COST'
COMPUTE RC_TOTAL/D12M = C2; AS 'Total RETAIL_COST'
END
Hope this helps ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
October 27, 2010, 04:50 PM
Waz
Awesome solution.
May be able to make it smaller with a stylesheet, but why.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
October 27, 2010, 05:51 PM
Swamp
GamP that does do exactly what I described that I wanted.
But, Waz could you elaborate on how I might attack this problem with a stylesheet? Because my example was a simplification of what I am really trying to do, which involves FML. A method using a stylesheet would work much better with FML.
WebFOCUS 7.6.10 Windows Excel, PDF, HTML
October 27, 2010, 06:23 PM
Waz
Actually it looks a bit bigger.
This uses the SEQUENCE stylesheet option to place the columns in a specified position.
The issue with using this with ACROSS, is you would need to know how many across values there will be to determine the correct sequence number (column position).
I've specified numbers that are larger, which work, but its probably not a good idea.
TABLE FILE CAR
SUM
DEALER_COST/D12M AS 'Total DEALER_COST'
RETAIL_COST/D12M AS 'Total RETAIL_COST'
BY COUNTRY
SUM
DEALER_COST
RETAIL_COST
BY COUNTRY
ACROSS SEATS AS ''
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=N2, SEQUENCE=10,$
TYPE=REPORT, COLUMN=N3, SEQUENCE=11,$
ENDSTYLE
END
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 02, 2010, 02:05 PM
Swamp
Waz your answer was right on. Now here is EXACTLY what I am tring to do. Using this code:
SET BLANKINDENT=ON SET FORMULTIPLE = ON JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF TABLE FILE CENTGL SUM NAT_AMOUNT/D10.0 NAT_YTDAMT/D10.0 ACROSS PERIOD FOR GL_ACCOUNT 3100 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER BAR OVER 3100 ADD AS CAPTION LABEL GROSS OVER 3300 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER BAR OVER 3300 ADD AS CAPTION LABEL GROSS OVER BAR ON TABLE ROW-TOTAL END
I would like the dollar sign on the Selling Expenses and Salaries-Corporate lines ONLY, especially on the totals.
WebFOCUS 7.6.10 Windows Excel, PDF, HTML
November 02, 2010, 03:14 PM
GamP
SET BLANKINDENT=ON
SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM
NAT_AMOUNT/D10.0
NAT_YTDAMT/D10.0
ACROSS PERIOD
COMPUTE XYZZY1/D10M = C1+C5+C9+C13+C17+C21; AS 'Total amount'
COMPUTE XYZZY2/D10M = C3+C7+C11+C15+C19+C23; AS 'Total YTD'
FOR GL_ACCOUNT
3100 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER
BAR OVER
3100 ADD AS CAPTION LABEL GROSS OVER
3300 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER
BAR OVER
3300 ADD AS CAPTION LABEL GROSS OVER
BAR
END
This uses the same principle as before. And since you now compute totals over only the figures shown, the totals are now correct.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
November 02, 2010, 04:56 PM
Swamp
GamP,
That gives me dollar signs on ALL the rows of the totals but I would like the dollar sign on the Selling Expenses and Salaries-Corporate lines ONLY. Or, ideally, on the Selling Expenses and Salaries-Corporate total lines ONLY.
WebFOCUS 7.6.10 Windows Excel, PDF, HTML
November 02, 2010, 05:14 PM
Waz
This will be difficult with FML in place
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 02, 2010, 05:43 PM
Waz
Here is a solution.
Its not pretty!
SET BLANKINDENT=ON
SET FORMULTIPLE = ON
JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF
TABLE FILE CENTGL
SUM
NAT_AMOUNT/D10.0
NAT_YTDAMT/D10.0
ACROSS PERIOD
COMPUTE T_AMT/P10 = C1+C5+C9+C13+C17+C21; NOPRINT
COMPUTE T_YTD/P10 = C3+C7+C11+C15+C19+C23; NOPRINT
COMPUTE GLAC/A7 = FMLFOR('A7'); NOPRINT
COMPUTE D_AMT/A20 = IF GLAC EQ '3100' OR '3300'
THEN PTOA(T_AMT,'(P10MC)','A20')
ELSE PTOA(T_AMT,'(P10C)','A20') ; AS 'Total amount'
COMPUTE D_YTD/A20 = IF GLAC EQ '3100' OR '3300'
THEN PTOA(T_YTD,'(P10MC)','A20')
ELSE PTOA(T_YTD,'(P10C)','A20'); AS 'Total YTD'
FOR GL_ACCOUNT
3100 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER
BAR OVER
3100 ADD AS CAPTION LABEL GROSS OVER
3300 GET CHILDREN ALL WHEN EXISTS AS CAPTION OVER
BAR OVER
3300 ADD AS CAPTION LABEL GROSS OVER
BAR
END