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 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

Reports

14 Replies

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    3 years ago

    Could you try formatting the number in your sql query?

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

  • RandyThompson's avatar
    RandyThompson
    New Contributor III
    3 years ago

    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.

  • ckattookaran's avatar
    ckattookaran
    Valued Contributor
    3 years ago

    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.

    • 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