Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2015Report 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, 2006Report 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, 2015Report 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: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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, 2015Report 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, 2006Report 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: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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, 2015Report This Post
Member
posted Hide Post
The solution is working.

Thanks for the help.


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

Hail Focal Point!


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

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders