[SOLVED] Creating a row based on multiple row values
I am very new to WF and I have a sql report that I have created using all SQL- meaning Sum, Open PPms, Weekly Average and Total columns were created within the SQL statement.
I need to calculate a row for Backlog which needs to be the 'Open PPMs' row / 'Weekly Average' row and I am not familiar enough with the syntax yet, I am not sure what I need to do to accomplish this tasks. Any and all help is greatly appreciated.This message has been edited. Last edited by: Kerry,
June 02, 2009, 06:26 PM
Darin Lee
Are you wanting help with SQL or with FOCUS?
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
June 02, 2009, 06:29 PM
Doug
That's a good point Darin. The "NEW ROW INSERTED HERE" is SQL... Of course WebFOCUS (Maintain or MODIFY) can do that. So, knamc, where is your WebFOCUS Question?
June 03, 2009, 02:13 AM
<JG>
In WebFOCUS terms RECAP and SUBFOOT
June 03, 2009, 09:30 AM
<AM>
I need help in WebFocus, can you send me a sample of the RECAP, SUBFOOT code that I use as a guide? Thank you
June 03, 2009, 09:32 AM
<AM>
quote:
Originally posted by Doug: That's a good point Darin. The "NEW ROW INSERTED HERE" is SQL... Of course WebFOCUS (Maintain or MODIFY) can do that. So, knamc, where is your WebFOCUS Question?
THe "NEW ROW HERE...." is what I want to achieve in WebFOCUS
June 03, 2009, 10:43 AM
<JG>
RECAP SUBFOOT
It's all in the online help for dev studio.
Consultancy rates available on request.
It's a help forum not a do the work for you forum, At least until you have made some sort of attempt.
June 03, 2009, 12:02 PM
<AM>
I have created a statement:
RECAP BACKLOGVAL/D6.1= R12 / R13; AS 'Backlog'
But I an getting error message that it can not parse it, should the rows be explicitly defined, instead of the defaults?
There will always be 10 rows of detail, sum, openppm, average which will be fixed number of rows (13), I want to create a 14th row for Backlog.
June 03, 2009, 12:03 PM
Darin Lee
You don't help yourself by biting the hand that feeds you.
JG gave you what you need to know. If you go into the help in Dev Studio (not the forum) and look up the RECAP or SUBFOOT statements in the index, there are examples of the code. No need for others to re-invent. This information is also in the "Creating Reports" manual.
Once you look through the sample ocde, try a few things, and let us know what you've tried and what your additional questions are, we go from there.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
June 03, 2009, 01:35 PM
Doug
JG and Darin: You make very good points... "HELP NE DO"
As far as the "THe 'NEW ROW HERE....' is what I want to achieve in WebFOCUS"... a "NEW ROW" normally implies a new row in a DB which is avaiable via MAINTAIN or, do I dare say, MODIFY. However, if it's just a new row in a report, check out the RECAP as JG stated.
June 03, 2009, 02:44 PM
<AM>
Now that you have made each other all warm & fuzzy.
I am happy to report that I found what I needed from the online documentation, Thank you, JG.
My problem was that I was not exposed to the FML portion of the developer, now that I am familiarized with it I can see where this will make my development much easier. I have only been working in WebFOCUS for a month now and had training a year ago. So when I said I was a neebie I am very green in this product. This is all second nature to you "experts" so a little patience with us small guys goes a long way.
June 03, 2009, 03:00 PM
<AM>
Here is the result:
TABLE FILE SQLOUT SUM AIX/I5 LINUX/I5 AS 'Linux' SECURITYOPSDEV/I5 AS 'Security,Ops Dev' SOLARIS/I5 AS 'Solaris' FILEPRINT/I5 AS 'File and ,Print' VMWARE/I5 AS 'VMWare' WINDOWS/I5 AS 'Windows' TOTALS/I5 AS 'Grand Total' FOR LOOKUP_VALUE '03/28/2009' AS '03/28/2009' LABEL R1 OVER '04/04/2009' AS '04/04/2009' LABEL R2 OVER '04/11/2009' AS '04/11/2009' LABEL R3 OVER '04/18/2009' AS '04/18/2009' LABEL R4 OVER '04/25/2009' AS '04/25/2009' LABEL R5 OVER '05/02/2009' AS '05/02/2009' LABEL R6 OVER '05/09/2009' AS '05/09/2009' LABEL R7 OVER '05/16/2009' AS '05/16/2009' LABEL R8 OVER '05/23/2009' AS '05/23/2009' LABEL R9 OVER '05/30/2009' AS '05/30/2009' LABEL R10 OVER 'Sum' AS 'Sum' LABEL R11 OVER 'Open PPMs' AS 'Open PPMs' LABEL R12 OVER 'Weekly Average' AS 'Weekly Average' LABEL R13 OVER RECAP R14/D6.2=R12/R13; AS 'Backlog in Weeks'
June 03, 2009, 04:30 PM
Doug
quote:
all warm & fuzzy
imho: I may be speaking for others in saying that while we're here to help others in their quest to achieve their desired results, we're really not here to do their work for them.
I am glad that we guided you to the documentation and in such you found the answer to you question. I hope that you have not only achieved your desired goal, but, that you have also gained a greater understanding of the subject and the capabilities of WebFOCUS.