Apply Money Format on EZWriter Data Source

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

Date Votes
  • Kris Corrigan

    I am applying the format as above and receiving the error message

    'Operand data type varchar is invalid for sum operator'

    0 upvotes

  • Ricardo Guevara
    • Edited

    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:

    SELECT '$' + CONVERT(VARCHAR,CONVERT(MONEY, SUM(ER100_ORD_TOT)),0) As MoneyFormat
    FROM ER100_ACCT_ORDER
    WHERE ER100_ORG_CODE = '10' AND ER100_EVT_ID = 5101

    0 upvotes

  • Kris Corrigan

    HI Ricardo

    I am still getting the error message, the full query is below,

    DECLARE @DUMMY bit

    SELECT Department,
     ISNULL(JAN,0) AS JAN,
     ISNULL(FEB,0) AS FEB,
     ISNULL(MAR,0) AS MAR,
     ISNULL(APR,0) AS APR,
     ISNULL(MAY,0) AS MAY,
     ISNULL(JUN,0) AS JUN,
     ISNULL(JUL,0) AS JUL,
     ISNULL(AUG,0) AS AUG,
     ISNULL(SEP,0) AS SEP,
     ISNULL(OCT,0) AS OCT,
     ISNULL(NOV,0) AS NOV,
     ISNULL(DEC,0) AS [DEC]
    FROM
    (
     SELECT
     po100_Dept AS Department,
     LEFT(DATENAME(MONTH,po100_date),3) AS [MONTH],
     sum(cast(PO100_TOT_STD_COST as money)) as Total
     FROM PO100_orders
     LEFT JOIN ev870_acct_master
     ON po100_requestor=ev870_acct_code
     WHERE YEAR(po100_date) = 2016 AND Po100_ORG_CODE='10' and po100_dept in ('140', '141', '143', '144')
     GROUP BY po100_dept, po100_date, PO100_TOT_STD_COST
    ) DATATABLE

    PIVOT
    (
     SUM(TOTAL) FOR MONTH
     IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, [DEC])
    )PIVOTTABLE

    ORDER BY 'Department'

    0 upvotes

  • Sean Tame

    Kris,

    You need to cast it as money after summing it.  EG, cast the total as money.  Change

    sum(cast(PO100_TOT_STD_COST as money)) to
    cast(sum(PO100_TOT_STD_COST) as money))

    0 upvotes

  • Kris Corrigan

    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.