# Convert number to text to enable concatenate

**URL:** <https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337>\
**Category:** Ask the Community\
**Created:** [November 5, 2019, 12:21am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337 "2019-11-05T00:21:32Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![99Michelle](https://avatars.discourse-cdn.com/v4/letter/9/87869e/32.png) [@99Michelle](https://pug.phocassoftware.com/u/99Michelle)\
**Post date:** [November 5, 2019, 12:21am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/1 "2019-11-05T00:21:32Z")

</div>

Hi,

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.

---

<div class="post-metadata">

**Author:** ![StuartH](https://sea1.discourse-cdn.com/flex015/user_avatar/pug.phocassoftware.com/stuarth/32/394_2.png) [@StuartH](https://pug.phocassoftware.com/u/StuartH)\
**Post date:** [November 5, 2019, 8:51am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/2 "2019-11-05T08:51:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![99Michelle](https://avatars.discourse-cdn.com/v4/letter/9/87869e/32.png) [@99Michelle](https://pug.phocassoftware.com/u/99Michelle)\
**Post date:** [November 5, 2019, 11:09pm UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/3 "2019-11-05T23:09:24Z")

</div>

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.

Regards,  
Michelle

---

<div class="post-metadata">

**Author:** ![StuartH](https://sea1.discourse-cdn.com/flex015/user_avatar/pug.phocassoftware.com/stuarth/32/394_2.png) [@StuartH](https://pug.phocassoftware.com/u/StuartH)\
**Post date:** [November 6, 2019, 9:29am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/4 "2019-11-06T09:29:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![99Michelle](https://avatars.discourse-cdn.com/v4/letter/9/87869e/32.png) [@99Michelle](https://pug.phocassoftware.com/u/99Michelle)\
**Post date:** [November 7, 2019, 12:59am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/5 "2019-11-07T00:59:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![99Michelle](https://avatars.discourse-cdn.com/v4/letter/9/87869e/32.png) [@99Michelle](https://pug.phocassoftware.com/u/99Michelle)\
**Post date:** [November 7, 2019, 1:24am UTC](https://pug.phocassoftware.com/t/convert-number-to-text-to-enable-concatenate/1337/6 "2019-11-07T01:24:35Z")

</div>

Was able to do this now.

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