Skip to main content
J
JFlak
Newcomer
July 14, 2022

Table View Formatting - Displaying Commas in Numeric Columns

  • July 14, 2022
  • 2 replies
  • 0 views

Does anyone know if it is possible to display commas within numeric columns in the table view feature?  I have attempted to make this work both through the business rule and using formatting within the spreadsheet tool, but every time the spreadsheet is refreshed, the commas are removed.  Any advice in this area would be greatly appreciated.

2 replies

N
NidhiMangtani
Veteran
July 14, 2022

Hello,

In your select query for GetTableView in spreadsheet business rule, apply format on the numeric column. 

SELECT FORMAT(column1, '#,#') from tablename

GetTableView gets called when spreadsheet is refreshed so the formatting would sustain.

Hope this helps.

    J
    JFlakAuthor
    Newcomer
    July 14, 2022

    Hi Bharti,

       Thanks for your response.  This does display commas in the table view.  Am I correct in assuming that in order for this to work, I have to set the table view column datatype to text.  Is there any way to display table view decimal or integer columns with commas?  I actually have a number of excel formulas that reference the table view columns and they no longer work when the data type of the columns is set to text.

    N
    NidhiMangtani
    Veteran
    July 14, 2022

    This would work for decimal columns:

    SELECT CONVERT( numeric(10,1), 5634.6334) as number5634.6
    SELECT CONVERT( numeric(10,2), 5634.6334) as number5634.63

     

    Source: https://www.mssqltips.com/sqlservertip/7021/sql-format-number/

    Please let me know if you have additional queries.

    Terms & ConditionsAccessibility statement