This wiki article explains how to create report with field which picks up values from different attributes.
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 DocumentSELECT 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 DocumentSELECT 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.NameWHERE 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.