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
  
[CLOSED] ACROSS IF?
 Login/Join
 
Gold member
posted
I need to sum columns by month, quarter, or year, depending on radio button selected. Is something like this possible?

ACROSS IF &Agro EQ 1 THEN J0.CMSBESTPRACTICESDATA.DATE_MYY AS 'Month Year'
ELSE IF &Agro EQ 3 THEN J0.CMSBESTPRACTICESDATA.HEAD_QUARTER AS 'Quarter Year'
ELSE J0.CMSBESTPRACTICESDATA.HEAD_YEAR AS 'Year';

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 70 | Registered: February 08, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
-* &BYSELECT it's your input parameter that will assigne the proper field / title
-DEFAULTH &BYSELECT = 2
-SET &BYACR = DECODE &BYSELECT (1 REGION
-                               2 ST
-                               3 CITY
-                            ELSE DATE);

-SET &BYACRT = DECODE &BYSELECT (1 'Region'
-                                2 'State'
-                                3 'City'
-                             ELSE 'Date');

TABLE FILE GGSALES
SUM DOLLARS
ACROSS &BYACR AS '&BYACRT'
END


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2105 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
CMSBESTPRACTICESDATA.CMSBESTPRACTICESDATA.PROVIDER IN CMSBESTPRACTICESDATA
TAG J0 AS J0
END
SET NODATA = 0
SET &Bound = DECODE &Agro (1 J0.CMSBESTPRACTICESDATA.DATE_MYY 3 J0.CMSBESTPRACTICESDATA.HEAD_QUARTER ELSE J0.CMSBESTPRACTICESDATA.HEAD_YEAR);
SET &Header = DECODE &Agro (1 'Month Year' 3 'Quarter Year' ELSE 'Year');
TABLE FILE HOLDFILEDATA
-*ON TABLE PCHOLD FORMAT XLSX
SUM
J0.CMSBESTPRACTICESDATA.TOTAL_CASES AS 'Total Cases'
J0.CMSBESTPRACTICESDATA.DENOMINATOR_CALC_2 AS 'Opportunities'
J0.CMSBESTPRACTICESDATA.NUMERATOR_CALC_2 AS 'Failures'
BY HOLDFILEDATA.SEG01.PROVIDER AS 'Provider'
BY J0.CMSBESTPRACTICESDATA.SERVICE AS 'Service'
-* ACROSS LOWEST J0.CMSBESTPRACTICESDATA.DATE_MYY AS 'Month Year'
ACROSS &Bound AS '&Header'

When I run this, the report asks for the values of &Bound and &Header. It has the their default values and it runs with those, but the SET statements don't seem to have executed, or maybe they have as they would return the same as the default, but the report doesn't continue with their values.


WebFOCUS 8
Windows, All Outputs
 
Posts: 70 | Registered: February 08, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You need a - (dash) before the SET of Bound and Header


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2105 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
@mbondr: -SET populates variables in DIALOGUE MANAGER (https://webfocusinfocenter.informationbuilders.com/wfappent/pdfs4/wfappslang.pdf). SET adjusts the environment. MartinY's code is using -SET.


WebFOCUS 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Added the hyphens in front of SET to no effect, still just the default values and the report isn't running with them, but instead is waiting for values.

Could it be where the SET's are? As you may have guessed, I'm a total untrained nube tossed into the deep end.

(see?)

-SET &Bound = DECODE &Agro (1 J0.CMSBESTPRACTICESDATA.DATE_MYY 3 J0.CMSBESTPRACTICESDATA.HEAD_QUARTER ELSE J0.CMSBESTPRACTICESDATA.HEAD_YEAR);
-SET &Header = DECODE &Agro (1 'Month Year' 3 'Quarter Year' ELSE 'Year');


WebFOCUS 8
Windows, All Outputs
 
Posts: 70 | Registered: February 08, 2018Reply With QuoteReport This Post
Silver Member
posted Hide Post
This should get you what you need

 -DEFAULT &BYDISPLAY = '_FOC_NULL';


-SET &BYDATE = IF &BYDISPLAY EQ 'Quarter' THEN 'CMSBESTPRACTICESDATA.CMSBESTPRACTICESDATA.DATE_QYY' ELSE IF &BYDISPLAY EQ 'Year' THEN 'CMSBESTPRACTICESDATA.CMSBESTPRACTICESDATA.DATE_YY' ELSE 'CMSBESTPRACTICESDATA.CMSBESTPRACTICESDATA.DATE_MYY';
-SET &BYDATENAME = IF &BYDISPLAY EQ 'Quarter' THEN 'Quarter' ELSE IF &BYDISPLAY EQ 'Year' THEN 'Year' ELSE 'Month';

TABLE FILE DATA
SUM
field 1
field 2
ACROSS LOWEST &BYDATE AS &BYDATENAME
 


WebFOCUS 8204
Windows, All Outputs
 
Posts: 43 | Registered: March 21, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 


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