Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]SQL Report
 Login/Join
 
Member
posted
Hi,

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,
Prakash

This message has been edited. Last edited by: <Emily McAllister>,


WebFocus 8, OS: iSeries
 
Posts: 22 | Registered: September 23, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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]

Output format : Empid,Name,Street,State,Zip, sum(401k_Employee), sum(401K_Employer), Deduction_code1, Sum(Amount), Deduction_code2, Sum(Amount), Deduction_code3, Sum(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
 
Posts: 22 | Registered: September 23, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 22 | Registered: September 23, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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.

Best Regards,
Prakash


WebFocus 8, OS: iSeries
 
Posts: 22 | Registered: September 23, 2015Reply With QuoteReport This Post
Member
posted Hide Post
The solution is working.

Thanks for the help.


WebFocus 8, OS: iSeries
 
Posts: 22 | Registered: September 23, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
Welcome.

Hail Focal Point!


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.