Table View Formatting - Displaying Commas in Numeric Columns

[JFlak]

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.

[NidhiMangtani]

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.

[JFlak]

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.

[NidhiMangtani]

This would work for decimal columns:

SELECT CONVERT( numeric(10,1), 5634.6334) as number	5634.6
SELECT CONVERT( numeric(10,2), 5634.6334) as number	5634.63

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

Please let me know if you have additional queries.