Our users would like for a field to show only if the policy number in greater than zero due to it would indicate it's ot a converted policy. If the policy number is greater than zero than it's a converted policy. Can a report be done to show a field off and on? And if so how would I go about coding it?

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

Hi Latigresa,

You can do a WHERE on the table and only extract Policy number greater than zero.

If there is a calculation, you may need to do a DEFINE and then a WHERE on the DEFINE field. (Note this may not be efficient depending on table size and calculation).

Best Regards,

Jimmy Pang

SET NODATA = '';

DEFINE FILE CAR
XFLAG/A1 = IF RETAIL LT 5000 THEN 'Y' ELSE ' ';
END
-*
TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
XFLAG
BY COUNTRY
END
-RUN

I know the where clause but they want the field itself to only show when a Policy has been converted. For instance say on 2/15/2010 the policy on the report was not converted then they don't want the field to appear at all on the report but then say 2/16/2010 the policy on the report was converted then they do want the field to show. At the moment I have a define to blank out the policy number if it equals a zero else show the policy number but thats not what they want because the field itself will still show up if the policy is blanked out. :-( Hope I'm making sense here.

SET NODATA = ' '

DEFINE FILE CAR
SHOW_RTL/D7 MISSING ON = IF RETAIL LT 5000 THEN RETAIL ELSE MISSING;
END

TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
SHOW_RTL
BY COUNTRY
END

Can you set up a conditional style to turn the text white to match the background if the condition is met?

Correction:

SHOW_RTL AS 'RETAIL'

Ginny unfortunately knowing our picky users they will not want the blank space between one field to the next. :-(

Tomsweb I tried you code but the field still shows up. :-( or should I say the heading on the field.

Change POLICY_NUMB to 1111111 for the column to show...

  
SET NODATA = ' '

DEFINE FILE CAR
POLICY_NUMB/I7 = 0000000;
END
TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
POLICY_NUMB
BY COUNTRY
  ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM
   MAX.POLICY_NUMB
 ON TABLE SAVE
END
-RUN
-READ SAVE, &POLICY
-SET &POLICY_COL = IF &POLICY GT 0 THEN 'POLICY_NUMB AS ' | '''' | 'Policy,Number' | ''''  ELSE ' ';
TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
&POLICY_COL
BY COUNTRY
END
-RUN
-EXIT

hth

What is the output type: HTML, Excel, PDF?

Here's a compound example:

  
SET NODATA = ' '

DEFINE FILE CAR
POLICY_NUMB/I7 = IF COUNTRY IN ('ENGLAND','JAPAN') THEN 1111111 ELSE 0000000;
END
TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
POLICY_NUMB
BY COUNTRY
  ON TABLE HOLD
END
-RUN

TABLE FILE HOLD
SUM
   MAX.POLICY_NUMB
 BY COUNTRY
  ON TABLE SAVE
END
-RUN

-SET &XLINES = &LINES;
-SET &CNTR   = 0;
-REPEAT DO_REPORT &XLINES TIMES
-SET &CNTR  = &CNTR + 1;
-SET &OPEN_CLOSE = IF &CNTR EQ 1       THEN 'OPEN'
-             ELSE IF &CNTR EQ &XLINES THEN 'CLOSE' ELSE ' ';
-READ SAVE NOCLOSE &COUNTRY.A10. &POLICY.I7.
-SET &POLICY_COL = IF &POLICY GT 0 THEN 'POLICY_NUMB AS ' | '''' | 'Policy,Number' | ''''  ELSE ' ';
TABLE FILE CAR
PRINT
CAR
MODEL
RETAIL
&POLICY_COL
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY';
  ON TABLE PCHOLD FORMAT EXL2K &OPEN_CLOSE
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
     TITLETEXT='&COUNTRY',
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
ENDSTYLE
END
-RUN
-DO_REPORT
-EXIT

Dan this is in HTML format and is being run through reportcaster and ging into Reportcaster library.

Tom I was able to get your first code to work for me but I have one more question for you. On some days there will be no policies which will give the report an error message due to not data to supply the &POLICY pramatar how do I go about not getting an error when there are no policies at all? Or is that there your 2nd code comes in?

It sounds to me that you either want to code like this...

IF &POLICY EQ '' THEN ' '
ELSE IF ...
(whatever you have if &POLICY does EQ something).

Hi Latigresa,

Well, my 1st example showed all policies equal to zero!

Put your code, between the RED code tags(upper right on toolbar). Leave out the style stuff.

We'll see what we can do with that.

Here's a different approach using the StyleSheet, an external style declaration, and HTMLFORM to hide the column when the policy number is zero.

DEFINE FILE CAR
 POLICY_NO/I6 = IF COUNTRY EQ 'ITALY' OR 'FRANCE' THEN 0 ELSE (BYTVAL(COUNTRY,'I3') * 10000 + BYTVAL(CAR,'I3') * 100 + BYTVAL(MODEL,'I3'));
END
-*
TABLE FILE CAR
 SUM MAX.POLICY_NO
 BY COUNTRY PAGE-BREAK
 BY CAR
 BY MODEL
 ON TABLE SET HTMLCSS ON
 ON TABLE HOLD FORMAT HTMTABLE
 ON TABLE SET STYLE *
 TYPE=REPORT, COLUMN=MAX.POLICY_NO, CLASS=nodisplay, WHEN=MAX.POLICY_NO EQ 0, $
 ENDSTYLE
END
-*
-RUN
-HTMLFORM BEGIN
<style type="text/css">
.nodisplay {display: none;}
</style>
!IBI.FIL.HOLD;
-HTMLFORM END

Here's the code I currently have...

 
SET NODATA = ' '
DEFINE FILE PCHGPDANB1
TOPS_POL_NBR/A7=EDIT(CONV_POL_NUM, '$999999');
TOPS_NBR/A7=IF TOPS_POL_NBR EQ '000000' THEN '0' ELSE TOPS_POL_NBR;
END
TABLE FILE PCHGPDANB1
PRINT
     TOPS_NBR
WHERE ACTIVITY_DESCR EQ 'Added Payroll Deduct';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD
ON TABLE SET HTMLCSS ON
END
-RUN
TABLE FILE HOLD
SUM
   MAX.TOPS_NBR
 ON TABLE SAVE
END
-RUN
-READ SAVE, &POLICY
-SET &POLICY_COL = IF &POLICY GE 000001 THEN 'TOPS_NBR AS ' | '''' | 'Policy,Number' | ''''  ELSE ' ';
TABLE FILE PCHGPDANB1
PRINT
     MEM_NBR AS 'Member #'
     POLICY_NBR AS 'Policy #'
     TOPS_NBR AS 'Legacy Policy #'
     &POLICY_COL
	 BILL_ACCOUNT_NBR AS 'Bill Acct #'
     BILL_ACC_POL_STA AS 'Bill Acct,Status'
     ACTIVITY_DESCR AS 'Activity Description'
     NEXT_PMT_DUE_DT AS 'Premium Due,Date'
     TERM_EXP_DATE AS 'Term Exp ,Date'
     PAY_OFF_AMT AS 'Pay Off ,Amt Due'
     PAYROLL_NUMBER AS 'Payroll Num'
     RUN_DATE AS 'Activity Date'
     REFUND_AMT AS 'Refund Amt'
BY BILL_ACCOUNT_NBR NOPRINT
BY POL_TYP_CD AS 'Policy Type'
BY Name_first_last AS 'Insured Name'
ON TABLE SUBHEAD
"Payroll Deduction Added"
" Run Date: <+0>&DATE_EXTRACT<+0> "
" "
WHERE ACTIVITY_DESCR EQ 'Added Payroll Deduct';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END
-EXIT
 

Dan,

quote:

Tomsweb I tried you code but the field still shows up. :-( or should I say the heading on the field.

Latigresa,

OK, that looks good, although it's an Alpha format. What is happening that is not working?

Dan,

It works great when there is a policy on the report the heading shows up only when needed but on days the report is empty it gives an error.
0 ERROR AT OR NEAR LINE 131 IN PROCEDURE paychgpdanb_rclib1
(FOC295) A VALUE IS MISSING FOR: &POLICY

This is due to no data for the &POLICY.

quote:

..I tried you code but the field still shows up. :-( or should I say the heading on the field.

Tom,

The column appears only because some records do have policy numbers. If you add a WHERE clause to select only those records with POLICY_NO = 0, you will see that there is no evidence of the column - not even the column title.

Dan,

And, How is that run via Reportcaster?

Try with this and hope it works,

TABLE FILE HOLD
SUM
MAX.TOPS_NBR
ON TABLE SET EMPTYREPORT ANSI
ON TABLE SAVE
END

Latigresa: I think your last post was meant for Tom. There is no &POLICY variable in my code.

Tom: Right! I forgot about the ReportCaster requirement.

Hi Latigresa,

I'm probably making a mountain out of a molehill, but, here goes. I broke the reporting up by 0 policies and policies GT 0:

  
-DEFAULT &POLICY_COL = ''
DEFINE FILE PCHGPDANB1
MAIN_SORT/I1 = IF EDIT(CONV_POL_NUM, '$999999') EQ '000000' THEN 1 ELSE 2;
END

TABLE FILE PCHGPDANB1
COUNT ENTRIES
  BY MAIN_SORT
WHERE ACTIVITY_DESCR EQ 'Added Payroll Deduct';
 ON TABLE SAVE
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR   = 1;
-*********************************************************
-IF &XLINES GT 1 GOTO DO_RPT_LOOP;
-READ SAVE &SORT_KEY.I1.
-SET &POLICY_COL = IF &SORT_KEY EQ 2 THEN 'TOPS_NBR AS ' | '''' | 'Policy,Number' | ''''  ELSE ' ';
-SET &CNTR   = &SORT_KEY;
-GOTO SKIP_LOOP
-*********************************************************
-DO_RPT_LOOP
-REPEAT DO_REPORT 2 TIMES
-SET &POLICY_COL = IF &CNTR GT 1 THEN 'TOPS_NBR AS ' | '''' | 'Policy,Number' | ''''  ELSE ' ';
-*********************************************************
-SKIP_LOOP

TABLE FILE PCHGPDANB1
PRINT
     MEM_NBR AS 'Member #'
     POLICY_NBR AS 'Policy #'
     TOPS_NBR AS 'Legacy Policy #'
     &POLICY_COL
	 BILL_ACCOUNT_NBR AS 'Bill Acct #'
     BILL_ACC_POL_STA AS 'Bill Acct,Status'
     ACTIVITY_DESCR AS 'Activity Description'
     NEXT_PMT_DUE_DT AS 'Premium Due,Date'
     TERM_EXP_DATE AS 'Term Exp ,Date'
     PAY_OFF_AMT AS 'Pay Off ,Amt Due'
     PAYROLL_NUMBER AS 'Payroll Num'
     RUN_DATE AS 'Activity Date'
     REFUND_AMT AS 'Refund Amt'
	 MAIN_SORT NOPRINT
BY BILL_ACCOUNT_NBR NOPRINT
BY POL_TYP_CD AS 'Policy Type'
BY Name_first_last AS 'Insured Name'

WHERE MAIN_SORT EQ &CNTR;

ON TABLE SUBHEAD
"Payroll Deduction Added"
" Run Date: <+0>&DATE_EXTRACT<+0> "
" "
WHERE ACTIVITY_DESCR EQ 'Added Payroll Deduct';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END
-RUN
-IF &XLINES EQ 1 GOTO EOJ;
-IF &CNTR   GT 2 GOTO EOJ;
-SET &CNTR = &CNTR + 1;
-DO_REPORT
-EOJ
-EXIT

hth

Dan, Sorry doing so many things that I got confused as to who I was trying to reply to.

Atturhari, Is there any way to change the period "ON TABLE SET EMPTYREPORT ANSI" creates into a zero?

Tom, When I use the new code it promting me for the &POLICY.

Change the -DEFAULT to -SET

Also, change/Add this for ZERO records, if this is a requirement:

  
-SET &XLINES = &LINES;
-IF &XLINES EQ 0 GOTO EOJ;
-SET &CNTR   = 1;

quote:

Atturhari, Is there any way to change the period "ON TABLE SET EMPTYREPORT ANSI" creates into a zero?

You can achieve that by an extra variable. Not sure if this is what you really wanted.

TABLE FILE HOLD
SUM
MAX.TOPS_NBR
ON TABLE SET EMPTYREPORT ANSI
ON TABLE SAVE
END
-RUN
-READ SAVE, &POLICY_1
-SET &POLICY = IF &POLICY_1 EQ ' ' THEN 00000 ELSE &POLICY;

I have been pulled to work on another report. I will get back to this one as soon as possible and give an update.

THANK YOU ALL FOR ALL YOUR HELP SO FAR!!!
Have a great Weekend!!

I should be back on this by this afternoon. If anyone has other thoughts please let me know. Thank you to all that have been helping. Hope everyone had a great weekend!