In SAS Web Report Studio, you might see the following error message in the data dialog box for a crosstabulation table that is based on relational data:

[Measure...] cannot be included in a crosstab because the aggregation type(s) are not supported

The message might refer to more than one measure. In addition, the measures named in the message do not appear in the dialog box.

Depending on which release of SAS Web Report Studio that you are using, here are the reasons why you might not be able to use a specific measure in a crosstabulation table:

SAS Web Report Studio 4.2 and later

Note: Beginning with version 4.2_M2, SAS Web Report Studio supports distinct aggregation types such as COUNT(Distinct), AVG(Distinct), and SUM(Distinct).

• The measure is using an unsupported aggregation type. Crosstabulation tables that use relational data support only the following aggregation types for measures: AVG, COUNT, CSS, CV, FREQ, MAX, MEAN, MIN, N, NMISS, PRT, RANGE, STD, STDERR, SUM, USS, T, VAR, and the two Defined in expression types.

  Note: This rule is also enforced if you create a new data item that refers to a measure that is using an unsupported aggregation type such as any of the distinct aggregation types.

• The measure expression evaluates to a constant.

• The measure expression does not contain an aggregate function. Expressions that do not contain an aggregate function use one of the Defined in expression aggregation types and they reference physical data items instead of business data items.

• The measure is using one of the Defined in expression aggregation types, and the expression for the measure contains what looks like a hard-coded aggregate function. For example, you would not be able to use a measure with the expression <<root.NoDays >> * <<root.CallTime Avg (internal)>> because CallTime Avg (internal) is interpreted as being a reference to the AVG() aggregation type.

SAS Web Report Studio 3.1

• The measure is using an unsupported aggregation type. Crosstabulation tables that use relational data support only the following aggregation types for measures: AVG, COUNT, FREQ, MAX, MEAN, MIN, N, NMISS, SUM, and the two Defined in expression types.

  Note: This rule is also enforced if you create a new data item that refers to a measure that is using an unsupported aggregation type such as any of the distinct aggregation types.

• The measure expression evaluates to a constant.

• The measure expression does not contain an aggregate function. Expressions that do not contain an aggregate function use one of the Defined in expression aggregation types and they reference physical data items instead of business data items. (This type of measure does not generate an error until you attempt to display it in a crosstabulation table, or, in some cases, when you modify the section query in a synchronized report. For more information, see SAS Note 40629.)

• The measure is using one of the Defined in expression aggregation types, and the expression for the measure contains what looks like a hard-coded aggregate function. For example, you would not be able to use a measure with the expression <<root.NoDays >> * <<root.CallTime Avg (internal)>> because CallTime Avg (internal) is interpreted as being a reference to the AVG() aggregation type.

Possible Alternatives

• In some cases, if you are using SAS Web Report Studio 3.1, changing the aggregation type to Defined in expression (additive) will enable you to include a distinct calculation in a crosstabulation table.

• Depending on your requirements, inserting a Text Object that contains the non-additive measure beneath the table might meet your needs.

For related information, also see SAS Note 35430.

Operating System and Release Information