Report - Calculated Fields

[hogie503]

Hi Alpha Experts,

I am trying to cerate an Calculated Fields

I have a field called Amount in table A which is Decimal value or a NULL value, I would like to be able to print " ERROR" when the value is NULL or the Actual Decimal value,

EX:

Amount

195.50
200.10
NULL
600.00

in the expression / Calculated fields, I have something like this,

Amt= IIF(Amount ="", "ERROR", STR(Amount)) [ without the STR i get errors] this give me result but I cannot format the Amt as money values because it is now a string, I cant seem to figure out how to create the
new value where it can be formatted like $125.00, seem to be having problem with the conversion process, any help or ideas will be greatly appreciated.

also can a case statement be used in the Calculated Fields ?, if so please provide an example.

Thanks you.

[Russell Craycraft]

I would put the below into a view with all of your other fields. Do the work on the backend, and not worry about what's built in Alpha. I could be mistaken in what you're looking for, but that'd be my approach.

I also threw in your requirements, but otherwise you may want to build it out in sql, and use it as a varchar field, and do the same in Alpha if this is the route you're going.

SELECT
CASE
WHEN YourColumn IS NULL THEN 'ERROR'
ELSE CAST(YourColumn AS VARCHAR)
END AS DisplayValue
FROM YourTable;

[madtowng]

you could try: coalesce(columnValue,‘error’) as calcVariable

Use punctuation as required by the sql you use.

[hogie503]

Hi Russell, Thanks for responding, I tried that approach by doing exactly what you suggested and putting everything in a Store Procedure but the problem is we cannot format the dollars amount (VARCHAR column) to money $ values because its not Decimal and Alpha don't allow you to format VARCHAR Values in the form of $xxx.xx and I cannot figure out how to get the Decimal values.

[madtowng]

You might like this.
I used the @ variable for testing. replace it with your actual column name. Set the column alias to suit your needs.

image.png

[hogie503]

Hi Madtowng, that don't work for me, the Amount is the values in the Amount column , from the result of the sql, don't know if I can set all those values using the @ symbol

[madtowng]

Lets try this. The first image is showing the data in my mysql table using toad.
The second image is a script using AA.

image.png

image.png

[hogie503]

Thank you for the example, really appreciate the response, and taking the time to show this..

[madtowng]

Glad to help. The real question is did you get it to work for your report?

[hogie503]

Hi, Thank you once again
I saw the way you did it in XBasic, I have always use SQL Server Store Procedure for the dataset for all my reports so I am not familiar with doing it using XBasic this way, so I have to figure out how to make this work with my Alpha report.
If you have any links / Doc's or videos showing how to get this to work in the reports please send them my way.. Thanks again.

[madtowng]

I actually used a combination of sql and xbasic.
Show what you want the response from sql to look like.

[hogie503]

image.png

We are joining two tables when we run the Query we get results like this

user ID Date Amount
1 03/15/2024 $ 500.00
2 03/15/2024 $ 150.00
3 03/15/2024 NULL
4 03/15/2024 $100.00

But in the report we want to display ERROR whenever Amount is NULL, Below is the SQL statement I tried bet no luck, getting this error (Error converting data type varchar to numeric.)


CASE
WHEN Amount IS NULL THEN 'ERROR'
WHEN ISNUMERIC (Amount) = 0 THEN 'INVALID'
ELSE CAST(Amount AS DECIMAL(18,2))
END AS Amount,

[madtowng]

In my opinion, the case statement is overkill.
select seq, coalesce(concat('$ ',amount),'error') `calcVar` should provide the desired format as shown in the image below.

My work is done using mysql, but I believe sql server/express should work basically the same way.

image.png

[Russell Craycraft]

Sorry for the delay. If you sent your full code I'd be able to help better, but this is what goes in your view in place of amount.

CASE
WHEN amount IS NULL THEN 'ERROR'
ELSE CONCAT('$ ', FORMAT(amount, 'N2'))
END AS amount,

[Russell Craycraft]

The full statement would be similar to this:

SELECT

ID,
Name,
OtherField1,
OtherField2,
CASE
WHEN amount IS NULL THEN 'ERROR'
ELSE CONCAT('$ ', FORMAT(amount, 'N2'))
END AS amount

FROM YourTableName