I have a report requirement where I have to group by some columns then do Across with one column. When I do this for a simple table it is doing the across for all the columns. I am not able to think of doing this in webfocus. But I can write a once complex SQL and create a SQL report. is there a way we can add parameter/filters to the SQL report.
Please suggest.
Best Regards, PrakashThis message has been edited. Last edited by: <Emily McAllister>,
WebFocus 8, OS: iSeries
January 08, 2016, 04:41 AM
Ricardo Augusto
Sure.
First this is an example of a simple ACROSS report using CAR.MAS:
TABLE FILE CAR SUM SALES BY CAR ACROSS LOWEST COUNTRY ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE COLUMN-TOTAL AS 'TOTAL' ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = endeflt, $ ENDSTYLE END
Now an example of running your SQL with a parameter:
-**************************************** -* SET SQLMSS ENGINE TO RUN SQL QUERIES -****************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION YOUR_SQL_ADAPTER SQL SQLMSS PREPARE HOLD_SQL FOR
-**************************************** -* ADD YOUR SQL QUERY HERE -****************************************
SELECT * FROM MSRSD05_CITY WHERE SRSD05_STATE_C = '&STATE' ; END
-************** -* NOW REPORT IT -**************
TABLE FILE HOLD_SQL PRINT * END
WebFOCUS 8.1.05 / APP Studio
January 08, 2016, 04:40 PM
PrakashDT
Hi Ricardo,
First thanks for the replay.
And I am planing to work out this entire solution using the SQL report. However I want to see if I can get the report itself working rather having the business logic in the SQL.
Let me define the input and output so every one has better understanding of the issue.
I have following tables, - Employee Demography details [Ex record format: empid,name,street,state,zip] - Employee salary details [ this table is the child table for above table and contains 401K columns which I have to sum it up at each employee level] [Ex record format empid, month_btach_no, 401k_employe, 401k_employer] - Employee 401K loan details [ again this table is the child for above table, this table contains multiple record for each record in the salary table] [Ex record format: empid, month_batch_no , deduction_code, amount]
I understand the 401K columns will be the "by column" but when I make deduction code column from table 3 as a "across column" it is not giving me correct number.
Please help me construct the report now.
Best Regards, Prakash
WebFocus 8, OS: iSeries
January 09, 2016, 02:25 PM
Danny-SRL
Prakash, I think you could use a multi-set request:
-* File prakash01.fex
DEFINE FILE CAR
EMPLOYER401K/D6=RCOST;
EMPLOYEE401K/D6=DCOST;
DEDCODE/I2=SEATS;
AMOUNT/D6=SALES;
END
TABLE FILE CAR
SUM EMPLOYER401K EMPLOYEE401K
BY COUNTRY BY CAR
SUM AMOUNT
ACROSS DEDCODE
BY COUNTRY BY CAR
ON TABLE SET ACROSSTITLE SIDE
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
January 10, 2016, 09:07 PM
PrakashDT
quote:
ACROSS
I tried this with my master file but did not work out. It says "Multiple verb specifications are invalid"
This is the code :
TABLE FILE RETIREMENT SUM RETIREMENT.RETIREMENT.BASE_PAY RETIREMENT.RETIREMENT.GROSS_AMOUNT RETIREMENT.RETIREMENT.EMPLOYER_401K RETIREMENT.RETIREMENT.EMPLOYEE_401K RETIREMENT.RETIREMENT.TOTAL_HOURS BY RETIREMENT.RETIREMENT.EMPLOYEE_NAME BY RETIREMENT.RETIREMENT.EMPLOYEE_ADDRESS BY RETIREMENT.RETIREMENT.CITY BY RETIREMENT.RETIREMENT.ST_CODE BY RETIREMENT.RETIREMENT.ZIP_CODE BY RETIREMENT.RETIREMENT.SSN BY RETIREMENT.RETIREMENT.BIRTHDATE BY RETIREMENT.RETIREMENT.HIRE_DATE BY RETIREMENT.RETIREMENT.BASE_HOURLY_RATE SUM AMOUNT ACROSS CODE_TYPE ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty, $ ENDSTYLE END
Reporting is new to me. Also this scenario is also little bit different than regular summary reports.
Please suggest.
Best Regards, Prakash
WebFocus 8, OS: iSeries
January 11, 2016, 04:14 AM
Ricardo Augusto
Your code doesn´t look like Danny´s.
Try adding BY RETIREMENT.RETIREMENT.EMPLOYEE_NAME BY RETIREMENT.RETIREMENT.EMPLOYEE_ADDRESS BY RETIREMENT.RETIREMENT.CITY BY RETIREMENT.RETIREMENT.ST_CODE BY RETIREMENT.RETIREMENT.ZIP_CODE BY RETIREMENT.RETIREMENT.SSN BY RETIREMENT.RETIREMENT.BIRTHDATE BY RETIREMENT.RETIREMENT.HIRE_DATE BY RETIREMENT.RETIREMENT.BASE_HOURLY_RATE
After the ACROSS.
WebFOCUS 8.1.05 / APP Studio
January 11, 2016, 05:50 AM
Danny-SRL
Prakash, It looks like you were thrown into the deep end of the swimming pool without a buoy... It seems that some training is in order...
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
January 11, 2016, 11:23 AM
PrakashDT
Thanks Ricardo & Danny.
I will let every one know once I fix this.
Danny,
I had some formal training very recently but now I am getting this hand-on. I don't have any body inside my team who can guide me initially. But I think it just take little time.