In EZWriter, whenever using data fields that require a specific format; we have to provide the field formatted the way is needed starting from the Data Source. Data Formats will allow us to apply simple text formatting to our field, but not format the data in any way, for example, specific date format, numbers or currency.
Taking the number “12345.6” as an example of a value coming from database, we can use the following statement to return a number formatted as currency:
SELECT '$' + CONVERT(VARCHAR,CONVERT(MONEY, 12345.6),0) As MoneyFormat
-- No commas on thousands, 2 decimals: $12345.60
SELECT '$' + CONVERT(VARCHAR,CONVERT(MONEY, 12345.6),1) As MoneyFormat
-- Commas every three digits, 2 decimals: $12,345.60
SELECT '$' + CONVERT(VARCHAR,CONVERT(MONEY, 12345.6),2) As MoneyFormat
-- Commas every three digits, 4 decimals: $12,345.6000
For more information about the SQL Function CONVERT, visit the following page:
http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
Comments
5 comments
I am applying the format as above and receiving the error message
'Operand data type varchar is invalid for sum operator'
0 upvotes
Hello Kris, thank you for your imput.
I don't have the full SQL statement you are building but based on the message seems like you may be applying the money formatting inside the aggregate SUM function of a GROUP BY. This exception will come up because SQL Server cannot sum text, only numeric values.
If this is the case, try applying the money formatting last, where you would have the SUM of the database field in the inner part of the statement, like so:
0 upvotes
HI Ricardo
I am still getting the error message, the full query is below,
0 upvotes
Kris,
You need to cast it as money after summing it. EG, cast the total as money. Change
0 upvotes
Thanks Sean
I don't seem to getting what I need
It would be if the EZ writer templates had a formatting value
0 upvotes
Please sign in to leave a comment.