The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

RandyThompson's avatar
RandyThompson
New Contributor III
3 years ago

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

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 currentl...
Reports
RandyThompson's avatar
RandyThompson
New Contributor III
3 years ago

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.

JackLacava's avatar
JackLacava
Icon for OneStream Employee rankOneStream Employee
3 years ago

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's avatar
    sfilho
    New Contributor III
    3 years ago

    Hi Jack,

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

    Thx

    • JackLacava's avatar
      JackLacava
      Icon for OneStream Employee rankOneStream Employee
      3 years ago

      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 https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/creating-a-datatable 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's avatar
        sfilho
        New Contributor III
        3 years ago

        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,