Convert nvarchar to data type numeric error

I’m trying to create a calculated measure that takes a Sales Quantity * 1.05. The measure itself is now built. But when I try to create the transformed column to do the calculation [Quantity] *1.05, I get an error message stating
image

Has anyone experienced something similar?

Hi @wpratt ,

Looks like the Quantity column is being passed to Phocas as text. Been around the block a few times so I suspect that the Quantity column has values that are not considered numeric.

How do I fix in SQL:
You need to check that the column is numeric. This can be done by doing the following:
NOTE: There are other functions such as TRY_CAST,TRY_PARSE, but the below is a way that supports all versions of SQL

Select 
    CAST(Quantity AS Numeric(18,2)) AS Quantity
From MyTable

If this fails then you know that the Quantity field has non numeric inside. To fix in SQL:

Select
      Case Isnumeric(Quantity)
                   WHEN 1 THEN CAST(Quantity as Numeric(18,2))
                   Else 0
      END
From MyTable

The above inspects the Quantity column, if its a number it will convert it, if its text it replaces with a zero. This is the recommended approach, fix at source not for every report you have.

If its Excel:
Select the column (lets say its column D), then set the column format to numeric, save the file and re-upload. This should instruct Phocas that its not a text field and ignore non numeric values.

I only have designer in Phocas how am I supposed to do it?
Not a problem, remember that Phocas sits on top of SQL server, so create a new expression in the designer Sync file location called “QuanityFix” then do:

  Case Isnumeric([Quantity])
                   WHEN 1 THEN CAST([Quantity] as Numeric(18,2))
                   Else 0
      END

Click apply and you should see a new column in the feed, use this to map to the Quantity measure instead.

I don’t have designer only custom mode:
You can also do it here, but it starts getting messy, as you will have to do it for every report and using custom is far more restrictive, always try to fix at source:


The syntax is:
^CASE ISNUMERIC({a}) WHEN 1 THEN Cast({a} as numeric(18,2) )else 0 END
This works, but the syntax uses “^” and “{}”. For more info on using the “^” I published an article a couple of years ago here: Percentage share in Custom Mode

Good Luck!

Regards,
Jon

1 Like