Removing Trailing Zeros in SSRS Query

Question

In an SSRS report query, I have the following statement:

CASE WHEN PIB.Quantity IS NULL THEN I.ItemNumber ELSE I.ItemNumber + '-' + CAST(PIB.Quantity AS VARCHAR(12)) END  

This is evaluating the existence of item quantity price brackets in the report data. For example, item number ABC123 has quantity price brackets of 1 each, 10 each and 27.5 each. The above expression returns:

ABC123-1.000000
ABC123-10.000000
ABC123-27.500000

I'm trying to figure out a way of removing the trailing zeros from the results so that I wind up with:

ABC123-1
ABC123-10
ABC123-27.5

The purpose is to alert a report user that this item has brackets and what they are. The trailing zeros aren't hurting anything, but might be distracting to users. I've found several methods for doing this under other circumstances, but nothing that seem to address this situation. Any suggestions?

Answer 1

Hi @Jack Kuehne
This should be that the data type of Quantity in the database is decimal(*,6), so the data has trailing zeros when converted to varchar. By searching, I found "SELECT CONVERT(DOUBLE PERCISION,FIELD) FROM TABLE SOURCE" statement to get rid of trailing zeros.
Here is my test:

SELECT Quantity,ItemNumber,  
CASE  
WHEN Quantity IS NULL THEN ItemNumber   
ELSE ItemNumber + '-' + CAST(CONVERT(DOUBLE PRECISION,Quantity) AS VARCHAR(12))   
END as number   
From Zero2  

For more details, you can refer to this link: how-to-remove-trailing-zeros-using-ssrs
Best regards,
Aniya