Conditional Drilldowns with ACROSS
by Ken Lane

Challenge:

Recently, it became a requirement of mine to provide a conditional drilldown on an ACROSS value. For what seems like eternity, it has been a widely accepted idea that one cannot perform conditional drilldowns on ACROSS values. For instance, consider the example using GGSALES. Let us assume that we do not want to allow the user to drilldown on the Midwest region. Using the logic as outlined below, we find that no drilldown capability is provided for the user on the region.

TABLE FILE GGSALES
SUM UNITS
  BY PRODUCT
ACROSS REGION
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, 
  FOCEXEC=test,
  WHEN=REGION NE 'Midwest',  $
ENDSTYLE
END

The output from the above is:

PAGE 1
	Region
	Midwest	Northeast	Southeast	West
Product
Biscotti	86105	145242	119594	70436
Capuccino	.	44785	73264	71168
Coffee Grinder	50393	40977	47083	48081
Coffee Pot	47156	46185	49922	47432
Croissant	139182	137394	156456	197022
Espresso	101154	68127	68030	71675
Latte	231623	222866	209654	213920
Mug	86718	91497	88474	93881
Scone	116127	70732	73779	72776
Thermos	46587	48870	48976	45648

As you can plainly see, there is no drilldown capability provided for either of the regions.

Taking a page from other methods that you can use such as turning on a switch to identify a yes or no condition, I tried the following method:

DEFINE FILE GGSALES
  REGION1/A1 = IF REGION EQ 'Midwest'  THEN 'N' ELSE 'Y';
END

TABLE FILE GGSALES
SUM UNITS
  BY PRODUCT
ACROSS REGION
ACROSS REGION1  NOPRINT
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, 
  FOCEXEC=test, 
  WHEN=REGION1 EQ 'Y', $
ENDSTYLE
END
				

As in the first example, the user is once again left with no drilldown capability on region at all. 

Of course, there is always the tried and true method of using individual columns and then providing drilldown on the column title as shown in the following:

DEFINE FILE GGSALES
  MIDWEST/I8   = IF REGION EQ 'Midwest'   THEN UNITS ELSE 0;
  NORTHEAST/I8 = IF REGION EQ 'Northeast' THEN UNITS ELSE 0;
  SOUTHEAST/I8 = IF REGION EQ 'Southeast' THEN UNITS ELSE 0;
  WEST/I8      = IF REGION EQ 'West'      THEN UNITS ELSE 0;
  END

TABLE FILE GGSALES
SUM MIDWEST  AS 'Midwest'
   NORTHEAST AS 'Northeast'
   SOUTHEAST AS 'Southeast'
   WEST      AS 'West'
 BY PRODUCT
ON TABLE SET STYLE *
TYPE=TITLE,  COLUMN=NORTHEAST, FOCEXEC=test, $
TYPE=TITLE,  COLUMN=SOUTHEAST, FOCEXEC=test, $
TYPE=TITLE,  COLUMN=WEST,      FOCEXEC=test, $
ENDSTYLE
  END

Using this method, we receive the following output:

PAGE 1
Product	Midwest	Northeast	Southeast	West
Biscotti	86105	145242	119594	70436
Capuccino	0	44785	73264	71168
Coffee Grinder	50393	40977	47083	48081
Coffee Pot	47156	46185	49922	47432
Croissant	139182	137394	156456	197022
Espresso	101154	68127	68030	71675
Latte	231623	222866	209654	213920
Mug	86718	91497	88474	93881
Scone	116127	70732	73779	72776
Thermos	46587	48870	48976	45648

While this does give us the desired result, it is NOT accomplished using the ACROSS. The problem with this method is that, as a developer, we need to have prior knowledge of all of the values within the REGION column to write such as query.

So, what can you do?

Solution:

There is a simple method one can use to provide conditional drilldowns on an ACROSS column. To do so, one can anchor the across values on which you do not want the user to have drilldown capability. Staying consistent with our original assumption, we do not want the user to have drilldown capability on the Midwest.  This is our known requirement. This is not the same as having to know all of the values of a column. To accomplish this, the following will work:

DEFINE FILE GGSALES
 REGION1/A18 = IF REGION EQ 'Midwest'  THEN '<A>'
                  REGION || '</A>' ELSE REGION;
END

TABLE FILE GGSALES
SUM UNITS
  BY PRODUCT
ACROSS REGION1
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, 
  FOCEXEC=test, $
ENDSTYLE
END

Using this method, we receive the following output:

PAGE 1
	REGION1
	Midwest	Northeast	Southeast	West
Product
Biscotti	86105	145242	119594	70436
Capuccino	.	44785	73264	71168
Coffee Grinder	50393	40977	47083	48081
Coffee Pot	47156	46185	49922	47432
Croissant	139182	137394	156456	197022
Espresso	101154	68127	68030	71675
Latte	231623	222866	209654	213920
Mug	86718	91497	88474	93881
Scone	116127	70732	73779	72776
Thermos	46587	48870	48976	45648

In this output, the Midwest region cannot be used in a drilldown which is our desired result as per our requirements.

Taking this a step further, what happens if we try this method for more than one ACROSS value? Let us now assume that the user is not to have drilldown capability on the Midwest nor the Southeast. One could reasonably expect that you could just insert the Southeast into the IF statement within the DEFINE and it would work.

DEFINE FILE GGSALES
  REGION1/A18 = IF REGION EQ 'Midwest' OR 'Southeast'
                THEN '<A>' || REGION ||  '</A>' ELSE REGION;
  END

TABLE FILE GGSALES
SUM UNITS
 BY PRODUCT
ACROSS REGION1
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, 
  FOCEXEC=test, $
ENDSTYLE
END

If you do this, you will receive the following output:

PAGE 1
	REGION1
	Midwest	Southeast	Northeast	West
Product
Biscotti	86105	119594	145242	70436
Capuccino	.	73264	44785	71168
Coffee Grinder	50393	47083	40977	48081
Coffee Pot	47156	49922	46185	47432
Croissant	139182	156456	137394	197022
Espresso	101154	68030	68127	71675
Latte	231623	209654	222866	213920
Mug	86718	88474	91497	93881
Scone	116127	73779	70732	72776
Thermos	46587	48976	48870	45648

In this example, it does achieve the desired result of restricting drilldown capability on the Midwest and the Southeast, but it has changed the sort order for the report. There is a simple way that we can get around this problem. It is by including the original column, REGION, as an across column first in the ACROSS statements. This will ensure that the original sort order of the data remains intact. The logic to accomplish this is:

DEFINE FILE GGSALES
  REGION1/A18 = IF REGION EQ 'Midwest'  OR 'Southeast'
                THEN '<A>' || REGION ||  '</A>' ELSE REGION;
  END

TABLE FILE GGSALES
SUM UNITS
  BY PRODUCT
ACROSS REGION NOPRINT
ACROSS REGION1
ON TABLE SET STYLE *
TYPE=ACROSSVALUE, 
  FOCEXEC=test, $
ENDSTYLE
END

The output from the above query is :

PAGE 1
	REGION1
	Midwest	Northeast	Southeast	West
Product
Biscotti	86105	145242	119594	70436
Capuccino	.	44785	73264	71168
Coffee Grinder	50393	40977	47083	48081
Coffee Pot	47156	46185	49922	47432
Croissant	139182	137394	156456	197022
Espresso	101154	68127	68030	71675
Latte	231623	222866	209654	213920
Mug	86718	91497	88474	93881
Scone	116127	70732	73779	72776
Thermos	46587	48870	48976	45648

In this example, the original sort order of the data is maintained.

This method appears to work for HTML output only.

If any questions, please contact Ken at kenlane@shaw.ca.