Dividing Numbers

Stefan shared this question 2 months ago
Answered

Is it possible to get remainders when dividing numbers in BMC Smart Reporting ?

/AWno4Dh0nFMaAAAAAElFTkSuQmCCI'm currently dividing second to last column with UkIn column and getting whole number division.

Is there some setting that I need to set correctly to get values that I need.

For example for first row I need to get in last column 8.33% ( (1/12)*100 ) but I'm getting 0.

The Ukupno Incidenata (UkIn) field:

/n+BSnysMPcJUAAAAABJRU5ErkJggg==

Unutar 24 sata (ukupno) field:

/0oO5mom28NAAAAAElFTkSuQmCC


Where Hours to Resolve is:

/AXbCsYgJQT5pAAAAAElFTkSuQmCC

And Unutar 24 sata (%) is:

/d8akXr3g+wkAAAAASUVORK5CYII=

With format set to:

/fuXNHnn788cfb29tXr15dbK0Wvy5nmTFdBQBARZcvX261Wnfv3v3kk09ardbly5cXXSNSTim7TggAAJQbDAavvvpqu90eDAaLrkuek3KKyEwWAzkAAIxld3d3d3d30bV4jJQDAADiRMoBAABxIuUAAIA4kXIAAECcSDkAACBOpBwAABAnUg4AAIgTKQcAAMSJlAMAAOJEygEAAHEi5QAAgDjNNeVcAgAAOGx2wYOxHAAAECdSDgAAiBMpBwAAxImUAwAA4kTKAQAAcSLlAACAOJFyAABAnEg5AAAgTqQcAAAQJ1IOAACIEykHAADEiZQDAADiRMoBAABxIuUAAIA4kXIAAECcSDkAACBOpBwAABAnUg4AAIgTKQcAAMSJlAMAAOJEygEAAHEi5QAAgDiRcgAAQJxIOQAAIE6kHAAAECdSDgAAiBMpBwAAxImUAwAA4kTKAQAAcXrllVf+f9FO1WdHz7RTAAAAAElFTkSuQmCC

Best Answer
photo

Hi Stefan,

Thanks for reaching out. This has actually been logged by BMC themselves before and it turns out that This is related to how the values are being handled by the BMC driver (we have not been able to replicate on other dbs).

To use a simplified example, the integer 20 divided by the integer 7 will return 2. If these were treated as floats, the float 2.8 would be returned, but they're being treated as integers in BMC's driver. The SQL statement from Yellowfin is sending out 20/7 no matter what. In MySQL for instance, the SQL statement that Yellowfin sends returns 2.8571... because it recognizes these as float values. If we were now to put ".0" after these integers, then it forces the driver to recognize 20/7 as float values and return the desired result.

In terms of accomplishing this when referencing a column itself in a calculated field, considering the values are recognized as Integers by default, I'd guess this normally would be done by drafting a Freehand SQL CAST statement to convert the values to float values... Something like CAST(SUM(sl.parts) AS FLOAT), but I don't think Smart Reporting allows its users to draft Freehand SQL statements. As such, if you have further trouble with this though, please reach out to your BMC contact to assist further as they should have a better understanding of how their driver works and I'd guess they'd already know how to get around this, if possible given the Smart Reporting setup.

Please let me know if you have any further questions.

Regards,

Mike

Comments (8)

photo
1

Hi Stefan,

Thanks for reaching out. This has actually been logged by BMC themselves before and it turns out that This is related to how the values are being handled by the BMC driver (we have not been able to replicate on other dbs).

To use a simplified example, the integer 20 divided by the integer 7 will return 2. If these were treated as floats, the float 2.8 would be returned, but they're being treated as integers in BMC's driver. The SQL statement from Yellowfin is sending out 20/7 no matter what. In MySQL for instance, the SQL statement that Yellowfin sends returns 2.8571... because it recognizes these as float values. If we were now to put ".0" after these integers, then it forces the driver to recognize 20/7 as float values and return the desired result.

In terms of accomplishing this when referencing a column itself in a calculated field, considering the values are recognized as Integers by default, I'd guess this normally would be done by drafting a Freehand SQL CAST statement to convert the values to float values... Something like CAST(SUM(sl.parts) AS FLOAT), but I don't think Smart Reporting allows its users to draft Freehand SQL statements. As such, if you have further trouble with this though, please reach out to your BMC contact to assist further as they should have a better understanding of how their driver works and I'd guess they'd already know how to get around this, if possible given the Smart Reporting setup.

Please let me know if you have any further questions.

Regards,

Mike

photo
2

Hi Stefan,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you and I suspect I've covered your query, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

photo
1

I'm sorry for not replaying to your post.


I've talked to BMC support and they are currently looking into it on their side, but it seems to be that they 'convert' (for the lack of better word) any division result to integer so they cut off everything after decimal point.


Thank you for your time,


Mike.

photo
1

Hi Stefan,

No worries! Ah, that does seem familiar now that you mention it, but thanks for informing us!

Regards,

Mike

photo
1

SQL will always convert division by integer as an integer. For example:

select 5/2

Result: 2

select 5/2.00 

Returns a floating point or 2.500000


Somewhere in your calculation you need to include a decimal point. In your case, you need incorporate a decimal here:

((Unutar 24 sata (ukupno)) * 1.00) / ((Ukupno Incidenata (Ukin)) * 1.00)

or you can add 0.00

photo
2

Hi Larry,

Thanks for providing additional input. I think you may be correct about this solution. Due to the clients' restrictions, users on that system cannot script Freehand SQL statements, but it may very well be the case that their solution is to draft a Simple calculated field statement and multiply each fields' values by 1.00 to get the answer they're looking for. It may be helpful for others if you can confirm this though, Stefan!

Thanks,

Mike

photo
1

I tried doing this from the beginning hopping that forcing a value (in this case value with float/double type) would make it cast to said values type.

Unfortunately this did not give a correct result so that idea was dropped and I contacted this community.

Thanks for the idea thou. :)

photo
1

Hi Stefan,

Thanks for the response!

Regards,

Mike

photo