How to replace null values by a string in a query?

[semsel]

Hi,

I am using two queries Query 1 and 2 and the union of the two queries query 3 is used to build a crosstab. I have applied a detail filter to query 2 { [Sales Manager] includes null }to return only null values for Sales Manager. Now I want to replace these null values in the crosstab by the string 'Orders without Sales Manager'. When I use the data format (custom) option to replace the null values it doesn't seem to work. So is there any expression to return a string instead of null at the query level itself?

[HalfBloodPrince]

Create query Calculation as

if ([Sales Manager] is null ) then

('Orders without Sales Manager')
else
(cast([Sales Manager],varchar(10)))

[mtirpude]

Hi HalfBloodPrince,

You can not write a string (''Orders without Sales Manager') when your else statement has numeric value.

here the solution is
Create a boolean variable with expression as [Sales Manager] is null. It will have two values "Yes" and "No"

Now, in your crosstab, drag a text item side to "Sales Manager" data item by unlocking the report. Type the text as 'Orders without Sales Manager'. Now click on this text, chose render variable from properties pane, select the boolean variable you just created, "Yes" should be checked for this. Run the report and check the results.

Regards,
Manish | +919673990644
www.infocepts.com | mtirpude@infocepts.com

[HalfBloodPrince]

coalesce ([sale Manager],'Orders without Sales Manager')