Reports may require applying a standard set of selection filters to a set of data that can not easily be grouped using regular selections filters. This often results in very complicated selection filters.

  Suppose that we are interested in reviewing a report for various models of equipment. For those models on certain program types, we want to see service calls in a particular date range. To complicate the matter, there are twenty different models, and 15 different program types.

The simplest selection criteria is

IF	MODEL	EQ	"M1"
AND	PROGRAM	EQ	"P1"
AND	DATE	GE	START.DATE
AND	DATE	LE	END.DATE

This gets repeated for each possible combination of model & program type. In our example, there are 300 combinations, and therefore 1,200 selection criteria. It will not work.

By using the IN operator, we can pack more logic into each test.

IF	MODEL	IN	"M1,M2,M3,M4,M5"
AND	PROGRAM	IN	"P1,P2,P3,P4,P5,P6,P7"
AND	DATE	GE	START.DATE
AND	DATE	LE	END.DATE

Factoring in the lengths of each field, we have 4 model sets, and 3 program type sets, for a total of 12 sets, or 48 selection criteria. Better, and do-able, but not great.

Boolean filter example

The best approach is to use a defined variable as a boolean flag.

DEFINE
MODEL.OK C 2 0
"1" + "0"

IF MODEL IN "M1,M2,M3,M4,M5"
IF MODEL IN "M6,M7,M8,M9,M10"
IF MODEL IN "M11,M12,M13,M14,M15"
IF MODEL IN "M16,M17,M18,M19,M20"
/*

DEFINE
PGM.OK C 2 0
"1" + "0"

IF PROGRAM IN "P1,P2,P3,P4,P5,P6,P7"
IF PROGRAM IN "P8,P9,P10,P11,P12,P13,P14"
IF PROGRAM IN "P15"
/*

SELECT
IF MODEL.OK EQ "1"
AND PGM.OK EQ "1"
AND DATE GE START.DATE
AND DATE LE END.DATE
/*

Now we have 11 selection criteria, with less typing, less chance of error, and a faster more easily maintained report script. If a model needs to be added or removed, changes happen in only one place. This takes advantage of Data Writer's default operation which sets the define variables to 0 if the condition fails. Only if the model is in one of the lists will the value = 1

NOTE THAT WE LIMITED THE SIZE of the constants to the maximum of 28 characters currently available within the interactive report program.

If you are using vi, or edit, those constants can be extended to 64 characters each.

This brings us down to 7 selection criteria.

DEFINE
MODEL.OK C 2 0
"1" + "0"

IF MODEL IN "M1,M2,M3,M4,M5,M6,M7,M8,M9,M10"
IF MODEL IN "M11,M12,M13,M14,M15,M16,M17,M18,M19,M20"
/*

DEFINE
PGM.OK C 2 0
"1" + "0"

IF PROGRAM IN "P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15"
/*

SELECT
IF MODEL.OK EQ "1"
AND PGM.OK EQ "1"
AND DATE GE START.DATE
AND DATE LE END.DATE
/*

This same technique can be used to simplify report selection criteria in almost any report that has twenty or more selection criteria. If you are having difficulty managing the selection complexity, this provides a good way to organize the process and improve the clarity. As an aside, this same technique is equally viable for any reporting or analysis, including SQL.