Many times when building a group, some of the columns will be
required to display a value from a pick list. This is easily
accomplished by setting the “Format Type” property of the column to the
value “Picklist Item”. However, if the group is to be exported to a
file, e. g. Excel, you will notice that the columns displaying values
from a Pick List contain the internal code for the item instead of the
value in the exported file. This is because the data retrieved for
exporting to a file does not have the formatting applied to columns as
the Group Manager would normally do.
The solution to this problem is to simulate what the “Picklist Item” formatting does. This should be straight forward for an Admin user but may be confusing to an end user trying to build a group for exporting on their own. Simply click on “create a local join” in the Query Builder. Then, select the picklist table as the child for the columns that have the “Picklist Item” format type in the Query Builder. The trick here is to know that your picklist column will always map to the “ItemID” column in the PickList table in the Join Editor. Make sure that you select “Left” as the join type or you may filter out rows that do not have a pick list value.
Click OK, and the Picklist table will now be displayed as a child of the table you created a local join to. To get the value form Picklist table, click on the child picklist table you just added, the select “Text” from the list, as show in the screen below, and add it to your group, changing the “Caption” property as appropriate.
Your exported file will now contain the value from the picklist instead of the internal code.
Repeat this procedure for each column formatted as a “Picklist Item”.
Posted in: