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
fml help?
 Login/Join
 
Platinum Member
posted
I'm trying to do a crosstab that would (in CAR terms) report the avg gross pay, and avg deduction amounts, by department, across the months of a year, and then have a yearly avg for each of those at the end:
                    Jan    Feb    Mar   Apr   ...     2005
Dept
Production   Gross    n      n      n     n              n
             Ded      m      m      m     m              m
MIS          Gross
             Ded


I can do the crosstab and even use OVER to display the Gross over Ded, but it's the year end average that I'm having difficulty with. I'm using the multi-verb approach, but when I do it screws up the entire report.

Is this better done with FML? Any pointers?

This message has been edited. Last edited by: MacLonghorn,


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Virtuoso
posted Hide Post
Mark

If you post a snippet of your code we might be able to see better what you're up to.

If you post your example you better can put this [code ] [/code ] around it (without the spaces).




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
Here's a quick sample:
  
DEFINE FILE EMPLOYEE
	PAYDTYM/YYM = PAY_DATE;
END
TABLE FILE EMPLOYEE
SUM
	GROSS OVER
	DED_AMT
BY DEPARTMENT
ACROSS PAYDTYM
END
 


This does a sum, but I'm trying to do a monthly average, and then on top of that, have a yearly average at the end, to the right of the last month. Also, I need to display the year end averages OVER each other, as in the main report.


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Platinum Member
posted Hide Post
I think I've got a solution. Instead of using OVER, I can transform the different values (GROSS and DED_AMT) into a single column, using MORE. Then I can use the BY groupings instead of over....
 
SET ASNAMES = ON
DEFINE FILE EMPLOYEE
	PAYDTYM/YYM = PAY_DATE;
	GROSSTYPE/A20 = 'GROSSAMT';
	DEDTYPE/A20 = 'DEDAMT';
END
TABLE FILE EMPLOYEE
PRINT
	DEPARTMENT
	PAYDTYM
	GROSSTYPE AS MEASTYPE
	GROSS AS MEASURE
ON TABLE HOLD AS R1
END
TABLE FILE EMPLOYEE
PRINT
	DEPARTMENT
	PAYDTYM
	DEDTYPE
	DED_AMT
ON TABLE HOLD AS R2
END
TABLE FILE R1
PRINT *
ON TABLE HOLD AS RPT1
MORE
FILE R2
END
TABLE FILE RPT1
SUM AVE.MEASURE NOPRINT
BY DEPARTMENT
BY MEASTYPE
SUM AVE.MEASURE AS ''
BY DEPARTMENT
BY MEASTYPE
ACROSS PAYDTYM
COMPUTE AVGMEAS/D12.2 = C1; AS 'YEARLY AVG'
COMPUTE DIFF/D12.2 = AVGMEAS - C11;
END
 


But is there a nicer, easier way to do this with FML?


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
Expert
posted Hide Post
Mark,

One of the problems with achieving a similar result using FML is that you would need to "invent" additional columns.

Now this can be done by using the COLUMNS syntax on the ACROSS verb, but you would need to have an across value that allowed you to force two new columns. For instance, you couldn't just use the format MT and then have COLUMNS 'JAN' AND .... 'DEC' AND 'COL1' AND 'COL2' because you would get a data value format error (COL1 isn't an MT format). What I would do is convert the month into an alpha string and then force the two additional columns, as COL1 is an acceptable alpha value.

Another problem that you would encounter is, to get the deductions over the gross, you would have to use OVER between the summed values. This would mean that the cell annotation that you would have to use in the RECAP statements would have to be carefully done.

See this example using the same data as per yours -

DEFINE FILE EMPLOYEE
  PAYDATE/A17 = EDIT(PAY_DATE);
-* Convert the paydate (I6YMD) to Alpha
  PAYMNTH/A4  = CHGDAT('YMD','MT',PAYDATE,'A4');
  PAYYEAR/YY  = PAY_DATE;
END
-* Get a count of columns for the calculations
TABLE FILE EMPLOYEE
BY PAYMNTH
WHERE PAYYEAR EQ 1982
ON TABLE SAVE
END
-RUN
-SET &Cols = &LINES;
-*
TABLE FILE EMPLOYEE
  SUM AVE.DED_AMT AS 'Deductions'
 OVER AVE.GROSS   AS 'Gross'
-* Force your columns using COLUMNS ... AND ...
ACROSS PAYMNTH AS '' COLUMNS 'JAN' AND 'FEB' AND 'MAR' AND 'APR' AND 'MAY' AND 'JUN'
                         AND 'JUL' AND 'AUG' AND 'SEP' AND 'OCT' AND 'NOV' AND 'DEC'
-* Force the two extract columns required
                         AND 'AVG' AND 'DIFF'
WHERE PAYYEAR EQ 1982
-* Now the FML bit
FOR DEPARTMENT
'MIS'        LABEL MIS OVER
'PRODUCTION' LABEL PRD OVER
RECAP MIS(25) = (MIS(1)  + MIS(3)  + MIS(5)  + MIS(7)  + MIS(9)  + MIS(11)
              +  MIS(13) + MIS(15) + MIS(17) + MIS(19) + MIS(21) + MIS(23)) / &Cols; OVER
RECAP MIS(26) = (MIS(2)  + MIS(4)  + MIS(6)  + MIS(8)  + MIS(10) + MIS(12)
              +  MIS(14) + MIS(16) + MIS(18) + MIS(20) + MIS(22) + MIS(24)) / &Cols; OVER
RECAP PRD(25) = (PRD(1)  + PRD(3)  + PRD(5)  + PRD(7)  + PRD(9)  + PRD(11)
              +  PRD(13) + PRD(15) + PRD(17) + PRD(19) + PRD(21) + PRD(23)) / &Cols; OVER
RECAP PRD(26) = (PRD(2)  + PRD(4)  + PRD(6)  + PRD(8)  + PRD(10) + PRD(12)
              +  PRD(14) + PRD(16) + PRD(18) + PRD(20) + PRD(22) + PRD(24)) / &Cols; OVER
-* Here you need to achieve similar calculations for the DIFF column
""
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=8, $
ENDSTYLE
END

Not sure if you think this is a "nicer, easier" method than that you already have, but it is in FML as you wanted. For my money this is the way to go as is only needs one proper pass of the data (the extract for the &Cols aside).

Enjoy

T

Edited to correct spelling Roll Eyes

This message has been edited. Last edited by: Tony A,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Excellent, thanks Tony. Good FML lesson/technique for me. I think I'll stick with the prior method and try to make as few passes as possible.


Thanks.

Mark
WF 7.6 Windows
 
Posts: 150 | Registered: July 26, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders