Focal Point
Grouping Across Columns
August 03, 2007, 12:16 PM
sosterleGrouping Across Columns
OK, I know I know the answer to this, but I'm going crazy. I need to group some across data, following are the fields:
Sales Account - By field
Fiscal Year - Across field
Fiscal Period - Across field
# of Orders - Sum
Merch Sales - Sum
I need the finished product to be by Sales Account with all # of Orders columns by fiscal period and year and then all Merch sales by fiscal period and year. Currently, whenever I do the across, it puts the # of orders and Merch sales next to each other by fiscal period and year.
HELP!
PROD: WebFocus 7.6.9 on WinXP
August 03, 2007, 01:02 PM
GCohenIn my opinion the easiest way to do this is to use the Layout option. Place one report immediately next to the other. e.g.
Table file data
sum #of orders
by Account
across year across month
end
Table file data
sum merch sales
by account NOPRINT
across year across month
end
The two reports are positioned right next to each other in the layout painter.
Release 7.6.9
Windows
HTML
August 03, 2007, 01:57 PM
sosterleThank you so much for your reply, but I have a question. Doesn't that make two separate reports? I need them on the same report in excel format.
PROD: WebFocus 7.6.9 on WinXP
August 03, 2007, 02:14 PM
Francis MarianiI don't understand your requirements. Could you somehow illustrate it for us?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 03, 2007, 02:33 PM
sosterleOk, I hope this makes sense...
I originally built the report and it came out like this in excel:
2007
Jan Feb Mar
Sales Account # of Orders Sales # of Orders Sales # of Orders Sales
And they want it to come out like this, but everytime I make the fiscal period and year into Across fields, it goes across the # of Orders and Sales for each month together:
2007
Jan Feb Mar Jan Feb Mar
Sales Account # of Orders # of Orders # of Orders Sales Sales Sales
I really am sorry for being so confusing, but I hope this makes more sense.
Thank you so much for trying to help.
PROD: WebFocus 7.6.9 on WinXP
August 03, 2007, 03:18 PM
Francis MarianiWhat you originally had:
2007
Jan Feb Mar
Sales Account # of Orders Sales # of Orders Sales # of Orders Sales
------------- ----------- ----- ----------- ----- ----------- -----
What you're getting now:
2007
Jan Feb Mar Jan Feb Mar
Sales Account # of Orders # of Orders # of Orders Sales Sales Sales
------------- ----------- ----------- ----------- ----- ----- -----
What is the fiscal period? I assume it's NOT the Month. And it's a NOPRINT?
I think we need to see some of the code, particularly the BY and ACROSS statements.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 03, 2007, 03:25 PM
sosterleActually, Francis - you're close. The top example is what I'm getting....the bottom example is what I need. Does that make it any clearer? The fiscal period is the month.
PROD: WebFocus 7.6.9 on WinXP
August 03, 2007, 04:47 PM
FrankDutchquote:
- you're close
Post the fex.
You are going to make us guess, while you want us to help you.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
August 03, 2007, 04:56 PM
sosterleI'm very sorry, I wasn't trying to offend or frustrating. I didn't realize you were requesting the fex. This is it:
TABLE FILE INVOICE_DATA
SUM
CNT.DST.Real_Order/I11C AS '# of Orders'
NET_EXT/I9C AS 'Net Sales'
BY ACCOUNT_NAME
BY FISCAL_YEAR
BY FISCAL_PERIOD
WHERE FISCAL_YEAR GE 2006;
WHERE ROLLUP_TYPE EQ '1';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDTOTSALES FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
JUSTIFY=CENTER,
$
ENDSTYLE
END
TABLE FILE HOLDTOTSALES
SUM
'# OF ORDERS'
'NET SALES'
BY ACCOUNT_NAME AS ''
ACROSS FISCAL_YEAR AS ''
ACROSS FISCAL_PERIOD AS ''
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=ACROSSVALUE,
ACROSS=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
ACROSS=2,
SIZE=11,
STYLE=BOLD,
$
TYPE=ACROSSTITLE,
ACROSS=1,
JUSTIFY=CENTER,
$
TYPE=ACROSSTITLE,
ACROSS=2,
JUSTIFY=CENTER,
$
TYPE=GRANDTOTAL,
SIZE=14,
COLOR='BLACK',
STYLE=BOLD+ITALIC,
$
ENDSTYLE
END
PROD: WebFocus 7.6.9 on WinXP
August 04, 2007, 08:02 AM
FrankDutchThere will be more than one solution.
This is what you can do
DEFINE FILE HOLDTOTSALES
DESCR/A10='Orders';
AMOUNT/I9='# of Orders';
END
TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY ACCOUNT_NAME
BY FISCAL_YEAR
BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD1
END
DEFINE FILE HOLDTOTSALES
DESCR/A10='Sales';
AMOUNT/I9='Net Sales';
END
TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY ACCOUNT_NAME
BY FISCAL_YEAR
BY FISCAL_PERIOD
BY DESCR
ON TABLE HOLD AS HOLD2
END
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLDTOT
MORE
FILE HOLD2
END
-* now create your report with an extra across
TABLE FILE HOLDTOTSALES
SUM AMOUNT
BY ACCOUNT_NAME AS ''
ACROSS DESCR AS ''
ACROSS FISCAL_YEAR AS ''
ACROSS FISCAL_PERIOD AS ''
ACROSS DESCR AS ''
END
If you want you can add a across subtotal per description per year.
The format of the two amounts are the same now, but in the report that can be changed on the condition of DESCR
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
August 06, 2007, 09:48 AM
sosterleYou are so wonderful....thank you for your help!
PROD: WebFocus 7.6.9 on WinXP