[SSRS] Use Enum values in filter expressions

When designing SSRS Reports with Data from Dynamcis AX, most of the datasets contains enum fields. But first let me give you a little background on enum fields.

How to treat enum fields

Those fields must be treated in a special way because each enum field appears as it is splitted into two fields in the dataset on the report:

SSRS Enum field in dataset

A closer look at the properties of those fields will reveal the difference:
ssrs enum field name ssrs enum field label

The difference is found in the value of the property alias:

Alias: LABEL
Value

This field contains the translated enum value into the user language.

Purpose

Used to print out the value in the design.

Alias: NAME
Value

This field contains the untranslated enum value.

Purpose

Used for expressions and conditions in design.

Use in conditional expresions

Using enum fields in conditional expressions in simply:

  • Always use the field with the NAME alias for statements.
  • Quote the enum value to be checked on (ex.: "Yes" for a NoYes-Enum)

Example of a Hidden-expression:

=iif(Fields!PurchOrderTransOrTaxTrans.Value = "Yes", True, False)

ssrs conditional expression

Use enum fields in filter expressions

When using enum values in filter expressions of a tablix or grouping, following rules apply:

  • Always use the field with the NAME alias for statements.
  • Don't Quote the enum value to be checked on (ex.: Yes for a NoYes-Enum)

Example of filter expression of a grouping:
ssrs filter expression on grouping

Note that the value is not quoted on the filter expression!

Robin

Software Engineer / Application Developer

Subscribe to robsCode

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!