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

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

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

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

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

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.

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...

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

The solution is working.

Thanks for the help.

Welcome.

Hail Focal Point!