Forum Discussion

aprats's avatar
aprats
New Contributor II
2 years ago

Table View Numer Formatting

Hi all,

I have an issue with table view number formatting. On my SQL query, I'm introducing the format: 

Select FORMAT (Sum(Year_01), 'N2', 'es-es') as Year_01 ")

After that, I populate the table view applying the true option in "UseDataTypes"

tv.PopulateFromDataTable(dt, True, True)

My issue is the next one: the format seems correct but, after openning or refreshing the tableview, it introduces the value as Text. 

I must enter on a cell and click the enter button to be able to recognize it as number. 

 

Does anyone have the same issue? 

Thanks,

Albert

 

 

Reports
  • aprats's avatar
    aprats
    2 years ago

    Hi MikeG and  JackLacava ,

    After beeing testing a lot of different options, I have solve it. JackLacava you was right. If I use FORMAT in my SQL, the column is stored as Text and it's imposible to solve the issue. 

    At last, the way to obtain the column in number format has been this one: 

    1. Define the text columns as Decimal. In my case, the columns that contains "Year":

    2. After that, the system is making an error derivated from the culture. I don't know why exactly, but if you are using ',' as decimal and '.' for thousands (for example: 1.150,12), the table view shows 115012. It seems that it can't be able to read ',' as decimal separator. 

    3. To solve it, when I'm inserting the rows, for number columns, I'm replacing ',' for '.'.

    Whit this, the system understand that the '.' is the decimal separator and it shows perfectly. The only thing left is the thousand indicator. I don't find a way to solve it.

    Hope it help you!

    Albert

     

     

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    Can you handle the number format directly in your Dashboard component?  Is it a Grid View or SQL Table Editor dashboard component?  It would look something like this:

     

  • aprats's avatar
    aprats
    New Contributor II
    2 years ago

    Hi MikeG and  JackLacava ,

    After beeing testing a lot of different options, I have solve it. JackLacava you was right. If I use FORMAT in my SQL, the column is stored as Text and it's imposible to solve the issue. 

    At last, the way to obtain the column in number format has been this one: 

    1. Define the text columns as Decimal. In my case, the columns that contains "Year":

    2. After that, the system is making an error derivated from the culture. I don't know why exactly, but if you are using ',' as decimal and '.' for thousands (for example: 1.150,12), the table view shows 115012. It seems that it can't be able to read ',' as decimal separator. 

    3. To solve it, when I'm inserting the rows, for number columns, I'm replacing ',' for '.'.

    Whit this, the system understand that the '.' is the decimal separator and it shows perfectly. The only thing left is the thousand indicator. I don't find a way to solve it.

    Hope it help you!

    Albert

     

     

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    Can't find it right now, but there was another thread with this same issue. I believe the fact is that, once you use FORMAT in your SQL, the DataTable object will contain the column as Text.

    You should try without FORMAT, and then ensuring your DataTable object has been configured to see that column with the right datatype, at which point the Populate call should do the right thing and give you a TableView containing numbers. Have a look at this thread for that.