Concatenated field with missing information

[naqsa]

Hi all,

I've created a concatenated field to pull 4 pieces of information together; employee, employee ID, report ID, and vendor name. There are situations where the 'vendor name' is not available. When this happens, the field returned is blank. How can I change my expression to populate 'missing information' when one of the data points is not available? Here's what's in the expression definition now:

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

Thanks!

   

[Hristina_Bambeva]

Hi if only the Vendor Name is occasionally missing you can create a new data item:

if ([Expense].[Entry Information].[Vendor]) is not missing then ([Expense].[Entry Information].[Vendor]) else (' ')

this should help you display the rest of the string

[clamus17]

Hi naqsa,

You can use case/if(Like what BamHr used) statements and other null handling functions.

Kindly try this:

coalesce([Expense].[Report Header Information].[Report ID],'-- Value you want for missing expression --') || ',' || coalesce([Expense].[Report Header Information].[Employee],'-- Value you want for missing expression --')  || ',' || coalesce([Expense].[Report Header Information].[Employee ID],'-- Value you want for missing expression --') || ',' || coalesce([Expense].[Report Header Information].[Vendor],'-- Value you want for missing expression --')   

OR

case when [Expense].[Report Header Information].[Report ID] is null then '-- Value you want for missing expression --' else [Expense].[Report Header Information].[Report ID] end || ',' || and so on...

OR

if [Expense].[Report Header Information].[Report ID] is null then  '-- Value you want for missing expression --' else [Expense].[Report Header Information].[Report ID] || ',' || and so on...

There are also other functions that you can use depending on what database you are using which are called vendor specific functions. Try to explore and use them. Hope this helps.

Cheers! :)