CONCAT field

[naqsa]

Hi All,

I've created a concatenated field that acts as a memo. It includes report ID, employee name, ID, and vendor name. When one of these data points is missing, the CONCAT field is blank. What can I do to force the CONCAT to EITHER:
1. drop what is missing and populate the rest
2. when one item is missing, populate field with 'memo not available'. 

[Expense].[Report Header Information].[Report ID] || ', ' || [Expense].[Report Header Information].[Employee] || ', ' || [Expense].[Report Header Information].[Employee ID] || ', ' || [Expense].[Entry Information].[Vendor]

Thanks!

[Lynn]

Hi All,

I've created a concatenated field that acts as a memo. It includes report ID, employee name, ID, and vendor name. When one of these data points is missing, the CONCAT field is blank. What can I do to force the CONCAT to EITHER:
1. drop what is missing and populate the rest
2. when one item is missing, populate field with 'memo not available'. 

[Expense].[Report Header Information].[Report ID] || ', ' || [Expense].[Report Header Information].[Employee] || ', ' || [Expense].[Report Header Information].[Employee ID] || ', ' || [Expense].[Entry Information].[Vendor]

Thanks!

Assuming this is a relational source and you are using a list layout container, you could skip the concatenated field entirely and just drag the items separately into the same list column by unlocking the layout (the little padlock icon in the toolbar). You can place a text item between each for the comma separators.

Another option to try is the coalesce function. If the missing values are actually null this would allow you to replace a missing element with some text. This function simply returns the first non-null value.

Code Select Expand


coalesce ( [Expense].[Report Header Information].[Report ID], 'Report ID not available' )


[naqsa]

I've tried this but it leaves the entire memo blank.

REPLACE(Coalesce([Expense].[Report Header Information].[Report ID] , ',') + Coalesce([Expense].[Report Header Information].[Employee], ',') + Coalesce([Expense].[Report Header Information].[Employee ID], '.') + Coalesce([Expense].[Entry Information].[Vendor], '.'), ',,,.', NULL)

[cognostechie]

Hi -

You do not need the Replace function. Coalesce itself will replace the NULL with a non-null value. What Lynn suggested is how to display a text using the coalesce function when the value of the field is null. The code she provided was to be used in one data item only which would display a text when the value of that field is null. The three other data items have to created accordingly.

Data Item 1  - coalesce ( [Expense].[Report Header Information].[Report ID], 'Report ID not available' )
Data Item 2  - coalesce ( [Expense].[Report Header Information].[Employee], 'Employee Name not available' )
Data Item 3  - coalesce ( [Expense].[Report Header Information].[Employee ID], 'Employee ID not available' )
Data Item 4  - coalesce ( [Expense].[Entry Information].[Vendor], 'Vendor Name not available' )

You can add 'Text Item' between these Data Items to insert commas. You will find it in the Toolbox of Report Studio.

So these are the steps:

1> Drag a 'Query Calculation' in the container and insert the expression for Data Item 1
2> Look at the toolbar on the top and click on the icon that looks like a lock
3> Drag a 'Text Item' from the Toolbox and insert a comma
4> Drag another 'Query Calculation' in the same column next to the Text Item created in Step 3 and insert the expression for Data Item  2.
5> Drag a 'Text Item' from the Toolbox and insert a comma
6> Drag another 'Query Calculation' in the same column next to the Text Item created in Step 5 and insert the expression for Data Item  3.
7> Drag a 'Text Item' from the Toolbox and insert a comma
8> Drag another 'Query Calculation' in the same column next to the Text Item created in Step 7 and insert the expression for Data Item  4.
10> Click on the same icon again as in Step 2

That should do it.  If the report still shows only nulls then check the Data format properties of the objects you created with the above steps.