This wiki article explains how to create report with field which picks up values from different attributes.

Steps to Accomplish

You have different status attributes on different document types, and you want to design a report that will show those different status attributes in a single column.

For example, say you have ‘Test Attrib Drawing’ attribute on a APP class, ‘Test Attrib Calculation’ on a ASB class, and ‘Test Attrib Delivery’ on a BILL class.

In designing a report that includes documents from all these classes, your first inclination might be to just include all those status values in your eQL select statement, like below:

START WITH Document
SELECT
    Code,
   Name,
   Attributes["Global", "Drawing Status"].Value,
   Attributes["Global", "Calculation Status"].Value,
   Attributes["Global", "Delivery Status"].Value,
   Class.Name
WHERE    Name LIKE  @Name 

This gets the job done, but each status value shows in a separate column, and all but one column will be empty for any one document in the report (see attached screenshot)

A way to collapse all those values into a single column is to use the CASE statement in eQL. The CASE statement acts like a set of if/then conditions. The CASE statement looks like this:

START WITH Document
SELECT
    Code,
    Name,
    CASE Class.Code
    WHEN 'APP' THEN Attributes["Global", "Drawing Status"].Value
    WHEN 'ASB' THEN Attributes["Global", "Calculation Status"].Value
    WHEN 'BILL' THEN Attributes["Global", "Delivery Status"].Value
    ELSE ApprovalStatus
   END,
    Class.Name
WHERE
    Name LIKE @Name

Note that each WHEN clause does not end with a comma. The comma comes after the END keyword.

The CASE ... END acts like a single column and displays all the different statuses in that one column according to which WHEN clause the document satisfies. The ELSE clause will handle all those documents that don't have one of those statuses, and it's optional.

In the Format Layout portion of the Report Designer, you should choose a label for the column that reflects the content of the column, like "Status".
Obviously, you don't have to restrict yourself to status columns. You can use this technique whenever you want to add conditional logic into the SELECT or WHERE clause.

See Also