In making a custom Usage report for my management, I found that in several of my reports, the .FEX is run several times for each time the report is run. And it is not always a constant multiplier. Depending on which parameters are chosen, the .FEX may be run 4 times or it may be run 7 times. Therefore I created a COMPUTE to divide by an average number for each report.

My problem is this:
Sometimes, depending on parameters chosen, the .FEX is run less than the average divisor and my compute comes out to be 0. I do not want this. I would like it to show a 1 when the calculation = 0.

So this is what I tried:
[Origional]--IF SMRPCNAME EQ 'mits_zip_report' THEN ( ( CNT.SMKEY ) / 3 ) ELSE CNT.SMKEY
-----PRODUCES 0's

[1st attempt]--IF SMRPCNAME EQ 'mits_zip_report' AND (((CNT.SMKEY) / 3) NE 0) THEN ( ( CNT.SMKEY ) / 3 ) ELSE 1 ELSE CNT.SMKEY
-----FOR SOME REASON THIS COMPLETLY REMOVED THE REPORT FROM THE OUTPUT.

[2nd attempt]--IF SMRPCNAME EQ 'mits_zip_report' AND (((CNT.SMKEY) / 3) EQ 0) THEN 1 ELSE IF SMRPCNAME EQ 'mits_zip_report' AND (((CNT.SMKEY) / 3) NE 0) THEN ( ( CNT.SMKEY ) / 3 ) ELSE CNT.SMKEY
-----THE REPORT IS BACK, BUT STILL SHOWING 0'S.

Can anyone shed some insight on an easier way to convert the 0 from the computation to a 1?

Thanks,

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

Why are you dividing by 3?

I asume this report is looped a few times depending on variables. Isn't there a main report which is ran once every time, then you can cnt this.

Not that I have found. This is the most reliable count I could get for this report.

I am dividing by 3 because that seems to be the best average, but sometimes it will only execute the fex twice.

quote:

ELSE 1 ELSE CNT.SMKEY

Did you get a syntax error on this? Also if CNT.SMKEY is not 0 THEN ((CNT.SMKEY) / 3) is not zero even if it is less than 1.

Okay, so my first problem was not realizing that my calculation was not equaling 0, it was between 0 and 1. So I now have two checks for each report. First, if the calculation is less than 1, then make it 1. Second, if the calculation is greater than .99, then value = calculation. I also have a final else at the bottom for those .fex's that are only executed once.

Code:
COMPUTE REALCNT/I3 = IF SMRPCNAME EQ 'mits_zip_report' AND ((CNT.SMKEY) / 3) LT 1 THEN 1
ELSE IF SMRPCNAME EQ 'mits_zip_report' AND ((CNT.SMKEY) / 3) GT .99 THEN ((CNT.SMKEY) / 3)
ELSE CNT.SMKEY;

It works fine when only checking for one SMRPCNAME, like the example, but when I add more lines for others, I get a (FOC266) IF, THEN, ELSE syntax error:

Code:
COMPUTE REALCNT/I3 = IF SMRPCNAME EQ 'mits_zip_report' AND ((CNT.SMKEY) / 3) LT 1 THEN 1
ELSE IF SMRPCNAME EQ 'mits_zip_report' AND ((CNT.SMKEY) / 3) GT .99 THEN ((CNT.SMKEY) / 3)
ELSE IF SMRPCNAME EQ 'seperate_entity_report' AND ((CNT.SMKEY / 12) LT 1 THEN 1
ELSE IF SMRPCNAME EQ 'seperate_entity_report' AND ((CNT.SMKEY / 12) GT .99 THEN ( ( CNT.SMKEY ) / 12 )
ELSE CNT.SMKEY;

I don't understand what is wrong. When I was not doing the AND check in each line, multiple lines worked together.

All help is greatly appreciated.

Thanks,

Could you post the entire program please? Do you have a BY on SMRPCNAME?

Yes, I did have a BY on SMRCPNAME. However, it was a No Print, so I removed it.

Also, I added some parentheses in the compute.

After doing both of these, I was able to get two more lines working (1 other report's worth), but no more.

Here is the entire report (working with the extra compute lines commented out):

TABLE FILE WF_USERS
SUM
COMPUTE REALCNT/I4 = IF (SMRPCNAME EQ 'mits_zip_report') AND ((( CNT.SMKEY ) / 3 ) LT 1) THEN 1
ELSE IF (SMRPCNAME EQ 'mits_zip_report') AND ((( CNT.SMKEY ) / 3 ) GT .99) THEN ( ( CNT.SMKEY ) / 3 )
ELSE IF (SMRPCNAME EQ 'seperate_entity_report') AND (((CNT.SMKEY) / 12) LT 1) THEN 1
ELSE IF (SMRPCNAME EQ 'seperate_entity_report') AND (((CNT.SMKEY) / 12) GT .99) THEN ( ( CNT.SMKEY ) / 12 )
-* ELSE IF (SMRPCNAME EQ 'waiver_report') AND (((CNT.SMKEY / 2) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'waiver_report') AND (((CNT.SMKEY / 2) GT .99) THEN ( ( CNT.SMKEY ) / 2 )
-* ELSE IF (SMRPCNAME EQ 'findings_and_collections_report') AND (((CNT.SMKEY / 2) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'findings_and_collections_report') AND (((CNT.SMKEY / 2) GT .99) THEN ( ( CNT.SMKEY ) / 2 )
-* ELSE IF (SMRPCNAME EQ 'fc_analysis_report') AND (((CNT.SMKEY / 7) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'fc_analysis_report') AND (((CNT.SMKEY / 7) GT .99) THEN ( ( CNT.SMKEY ) / 7 )
-* ELSE IF (SMRPCNAME EQ 'aggr1040') AND (((CNT.SMKEY / 6) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'aggr1040') AND (((CNT.SMKEY / 6) GT .99) THEN ( ( CNT.SMKEY ) / 6 )
-* ELSE IF (SMRPCNAME EQ 'cp_compensation_main') AND (((CNT.SMKEY / 3) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'cp_compensation_main') AND (((CNT.SMKEY / 3) GT .99) THEN ( ( CNT.SMKEY ) / 3 )
-* ELSE IF (SMRPCNAME EQ 'home') AND (((CNT.SMKEY / 4) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'home') AND (((CNT.SMKEY / 4) GT .99) THEN ( ( CNT.SMKEY ) / 4 )
-* ELSE IF (SMRPCNAME EQ 'revenue_report') AND (((CNT.SMKEY / 5) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'revenue_report') AND (((CNT.SMKEY / 5) GT .99) THEN ( ( CNT.SMKEY ) / 5 )
-* ELSE IF (SMRPCNAME EQ 'wagesumy') AND (((CNT.SMKEY / 6) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'wagesumy') AND (((CNT.SMKEY / 6) GT .99) THEN ( ( CNT.SMKEY ) / 6 )
-* ELSE IF (SMRPCNAME EQ '1040_ptc_report') AND (((CNT.SMKEY / 8) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ '1040_ptc_report') AND (((CNT.SMKEY / 8) GT .99) THEN ( ( CNT.SMKEY ) / 8 )
-* ELSE IF (SMRPCNAME EQ 'cycle_time') AND (((CNT.SMKEY / 4) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'cycle_time') AND (((CNT.SMKEY / 4) GT .99) THEN ( ( CNT.SMKEY ) / 4 )
-* ELSE IF (SMRPCNAME EQ 'irs1040') AND (((CNT.SMKEY / 2) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'irs1040') AND (((CNT.SMKEY / 2) GT .99) THEN ( ( CNT.SMKEY ) / 2 )
-* ELSE IF (SMRPCNAME EQ 'cp_wage_summary') AND (((CNT.SMKEY / 5) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'cp_wage_summary') AND (((CNT.SMKEY / 5) GT .99) THEN ( ( CNT.SMKEY ) / 5 )
-* ELSE IF (SMRPCNAME EQ 'hjkhd3rb') AND (((CNT.SMKEY / 8) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'hjkhd3rb') AND (((CNT.SMKEY / 8) GT .99) THEN ( ( CNT.SMKEY ) / 8 )
-* ELSE IF (SMRPCNAME EQ 'fcb_corporate_report') AND (((CNT.SMKEY / 4) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'fcb_corporate_report') AND (((CNT.SMKEY / 4) GT .99) THEN ( ( CNT.SMKEY ) / 4 )
-* ELSE IF (SMRPCNAME EQ 'kw_wage_summary_report') AND (((CNT.SMKEY / 5) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'kw_wage_summary_report') AND (((CNT.SMKEY / 5) GT .99) THEN ( ( CNT.SMKEY ) / 5 )
-* ELSE IF (SMRPCNAME EQ 'account_detail_level_1') AND (((CNT.SMKEY / 4) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'account_detail_level_1') AND (((CNT.SMKEY / 4) GT .99) THEN ( ( CNT.SMKEY ) / 4 )
-* ELSE IF (SMRPCNAME EQ 'cp_hb6002') AND (((CNT.SMKEY / 5) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'cp_hb6002') AND (((CNT.SMKEY / 5) GT .99) THEN ( ( CNT.SMKEY ) / 5 )
-* ELSE IF (SMRPCNAME EQ 'seperate_entity_report1') AND (((CNT.SMKEY / 12) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'seperate_entity_report1') AND (((CNT.SMKEY / 12) GT .99) THEN ( ( CNT.SMKEY ) / 12 )
-* ELSE IF (SMRPCNAME EQ '1120_aggregate_report') AND (((CNT.SMKEY / 10) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ '1120_aggregate_report') AND (((CNT.SMKEY / 10) GT .99) THEN ( ( CNT.SMKEY ) / 10 )
-* ELSE IF (SMRPCNAME EQ '120_list_box_creation_test') AND (((CNT.SMKEY / 7) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ '120_list_box_creation_test') AND (((CNT.SMKEY / 7) GT .99) THEN ( ( CNT.SMKEY ) / 7 )
-* ELSE IF (SMRPCNAME EQ 'cp_vendor_prac_vendor') AND (((CNT.SMKEY / 2) LT 1) THEN 1
-* ELSE IF (SMRPCNAME EQ 'cp_vendor_prac_vendor') AND (((CNT.SMKEY / 2) GT .99) THEN ( ( CNT.SMKEY ) / 2 )
ELSE (CNT.SMKEY);
AS 'Approx.,Times,Ran'
DTCVT AS 'Last Run'
FED_INFO AS 'Federal,Info'
BY GRP_NME AS 'Group'
BY USER_NAME AS 'User'
BY REPORT_NAME AS 'Report Name'

ON GRP_NME PAGE-BREAK
HEADING
"WebFocus Usage Report"
"Report Date: <+0>&DATEtrMDYY <+0> "
"Server: "Date Range: <+0>&STDATE<+0> - <+0>&ENDDATE<+0> "
" "
FOOTING
""
WHERE ( SMRPCNAME NE ' ' );
WHERE ( DTCVT GE '&STDATE.Start Date-MM/DD/YYYY.' ) AND ( DTCVT LE '&ENDDATE.End Date-MM/DD/YYYY.' );
WHERE GROUP EQ &GROUP.(OR(,,,,,)).Select User Groups.;
WHERE SMRPCNAME IN ('1040_ptc_report',
'account_detail_level_1',
'aggr1040','cycle_time',
'cp_compensation_main',
'cp_hb6002',
'cp_wage_summary',
'cycle_time',
'dlce_year_to_year1',
'fc_analysis_report',
'fcb_corporate_report',
'findings_and_collections_report',
'home',
'irs1040',
'js_sql_tst',
'kw_wage_summary_report',
'loadstat',
'mits_zip_report',
'registra','revenue_report',
'seperate_entity_report',
'topfile','wagesumy',
'waiver_report',
'hjkhd3rb',
'gstlvyop',
'1120_aggregate_report',
'1120_list_box_creation_test',
'cp_vendor_prac_vendor');
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,).Select type of display output.

Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML

quote:

SMRPCNAME IN ('1040_ptc_report',
'account_detail_level_1',
'aggr1040','cycle_time',
'cp_compensation_main',
'cp_hb6002',
'cp_wage_summary',
'cycle_time',
'dlce_year_to_year1',
'fc_analysis_report',
'fcb_corporate_report',
'findings_and_collections_report',
'home',
'irs1040',
'js_sql_tst',
'kw_wage_summary_report',
'loadstat',
'mits_zip_report',
'registra','revenue_report',
'seperate_entity_report',
'topfile','wagesumy',
'waiver_report',
'hjkhd3rb',
'gstlvyop',
'1120_aggregate_report',
'1120_list_box_creation_test',
'cp_vendor_prac_vendor');

I think you need the BY on SMRPCNAME. Are you certain that the above are spelled correctly. Also what type of database are you using and how is the field defined in the database? It may be that you are not pulling the data because of having spaces rather than nulls filling the value. Try pulling just 'loadstat' and see if you get anything. If you do not, try tabling your file and sum SMRPCNAME by SMRPCNAME NOPRINT and see what values are in your file. If loadstat is among them change your where to have enough spaces after the value to fill the field.

As far as the parentheses in the compute you have more than you need. As long as they are balanced I wouldn't worry about them. Since you do not have any OR's you could simplify it to:
SMRPCNAME EQ 'mits_zip_report' AND ( CNT.SMKEY / 3 ) LT 1