How can we show commas as the thousand separator in a numeric Table View column

RandyThompson
New Contributor III

We are trying to format numeric columns in a Table View but haven't been successful. The numeric columns are currently appearing as 999999.99 instead of the desired 99,999.99. Are Table View currently looks like this:

RandyThompson_0-1683290461604.png

The Spreadsheet Table View rule is defining the numeric columns here:

sql.AppendLine( ",FORMAT(SUM(ISNULL([V4],'0')),'N2','en-us') As [Gross Revenue]")

RandyThompson_1-1683290513645.png

Thanks

14 REPLIES 14

RobbSalzmann
Valued Contributor

Could you try formatting the number in your sql query?

SELECT FORMAT(SomeNumericColumn,'#,0.00') from SOME_TABLE

RandyThompson
New Contributor III

I tried that and it didn't work. Table View seems to be ignoring the SQL formatting and applying their formatting based on the table view column definition.

Randy, you will have to get rid of the column format and use the populate from data table option with the keep format from data boolean.

Thanks, Celvin. Unfortunately I can't find any documentation on this. Can you share a snippet of code of how to use the populate from data table. Thanks.

Didn't test this but it should work.

' assuming yourDataTable will have been obtained already
Dim includeColHeaderRow as Boolean = True
Dim useDataTypes as Boolean = True  ' try switching this on/off to compare results
args.TableView.PopulateFromDataTable(dataTable, includeColHeaderRow, useDataTypes)
return args.TableView

sfilho
New Contributor III

Hi Jack,

Where I should create the datatable? Is the datatable a different table view?

Thx

JackLacava
Community Manager
Community Manager

DataTable is an object representing an SQL-like table in memory. It's a standard .Net class. For a simple example, check out this blog post - it's for Dashboard DataSets, but around halfway through it shows a simple example of querying a database to get a DataTable object. You can also check out Microsoft's own documentation or just search this forum, there are plenty of examples. Note that you don't have to perform a database query to get a DataTable, they can be created in memory from scratch.

sfilho
New Contributor III

Hi Jack, I created the data table and was able to populate the table view. When I open the table view in an Excel file which is embedded in a dashboard, the numbers are correctly formatted with the commas. I used format(<number>,'C') in the SQL to populate the data table. 

However, the number are being presented in excel aligned to the left sided instead of to the right side and when I save the excel file from the dashboard in my local drive the numbers are being saved as a text with a single comma before the number. Is that correct behavior? Would be a way to set the numbers alignment to the right and when saving the excel file set the values as numbers instead of text?

Thanks for the help,

JackLacava
Community Manager
Community Manager

Did you set the last parameter (useDataTypes) as True in your PopulateFromDataTable call?

sfilho
New Contributor III

Its current set to True but I tried False as well and the result is the same when showing in Excel.

That's what format does. When you use format, SQL server formats numbers into Text, and when you use that with populatefromdatatable with useDataTypes, EXCELwill now treat that as a TEXT field. How about just using it as it is (with DataTypes) and then format it in EXCEL?

sfilho
New Contributor III

The users want the excel populated and format ready. If there is no other way then I will discuss alternatives with them. Thank you for confirming the behavior.

You could file an enhancement request for the ColumnFormat like AsCurrency (there is already one for AsPercentage).

I have submitted this enhancement on IdeaStream.

Currently when opening the same Table View we can get the formatting we want in a native Excel Table View (right-justified currency fields) but not in the OneStream Spreadsheet tool (left-justified Text field).