Convert number to text to enable concatenate


Need to concatenate our product id with store id but can’t use " + " to do so as they’re in numeric format. Using " + " adds up the value and instead of concatenate.

Need to know what syntax to convert the number to characters so I can use the " + " to concatenate.

Is this to add as a dimension / property for all to use or just for a specific report.

Also I presume from your post that the product id and store id are both fully numeric?

I’d probably suggest this should be done in SQL if it’s to be a permanent fixture in which case it would be CONCAT(stringa,stringb) though I would put some sort of separator in like “-” to keep it readable by the user.

Hi Stuart,

Thanks for the reply.

I would like to it add as a dimension.

And yes, both fields are numeric.

I only have access on the database design not the SQL.

Main objective for this is to really filter our “0” stock in product_id and store_id level.
Which does not seem to be working if I create a filter at product_id with stock “>0” and another at store_id with stock “>0”. The value does not match-up when I downloaded the data without filters, and manually check the total stock “>0”, vs. to what I downloaded with the filters. So I thought I could add a new dimension and filter with this.


So presumably your data stores the stock at store level. Is it just a daily snapshot?

If you’re looking for which items have zero stock at store level I can’t see why this wouldn’t work without needing an extra dimension as essentially if your data is structured properly you already have it as a measure.

My assumption would be you just need to filter Quantity = 0 and maybe put the data in a nested grid with Product ID selected and Store ID put in the filter.

Sorry, typo there.

Should be filter out zero from the list.
This would be easy if my report is only for inventory data which can be done by simply un-ticking “show net zero”.

But since the report I’m working on have inventory data, purchase data and also sales data, I need to have the option to show only stores-product with “>0” inventory.

Was able to do this now.

What I’ve done is using the expression:
CONVERT(varchar(12), [WHID])+’: '+CONVERT(varchar(12), [Supplier_product_id])

1 Like