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,

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.

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.

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.

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


 

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

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?

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'
 

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?

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


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?

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


Unfortunately not Chuck. It's still including NBCAR in the Across.

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 

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!

zcbillions

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