Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

[Kevin at LECESSE]

This is an updated question regarding a topic that exists in the version 10 forum
Post #735435

The question results from not being able to dynamically format the number of decimal places in a report.
I had originally posted these questions in the version 10 forum but I am on V12 and the issue will get more visibility here


My situation is this, I want to vary (Dynamically!) the number of decimal places shown on the report for a field according to the magnitude of the number in that same field
As the field value changes row by row in my report I want the number of decimal places to change depending on the following:
If the value of the field is >=100 then I want zero decimal places
If the value of the field is <100 then I want two decimal places

I put the following expression in the expression builder (available at the DEC parameter of the Properties pane in the report writer)
if(Field Name >=100,0,2) where in my case the Field Name is "Escalated_Cost_Per_Uom_3"
This field is a calculated field created in a view in the MYSQL backend

see attached pictures of the expression builder and the field selector screen
Field Selector.jpgExpression Builder.jpg
I could create no instance where this expression took hold and worked as I think it should
I tried blanking out the decimal place settings in the various other places they can be set
I tried with different field names in the expression
I tried by entering a single number in the expression
I tried publishing the report to Production environment

I did note that the expression passed the internal testing for validity and completeness. It was returning valid results of 0 and 2 and the results changed as I clicked through the records in the dataset
The result was a numeric type N
<dynamic> even appeared in the DEC parameter line as I guess it should

Does anyone know what I am doing wrong or if this feature works at all?
I am on version 12.4.5.2 build 4770 of Alpha Anywhere V4.5.2 with system addins build 5018
Also MYSQL56 5.6.39

Thank you for any help you can offer

Kevin Callanan

[Davidk]

Re: Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

Have to say... Alpha Report stuff is some of the oldest... and worst... I've ever come across. It's awful.

The only way I could get the results you wanted was by creating 3 Calculated Fields and then using 2 of those fields in a Conditional Object.

An "if" statement used anywhere simply does not work. If tried it in Dynamic Decimal and in Dynamic Format. I'm using the latest pre-release... so this has nothing to do with a release... and everything to do with how old and patched reports are.

The calculated fields amt2 and amt3 are dragged into their respective Conditional Object conditions. CustAmount is my database field name. I'm not sure how your Field Type ExponentNumber affects anything.

rptCond1.PNGrptCond2.PNG

[Al Buchholz]

Re: Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

Conditional object

Filter to determine the layer to display

Place the field object on each layer.

Adjust the format of each object to display the needed decimals

[Davidk]

Re: Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

Even better... and cleaner... no need for calculated fields... and it rounds automatically when setting Dec to 0.

[peteconway]

Re: Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

I feel it as well David, but on more than reports - I sometimes wonder if we are all on a train wreck waiting to happen, the lack of attention to detail, and lack of robust testing, backward incompatibility and the long journey between official releases plus the recent security issue not to mention the response to submitting a bug report - all while under a subscription has me concerned. I recently had to go back to the official release and work, I cannot believe the issues I bumped into - I just had to shake my head an wonder about it all.

[Pat Bremkamp]

Re: Dynamic formatting of number of decimal places in a report: Doesn't work. Bug or operator error?

We have the same problem, maybe worse. We print a report where the number of decimals varies by line from 0 to 4 decimals. The solution was to realize that you can use a character field on the report. So we added a field to the table and created an SQL function 'num_to_str()' We feed in the number as decimal(12,8) and number of decimals we need and out pops the character representation formatted in the correct number of decimals. The report uses that character field instead of the numeric field. The following works in mariadb and mysql.

FUNCTION `num_to_str`(p_Value decimal(16,4), p_Decimals tinyint(1)) RETURNS varchar(20) CHARSET utf8
NO SQL
DETERMINISTIC
BEGIN
/* num_to_str by Pat 07/01/2013
takes a decimal value and the number of decimals and
returns character representation */
DECLARE v_Result varchar(20);
SET v_Result = '';
IF p_Decimals = 4 THEN
SET v_Result = FORMAT(p_Value,4);
ELSEIF p_Decimals = 3 THEN
SET v_Result = FORMAT(p_Value,3);
ELSEIF p_Decimals = 2 THEN
SET v_Result = FORMAT(p_Value,2);
ELSEIF p_Decimals = 1 THEN
SET v_Result = FORMAT(p_Value,1);
ELSE
SET v_Result = FORMAT(p_Value,0);
END IF;
SET v_Result = REPLACE(v_Result,',','');
SET v_Result = trim(v_Result);
RETURN(v_Result);
END