1. 

   apao, Member

   Hi everyone,

   I just noticed that numbers in my myDBR reports are rounded to two decimal places. For instance, 12.1234 is rounded to 12.12.
   This is not as Excel issue: I opened the CSV file (generated by myDBR) with notepad to confirm that the number written by myDBR into the file is actually 12.12 instead of 12.1234.

   Is there a way to change this behavior?

   Thanks in advance for sharing knowledge.
2. 

   myDBR Team, Key Master

   myDBR shows floating numbers by default in two decimals. You can set a column style to define the number of decimals to be shown.

   select 'dbr.colstyle', 'column', '%.4f';
   
   select 12.1234 as 'column';
   
   

   --
   myDBR Team
3. 

   apao, Member

   Thanks for your answer.

   Unfortunately, it can't work in my case because I use a stored procedure to run several SELECT queries in my report and the only place where I can add this dbr.colstyle "statement" is before I call the stored proc. As a consequence, it doesn't work because the dbr.colstyle statement isn't placed just before the SELECT that would need it.

   Anyhow, I found a workaround: I cast my value as CHAR. Not ideal but it works.

   However, if it's not too hard to implement, maybe it might be helpful in the future to have an environment setting to choose the number of decimals myDBR rounds floating numbers?

   Once again, many thanks for your answer.
4. 

   myDBR Team, Key Master

   Not sure what you mean by that.

   What is preventing you from adding the dbr.colstyle -command to the procedures if you can cast the values to char?

   --
   myDBR Team
5. 

   apao, Member

   Hi,

   My stored proc executes 2 SELECT queries :
   - one to display the parameters passed to the report by the user
   - one that executes the SELECT query passed to the stored proc

   So for each report, I call this stored proc and I pass to it the parameters of the report and the "skeleton" of the SQL query so that the stored proc can create both SELECT queries and execute them.

   So casting my floating number as CHAR is easy to do because it's part of the SQL query I pass to my stored proc. On the other hand, adding the "SELECT dbr.colstyle" command wouldn't be as easy. In fact, I'd have to change my stored proc so that it accepts the "SELECT dbr.colstyle" command as an optional parameter and executes it if it's present.
   Casting as CHAR seems to work so I'll stick to it for the time being.

   However, being able to choose the way myDBR rounds floating numbers (in general) might be nice, too.
6. 

   myDBR Team, Key Master

   You are using dynamic SQL inside your reports and the actual SQL query comes as a parameter? Is there a reason for this? Usually, the queries are in the routines instead of in the parameters. Just trying to make sure you are using reports in an optimal way.

   Casting the numbers into char changes the datatype and things like sorting in the reports are done as if the columns are text. Likewise, things like column filters work differently in char as opposed to numbers.

   You can change the default precision of the floating number with a definition in user/defaults.php:

   $mydbr_defaults['formatting']['float_decimals'] = 4;

   --
   myDBR Team
7. 

   apao, Member

   Ok, thanks!

   No, the SQL query itself is not a parameter chosen by the user.
   But the SQL query is a parameter of my stored proc so for each report, I call this stored proc with the SQL query (of the report) as a parameter and with the parameters chosen by the user.

Reply

You must log in to post.