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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use a computed field in the By Clause
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Use a computed field in the By Clause
 Login/Join
 
Gold member
posted
Hi all,

I have a report where I have some By fields and some computed measures under an Across.I want to Count the Number of months and have them as a by, but InfoAssist won't let me use the Compute as a By. Is there anyway around this. I don't want the count used in the Across, I want it used in the By.

Thank you in advance!

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Expert
posted Hide Post
To use a COMPUTEd field as a BY field, you would normally add TOTAL, like BY TOTAL computed_field.

Not sure if you can do this in InfoAssist.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
zcbillions

If the BY TOTAL doesn't work for you then do your COMPUTE and create a HOLD file and then you can sort by the value of the compute when you report against the hold file.

This can be done with InfoAssist in a single report.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Awesome, thank you for the advice! So I have tried creating a hold file with the computed field and then using the computed field in the report.

Let me make sure I'm doing this correctly... We have about 5 fields using in the BY clauses, 2 WHERE clauses and 2 ACROSS fields. I understand that I need to have all of those fields in the hold file since that will be the new file I use to create the report. So I set up the BYs, WHERE clauses, and I think it's complaining about the ACROSS clauses in the report. If I don't use the ACROSS clauses in the hold file, the data is structured wrong.


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
Not sure in which version you are, but in 8206 you can sort on a computed field, resulting in a by total compute:

 
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=2;
SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/car
SUM COMPUTE Compute_1/D12.2=CAR.BODY.SEATS * CAR.BODY.SALES ; NOPRINT
BY TOTAL HIGHEST Compute_1
BY CAR.CARREC.MODEL
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END

-RUN


 


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Interesting. It's the ACROSS Fields messing it up.

I've tried a lot of different ways and cant seem to get anything to work.
Here's my code:
  
-*COMPONENT=Define_hyp_cp_labor_util_actual
DEFINE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
 Employee/A101=EMPL_ID || ( ' - ' | FIRST_NAME || ( ' ' | LAST_NAME ) ) ;
 TYPES/A101=IF HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY EQ 'AT RISK' OR 'FIXED PRICE' OR 'T&|M' THEN 'BILLABLE' ELSE IF HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.UTIL_CATEGORY NE 'AT RISK' OR 
'FIXED PRICE' OR 'T&|M' OR 'CUSTOMER SUPPORT' OR 'LEAVE' OR 'OTHER' THEN 'NON-BILLABLE' ELSE '' ;

END

TABLE FILE si_bi/hyp_tables/hyp_cp_labor_util_actual
SUM  
COMPUTE NBCAR /D12 = CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.WEEK_NUM; NOPRINT
ENTERED_HRS
TYPES 
UTIL_CATEGORY
ORG_TYPE
STARTDATE
TYPES
BY Employee
BY COUNTRY
BY BU_INDUSTRY
BY DEPARTMENT
BY TOTAL HIGHEST NBCAR
ACROSS HIGHEST TYPES AS ' '
ACROSS UTIL_CATEGORY AS ' ' RECOMPUTE  AS 'Subtotal:'

WHERE ORG_TYPE EQ 'CUSTOMER SUPPORT' OR 'NON-SERVICES' OR 'SERVICES BILLABLE' OR 'SERVICES NON BILLABLE';
WHERE STARTDATE EQ '20190629' OR '20190706';
ON TABLE PCHOLD FORMAT HTML
ON TABLE ROW-TOTAL 
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENInformationBuilders_Light2.sty,$
TYPE=REPORT, $
ENDSTYLE
END


-*
-*
-*TABLE FILE WeekCount
-*SUM 
-*BY Employee
-*BY COUNTRY
-*BY BU_INDUSTRY
-*BY DEPARTMENT
-* NBCAR
-*ACROSS HIGHEST TYPES AS ' '
-*ACROSS UTIL_CATEGORY AS ' ' RECOMPUTE  AS 'Subtotal:'
-*
-*WHERE ORG_TYPE EQ 'CUSTOMER SUPPORT' OR 'NON-SERVICES' OR 'SERVICES BILLABLE' OR 'SERVICES NON BILLABLE';
-*WHERE STARTDATE EQ '20190629' OR '20190706';
-*ON TABLE PCHOLD FORMAT HTML
-*ON TABLE ROW-TOTAL 
-*ON TABLE NOTOTAL
-*ON TABLE SET CACHELINES 100
-*ON TABLE SET GRWIDTH 1
-*ON TABLE SET STYLE *
-*INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENInformationBuilders_Light2.sty,$
-*TYPE=REPORT, $
-*ENDSTYLE
-*END
-*
-*-RUN


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Gold member
posted Hide Post
It's definitely the across fields that are messing up the report. I've tried doing the Compute in a hold file. I've tried doing the compute after the Across fields.

Any other ideas?


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
zcbillions

I noticed that your compute is simply a CNT.DST You don't need a compute to do that. Go back to your original report and use

 
SUM
CNT.DST.HYP_CP_LABOR_UTIL_ACTUAL.HYP_CP_LABOR_UTIL_ACTUAL.WEEK_NUM AS 'NBCAR'
 


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks for you help on this Chuck!

So that worked in the sum, and it's including the field in the ACROSS clause. We would like that count distinct as a BY, so it wouldn't be used in the ACROSS. I think that's why we created it as a compute.

Is there any way to do this?


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Zcbillions

Then if no across is required it goes back to Frans example. I modified it a little to use your CNT.DST in the compute.


ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=2;
SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/car
SUM COMPUTE NBCAR/D12.2=CNT.DST.CAR.BODY.BODYTYPE ; NOPRINT
DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
BY TOTAL HIGHEST NBCAR
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
I'm sorry Chuck. I may have confused you sir.

The across is needed. It's needed for the ENTERED_HRS measure, that's the only field the users want in the Across. But for the CNT.DST.WEEK_NUM measure, we want to display that as a BY, not summed in the Across. Does that make sense?


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
zcbillions

How about this?


ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE
SET ASNAMES=ON
SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=2;
SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/car
SUM COMPUTE NBCAR/D12.2=CNT.DST.CAR.BODY.BODYTYPE ; AS 'NBCAR'
DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
-*BY TOTAL HIGHEST NBCAR
ON TABLE HOLD AS CARHOLD
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END
-RUN
TABLE FILE CARHOLD
SUM
NBCAR
DEALER_COST
BY COUNTRY
ACROSS CAR
-*BY TOTAL HIGHEST NBCAR
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Unfortunately not Chuck. It's still including NBCAR in the Across. Frowner


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
a smal adjustment on Chuck's code:

 ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE
SET ASNAMES=ON
SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=2;
SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/car
SUM 
-*COMPUTE NBCAR/D12.2=CNT.DST.CAR.BODY.BODYTYPE ; AS 'NBCAR'
DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY TOTAL COMPUTE   NBCAR/D12.2=CNT.DST.CAR.BODY.BODYTYPE ;
BY CAR.COMP.CAR
-*BY TOTAL HIGHEST NBCAR
ON TABLE HOLD AS CARHOLD
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
-*INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END
-RUN
TABLE FILE CARHOLD
SUM

DEALER_COST

BY HIGHEST NBCAR NOPRINT
BY COUNTRY
BY NBCAR
ACROSS CAR

ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLE *
-*INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, PAGESIZE=A4, $
ENDSTYLE
END 


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Frans,

Thank you so much!! Works perfectly. I knew there had to be a way.

Chuck,

Thank you as well for your time and help as well! What a wonderful community.

How do I make this thread Solved?

Cheers!


WebFOCUS 8
Windows, All Outputs
 
Posts: 75 | Registered: December 06, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
zcbillions

Edit your first post and change the summary line to [SOLVED]


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Use a computed field in the By Clause

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