05-05-2023 10:32 AM
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]")
Thanks
05-05-2023 11:19 AM
Could you try formatting the number in your sql query?
SELECT FORMAT(SomeNumericColumn,'#,0.00') from SOME_TABLE
05-05-2023 12:01 PM
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.
05-05-2023 05:23 PM
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.
05-05-2023 06:38 PM
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.
05-09-2023 04:16 AM
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
05-30-2023 11:06 AM
Hi Jack,
Where I should create the datatable? Is the datatable a different table view?
Thx
05-30-2023 12:02 PM - edited 05-30-2023 12:05 PM
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.
06-01-2023 09:14 AM
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,
06-01-2023 12:10 PM
Did you set the last parameter (useDataTypes) as True in your PopulateFromDataTable call?
06-02-2023 09:36 AM
Its current set to True but I tried False as well and the result is the same when showing in Excel.
06-01-2023 05:58 PM
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?
06-02-2023 09:38 AM
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.
06-02-2023 11:18 PM
You could file an enhancement request for the ColumnFormat like AsCurrency (there is already one for AsPercentage).
06-20-2023 08:57 AM
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).