Focal Point
For all WF reporting experts...here is a good challenge
June 11, 2012, 12:35 PM
rinksFor all WF reporting experts...here is a good challenge
I am stuck with a logic challenge. Its best illustrated by an example. This is how my hold file looks:
srt col1 col2 col3 period amount
1 a aa li1 1/1/2012 $11
2 a aa li1 1/2/2012 $12
3 a aa li3 ...
4 a aa li4 ...
5 c dd li5 ...
6 c dd li6 ...
7 d ee li7 ...
8 e ff li8 ...
note1: The "srt" is a sorting column holding numerical value in ascending order to help with placing rows in a particular order. Since the final output is a financial statement, this approach is handy.
note2: in the final output, the "period" column is displayed as across.
This is how I would like my final report to look:
srt col1 col2 col3 1/1/2012 1/2/2012
1 a aa li1 $11 $12
2 li2 ...
3 li3 ...
4 c dd li5 ...
5 li6 ...
6 d ee li7
7 e ff li8 ...
However for the life of me, I am not able to get the similar data to blank out with "SUM" command due to the srt column's changing values. I thought about using "COMPUTE" but WF complains BY TOTAL COMPUTE is now allowed with Across keyword.
Please please do not suggest "FOR" or financial reporting since it comes with its own set of headaces!!
Can anyone please help me!!!
WebFOCUS 7.6.1
Windows
ALL
June 11, 2012, 03:03 PM
j.grossIf you enclose the data and output samples in [ code ] tags, it's a lot easier on the audience:
srt col1 col2 col3 period amount
1 a aa li1 1/1/2012 $11
2 a aa li1 1/2/2012 $12
3 a aa li3 ...
4 a aa li4 ...
5 c dd li5 ...
6 c dd li6 ...
7 d ee li7 ...
8 e ff li8 ...
srt col1 col2 col3 1/1/2012 1/2/2012
1 a aa li1 $11 $12
2 li2 ...
3 li3 ...
4 c dd li5 ...
5 li6 ...
6 d ee li7
7 e ff li8 ...
As to your question: Try this
-- COMPUTE the line number
-- Use STYLE, SEQUENCE to promote the column from last to first position:
TABLE FILE CAR
WRITE SALES
BY COUNTRY
BY CAR
ACROSS SEATS
COMPUTE SEQ/I5=SEQ+1; AS 'srt'
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=SEQ, SEQUENCE=1, $
ENDSTYLE
END
June 11, 2012, 04:56 PM
rinksThanks very much for the reply.
And did learn something new with the "SEQUENCE" keyword. However what you suggested will not work for me and partly its due to how I explained my issue.
Attached is a picture of data with the issue.
http://imageshack.us/photo/my-images/24/issue1.png/ And this is how the code looks,
TABLE FILE HOLD1
SUM
'newamount' AS ''
BY Srt
BY InqSection
BY InqClass AS ''
BY InqDetail
BY InqItem
ACROSS newperiod2
So using your approach does not work for me since "Inqsection" or Col1 (from first post) will not be sorted in alpha order. Thats where SRT play a critial role. Infact SRT eventually becomes a hidden column, but for example purpose I am displaying it.
So I would want to show my data as
InqSection InqClass InqDetail InqItem ...
Revenue Gross Sales GAS LI1
LI2
LI3
Royalty GAS LI1
LI2
What i almost need is a way for WF to use "SRT" to sort overall data interally but when displaying ignore it and continue from InqSection onwards!!
Hope its making sense!!
WebFOCUS 7.6.1
Windows
ALL
June 11, 2012, 06:02 PM
j.grossquote:
What i almost need is a way for WF to use "SRT" to sort overall data interally but when displaying ignore it and continue from InqSection onwards
In my sample code, SALES plays the role of your Amount measure, and COUNTRY & CAR play the role of your grouping key values (InqSection, InqClass, InqDetail, InqItem).
I fail to see how the original setof Srt values of the several observations (observations with matching values of the grouping keys, but for different periods) that map into a row of output would play any role whatsoever in the sort order of the output rows. Can you explain what's wrong with
TABLE FILE HOLD1
SUM
'newamount' AS ''
-*
BY InqSection
BY InqClass AS ''
BY InqDetail
BY InqItem
ACROSS newperiod2
...
?
June 11, 2012, 07:34 PM
CrymsynI am a bit confused as well based on your example and how you said you wanted the final report to look j.gross's example is how you requested.
True the SRT column is changing values but your example for your output shows the same for when the LI1 InqItem lines go from two lines to one line the Li2's SRT value changes from 3 to 2.
Wanting all of the SRT values when you sum will not work unless you do a multi verb request but could you explain a bit more for why you need the SRT to use when placing rows in a particular order?
From your second post the reason why the InqSection isn't sorting in alphabetical order is because you are first sorting by SRT which should not be a BY field if you want to sum lines together.
WF: 8201, OS: Windows, Output: HTML, PDF, Excel
June 11, 2012, 10:35 PM
rinksThanks guys for really helping me out here.
To make it real simple, I need to hide the repeating values under Inqsection, inqclass, inqdetail. However I feel the SRT column is hindering the repeating values to hide due to how SUM and BY work.
If I don't use the SRT column, and for example do a "BY InqSection" (as my first sort order instead of BY srt) then I lose the expected and required ordering of item and instead items are sorted by InqSection. So sorting by Inqsection will display Exepense section followed by Revenue section...which isn't the order I am needing.
WebFOCUS 7.6.1
Windows
ALL
June 12, 2012, 03:50 AM
GamPYou'd have to play around a bit with the following code to make it work in your environment. Here's an attempt:
TABLE FILE FIXFILE
SUM NEWAMOUNT
BY SRT
BY INQSECTION
BY INQCLASS
BY INQDETAIL
BY INQITEM
ACROSS PERIOD
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA
END
DEFINE FILE HOLD
SECTION/A11 = IF INQSECTION EQ LAST INQSECTION THEN ' ' ELSE INQSECTION;
CLASS /A15 = IF INQCLASS EQ LAST INQCLASS THEN ' ' ELSE INQCLASS;
END
TABLE FILE HOLD
PRINT SRT AS 'Srt'
SECTION AS 'InqSection'
CLASS AS ''
INQDETAIL AS 'InqDetail'
INQITEM AS 'InqItem'
NEW112011 AS 'Nov 11'
NEW122011 AS 'Dec 11'
END
I have created a file called 'fixfile' that holds the data in an unsorted way. So the first I have to do is to sort the data in the desired way, while also doing the across, hold it and then just do print with using defines to blank out repeating values you do not wish to see.
Hope this sets you on your way...
GamP
- Using AS 8.2.01 on Windows 10 - IE11. |
| in Focus since 1988 |
June 12, 2012, 04:06 AM
TewyDo you need to do this in one pass?
With two passes you could do something like:
TABLE FILE file
SUM
AMOUNT
COMPUTE SORT1/I9=IF COL1 EQ LAST COL1 THEN LAST SORT1 ELSE SORT1+1;
COMPUTE SORT2/I9=IF COL2 EQ LAST COL2 THEN LAST SORT2 ELSE SORT2+1;
COMPUTE SORT3/I9=IF COL3 EQ LAST COL3 THEN LAST SORT3 ELSE SORT3+1;
BY SRT
BY COL1
BY COL2
BY COL3
BY PERIOD
ON TABLE HOLD
END
TABLE FILE HOLD
SUM
AMOUNT
BY SORT1 NOPRINT
BY COL1
BY SORT2 NOPRINT
BY COL2
BY SORT3 NOPRINT
BY COL3
BY SRT NOPRINT
ACROSS PERIOD
END
WF 7.6.11
Output: HTML, PDF, Excel
June 12, 2012, 08:25 AM
j.grossquote:
...sorting by Inqsection will display Exepense section followed by Revenue section...which isn't the order I am needing.
If that's your only problem, you can impose a different order: compute the desired ordering of Inqsection values (as numeric values that map 1-1 to the alpha values), and make it a non-printing sort key:
TABLE FILE HOLD1
SUM
'newamount' AS ''
AND COMPUTE order/I1=DECODE Inqsection('Revenue' 1, 'Expense' 2, ..., ELSE 99); NOPRINT
BY TOTAL order NOPRINT
BY InqSection
BY InqClass AS ''
BY InqDetail
BY InqItem
ACROSS newperiod2
...
END
Or use the ROWS option to specify the required order:
BY InqSection
ROWS
'Revenue'
OVER
'Expense'
...
June 12, 2012, 11:05 AM
linusI'm a bit confused, but if I follow you correctly in your data example,
srt col1 col2 col3 period amount
1 a aa li1 1/1/2012 $11
2 a aa li1 1/2/2012 $12
these two items really need to have the same value for srt you need to do something similar to the below define:
DEFINE FILE WHATEVER
SRTFLD/A1 = IF ((COL1 EQ 'A') AND (COL2 EQ 'AA')) THEN '1' ELSE
IF ((COL1 EQ 'C') AND (COL2 EQ 'DD')) THEN '2' ELSE
IF ((COL1 EQ 'D') AND (COL2 EQ 'EE')) THEN '3' ELSE etc...;
END
Then in table request do
BY SRTFLD NOPRINT
BY InqSection
BY InqClass AS ''
BY InqDetail
BY InqItem
ACROSS PERIOD
Hope this helps...
WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
June 13, 2012, 10:08 AM
rinksEveryone thanks for providing your expert opinions and advise. I did one thing correctly confuse everybody, i suppose :-) Nonetheless I believe I have my solution by taking ideas from you'll posts and from ibi support. So this is it.
SUM
COMPUTE test/A100 = IF InqSection EQ LAST InqSection AND InqClass THEN '' ELSE IF InqClass; AS ''
COMPUTE test2/A100 =IF InqClass EQ LAST InqClass THEN '' ELSE InqClass; AS ''
COMPUTE test3/A100 =IF InqDetail EQ LAST InqDetail THEN '' ELSE InqDetail; AS ''
BY Srt
SUM
'newamount' AS ''
BY Srt NOPRINT
BY InqSection NOPRINT
BY InqClass NOPRINT
BY InqDetail NOPRINT
BY cInqItem AS ''
ACROSS newperiod2 AS ''
Regards
RD
WebFOCUS 7.6.1
Windows
ALL
June 13, 2012, 03:40 PM
j.grossquote:
COMPUTE test/A100 = IF InqSection EQ LAST InqSection AND InqClass THEN '' ELSE IF InqClass; AS ''
Huh? That's not a valid COMPUTE.
Back to the issue -- please explain how the Srt values are assigned in the first place?
Is Srt unique (as I think we all assumed) -- if so you'll never get more than one of the ACROSS columns populated in any given row, if "BY Srt" is the high-order sort of the report.
On the other hand, if Srt is assigned *with duplicates*, based on section+class+detail but not period, so as to map the details into the row where they belong, then I don't know what was your problem, in the first place, that this TABLE code solves.
Please elucidate.