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:
The Spreadsheet Table View rule is defining the numeric columns here:
sql.AppendLine( ",FORMAT(SUM(ISNULL([V4],'0')),'N2','en-us') As [Gross Revenue]")
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
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.
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,
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).