# BAQ Calculated Field Format to \$.00 Question

Thanks for publishing the link Bob. (Progress offers a free 4GL handbook on their site in pdf - about 1000 pgs - packed with this stuff.)

Most of the fancy 4GL fails in BAQs (no support built in to the BAQ designer) - but sometimes a useful jewel can be found.

Rob

--- On Thu, 8/7/08, Robert Carlson <rcarlson@...> wrote:
From: Robert Carlson <rcarlson@...>
Subject: RE: [Vantage] BAQ Calculated Field Format to \$.00 Question
To: vantage@yahoogroups.com
Date: Thursday, August 7, 2008, 3:43 PM

as below: note-the reference to

does work and yeilds an Open Edge 10 Progress 4GL Handbook.... ...very

technical but some good clues if you have the interest to dig for them:

Product: Vantage

Version: 8.00.811

Changed: 05/28/2008

Module: BAQ

Summary: BAQ General information on BAQ Calculated fields - Calculations

Details:

PAGE NO: 3643ESC

DESCRIPTION: BAQ General information on BAQ Calculated fields

The following comes from the Vantage help.

------------ --------- --------- --------- --------- --------- -

To create calculated fields, you will need to understand how Progress

evaluates calculations.

Example #1: This calculation is based on the InvDtl file. It calculates the

total cost of the invoice line (not its sales revenue) by adding together

the Labor, Burden, Material Burden, Material Cost and Subcontract Cost; it

then multiplies this sum against the line's Ship Quantity.

(+ InvDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitC ost +

InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost ) * InvcDtl.SellingShip Qty

Example #2: These equations calculate a value that is placed in either the

Debit or Credit column. This is based on the GL Journal Transaction amount.

Debit - (if gljrndtl.transamt > 0 then gljrndtl.transamt else 0)

Credit - (if gljrnidtl.transamt < 0 then gljrnldtl.transamt else 0)

Both calculations evaluate the Transaction Amount value field from the GL

Journal Detail table. If the amount is greater than zero, the amount is

placed within the Debit column. If the amount is less than zero, the amount

is placed within the Credit column.

Example #3 To round a value to two decimal places you can use the formula:

Round(LaborDtl. LaborHrs, 2)

Example #4 To add a value and then round use the formula.

Round(( + LaborDtl.LaborHrs + 0.005), 2).

Notice that I used parentheses to enclose the formula to add .005 to the

LaborHrs.

Example #5 To convert a date to the day of the week e.g. 1/1/2007 to Monday.

(If WEEKDAY(LaborHed. ActualClockinDat e) = 1 then "Sunday" else

(if WEEKDAY(LaborHed. ActualClockinDat e) = 2 then "Monday" else

(If WEEKDAY(LaborHed. ActualClockinDat e) = 3 then "Tuesday" else

( IF WEEKDAY(LaborHed. ActualClockinDat e) = 4 then "Wednesday" else

(IF WEEKDAY(LaborHed. ActualClockinDat e) = 5 then "Thursday" else

(If WEEKDAY(LaborHed. ActualClockinDat e) = 6 then "Friday" else

( If WEEKDAY(LaborHed. ActualClockinDat e) = 7 then "Saturday" else "0" )))) )) )

Example # 6: To create a MonthYear field. You will need use both a

concatenation and a string function. To do a concatenation use the +

symbol. Use the string as the "outer function. Note: for this example you

must set the data type to Character and the format to X(7).

string(Month( OrderHed. OrderDate) , "99") + "-" +

string(Year( OrderHed. OrderDate) , "9999")

Example # 7: To subtract two date fields you need to convert them to

integers first. Note: for this example, set the data type to integer and

the format to >>>,>>9.

------------ --------- --------- --------- --------- --------- -

NOTE 1: When calculating a Cost field you will want to set the data type to

Decimal and the format to >>>>>9.9999 where ">" implies suppress leading

zeros.

Available Data Types are:

Character

Integer

Decimal

Date

Logical

The format that this calculated field will use to display its result. There

are several single character format options. You use these options in

various combinations to display the results in the format that you want.

Here are the single character formats:

X - Any Character

N - Number or Letter

A - Letter Only

! - Lower Case Letter

9 - Number Only

> - Suppress Zeros

Commonly you will enter a single character format that with the syntax

<single-char- format>(nnn) . The <single-char- format> is any of the format

options listed above. The (nnn) variable is an integer that defines the

length of the calculated field.

Example: Here are some format examples that you can enter in this field:

X(16) -- A 16 character field

A(4) -- A letter only field that displays up to 4 letters

>>>>9 -- A number only field that displays numeral results up to 1,000

NOTE 2: If you are creating a calculated field for a summarized table, you

can only use the TOTAL, AVG, or COUNT expressions to display the table's

data. These expressions calculate summary views of the table's data.

------------ --------- --------- --------- --------- --------- -

1) Because the BAQs in Vantage are using the Progress 4GL programming

language in depth syntaxes will not be found in theVantage help. But you

may find syntax information in in this PDF file found on Progress website:

OpenEdge Development: Progress 4GL Handbook by John Sadd

Chapter 2 contains information on Arithmetic expressions, 4GL functions

(Day, Month, Year, Today), etc.

2) If you want need today's date use the TODAY function.

3) When adding a constant to a formula be sure that the constant starts

with a number rather than a decimal place for example if you add .005 to

LaborHrs the formula " + LaborDtl.BurdenHrs + .005 " will not syntax but "

+ LaborDtl.BurdenHrs + 0.005 " is okay.

At 03:25 PM 8/7/2008, you wrote:

>Seems to be broken, I have tested it here and it is not working either.

>

>Please go look up Answerbook 3643ESC, it has examples. This is supposed

>to use Progress 4GL syntax.

>

>This answerbook references a progress document.

>

>I am waiting on a BAQ call for another issue; I will ask when they call

>me.

>

>8.03.404A Progress

>

>Dale Schuerman

>

>Senior Consultant

>

>RAM Software Systems, Inc.

>1701 North Collins Suite 200

>Richardson, Texas 75080

>Phone:

>Fax:

>Web Site: www.ramsys.com

><blocked::<http://www.ramsys com/>http://www.ramsys com/>

>

>___________ _________ _________ ___

>

>From: <mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com

>[mailto:vantage@yahoogroups .com] On Behalf

>Of Robert Carlson

>Sent: Thursday, August 07, :35 PM

>To: <mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com;

><mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com

>Subject: RE: [Vantage] BAQ Calculated Field Format to \$.00 Question

>

>Hi Dale,

>

>Tried that format as an integer, character, & decimal..... ...puts the

>commas in; always returns 5 decimal places, never inserts the dollar

>sign.

>

>This is in 8.03.404B SQL

>

>Thanks for the suggestion though.

>

>At 10:13 AM 8/7/2008, Dale Schuerman wrote:

> >\$->>>,>>>,> >>.99

>

>[Non-text portions of this message have been removed]

>

>

[Non-text portions of this message have been removed]
Does anyone know of a way to format a calculated field in a BAQ to show
the dollar sign and the decimal display?
\$->>>,>>>,>>>.99

the - is for negative numbers

Dale Schuerman

Senior Consultant

RAM Software Systems, Inc.
1701 North Collins Suite 200
Richardson, Texas 75080
Phone: (972) 669-0763
Fax: (972) 669-9603
Web Site: www.ramsys.com <blocked::http://www.ramsys.com/>

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Bob Carlson
Sent: Thursday, August 07, 2008 8:57 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Calculated Field Format to \$.00 Question

Does anyone know of a way to format a calculated field in a BAQ to show
the dollar sign and the decimal display?

[Non-text portions of this message have been removed]
Hi Dale,

Tried that format as an integer, character, & decimal........puts the
commas in; always returns 5 decimal places, never inserts the dollar sign.

This is in 8.03.404B SQL

Thanks for the suggestion though.

At 10:13 AM 8/7/2008, Dale Schuerman wrote:
>\$->>>,>>>,>>>.99
Seems to be broken, I have tested it here and it is not working either.

Please go look up Answerbook 3643ESC, it has examples. This is supposed
to use Progress 4GL syntax.

This answerbook references a progress document.

I am waiting on a BAQ call for another issue; I will ask when they call
me.

8.03.404A Progress

Dale Schuerman

Senior Consultant

RAM Software Systems, Inc.
1701 North Collins Suite 200
Richardson, Texas 75080
Phone: (972) 669-0763
Fax: (972) 669-9603
Web Site: www.ramsys.com <blocked::http://www.ramsys.com/>

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Robert Carlson
Sent: Thursday, August 07, 2008 12:35 PM
To: vantage@yahoogroups.com; vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Calculated Field Format to \$.00 Question

Hi Dale,

Tried that format as an integer, character, & decimal........puts the
commas in; always returns 5 decimal places, never inserts the dollar
sign.

This is in 8.03.404B SQL

Thanks for the suggestion though.

At 10:13 AM 8/7/2008, Dale Schuerman wrote:
>\$->>>,>>>,>>>.99

[Non-text portions of this message have been removed]
as below: note-the reference to
does work and yeilds an Open Edge 10 Progress 4GL Handbook.......very
technical but some good clues if you have the interest to dig for them:

Product: Vantage
Version: 8.00.811
Changed: 05/28/2008
Module: BAQ
Summary: BAQ General information on BAQ Calculated fields - Calculations
Details:
PAGE NO: 3643ESC

DESCRIPTION: BAQ General information on BAQ Calculated fields

The following comes from the Vantage help.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To create calculated fields, you will need to understand how Progress
evaluates calculations.

Example #1: This calculation is based on the InvDtl file. It calculates the
total cost of the invoice line (not its sales revenue) by adding together
the Labor, Burden, Material Burden, Material Cost and Subcontract Cost; it
then multiplies this sum against the line's Ship Quantity.

(+ InvDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitCost +
InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) * InvcDtl.SellingShipQty

Example #2: These equations calculate a value that is placed in either the
Debit or Credit column. This is based on the GL Journal Transaction amount.

Debit - (if gljrndtl.transamt > 0 then gljrndtl.transamt else 0)

Credit - (if gljrnidtl.transamt < 0 then gljrnldtl.transamt else 0)

Both calculations evaluate the Transaction Amount value field from the GL
Journal Detail table. If the amount is greater than zero, the amount is
placed within the Debit column. If the amount is less than zero, the amount
is placed within the Credit column.

Example #3 To round a value to two decimal places you can use the formula:
Round(LaborDtl.LaborHrs,2)

Example #4 To add a value and then round use the formula.
Round(( + LaborDtl.LaborHrs + 0.005), 2).
Notice that I used parentheses to enclose the formula to add .005 to the
LaborHrs.

Example #5 To convert a date to the day of the week e.g. 1/1/2007 to Monday.
(If WEEKDAY(LaborHed.ActualClockinDate) = 1 then "Sunday" else
(if WEEKDAY(LaborHed.ActualClockinDate) = 2 then "Monday" else
(If WEEKDAY(LaborHed.ActualClockinDate) = 3 then "Tuesday" else
( IF WEEKDAY(LaborHed.ActualClockinDate) = 4 then "Wednesday" else
(IF WEEKDAY(LaborHed.ActualClockinDate) = 5 then "Thursday" else
(If WEEKDAY(LaborHed.ActualClockinDate) = 6 then "Friday" else
( If WEEKDAY(LaborHed.ActualClockinDate) = 7 then "Saturday" else "0" )))) )) )

Example # 6: To create a MonthYear field. You will need use both a
concatenation and a string function. To do a concatenation use the +
symbol. Use the string as the "outer function. Note: for this example you
must set the data type to Character and the format to X(7).

string(Month(OrderHed.OrderDate), "99") + "-" +
string(Year(OrderHed.OrderDate), "9999")

Example # 7: To subtract two date fields you need to convert them to
integers first. Note: for this example, set the data type to integer and
the format to >>>,>>9.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

NOTE 1: When calculating a Cost field you will want to set the data type to
Decimal and the format to >>>>>9.9999 where ">" implies suppress leading
zeros.

Available Data Types are:
Character
Integer
Decimal
Date
Logical

The format that this calculated field will use to display its result. There
are several single character format options. You use these options in
various combinations to display the results in the format that you want.
Here are the single character formats:

X - Any Character
N - Number or Letter
A - Letter Only
! - Lower Case Letter
9 - Number Only
> - Suppress Zeros

Commonly you will enter a single character format that with the syntax
<single-char-format>(nnn) . The <single-char-format> is any of the format
options listed above. The (nnn) variable is an integer that defines the
length of the calculated field.

Example: Here are some format examples that you can enter in this field:

X(16) -- A 16 character field
A(4) -- A letter only field that displays up to 4 letters
>>>>9 -- A number only field that displays numeral results up to 1,000

NOTE 2: If you are creating a calculated field for a summarized table, you
can only use the TOTAL, AVG, or COUNT expressions to display the table's
data. These expressions calculate summary views of the table's data.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

1) Because the BAQs in Vantage are using the Progress 4GL programming
language in depth syntaxes will not be found in theVantage help. But you
may find syntax information in in this PDF file found on Progress website:

OpenEdge Development: Progress 4GL Handbook by John Sadd
Chapter 2 contains information on Arithmetic expressions, 4GL functions
(Day, Month, Year, Today), etc.

2) If you want need today's date use the TODAY function.

3) When adding a constant to a formula be sure that the constant starts
with a number rather than a decimal place for example if you add .005 to
LaborHrs the formula " + LaborDtl.BurdenHrs + .005 " will not syntax but "
+ LaborDtl.BurdenHrs + 0.005 " is okay.

At 03:25 PM 8/7/2008, you wrote:

>Seems to be broken, I have tested it here and it is not working either.
>
>Please go look up Answerbook 3643ESC, it has examples. This is supposed
>to use Progress 4GL syntax.
>
>This answerbook references a progress document.
>
>I am waiting on a BAQ call for another issue; I will ask when they call
>me.
>
>8.03.404A Progress
>
>Dale Schuerman
>
>Senior Consultant
>
>RAM Software Systems, Inc.
>1701 North Collins Suite 200
>Richardson, Texas 75080
>Phone: (972) 669-0763
>Fax: (972) 669-9603
>Web Site: www.ramsys.com
><blocked::<http://www.ramsys.com/>http://www.ramsys.com/>
>
>________________________________
>
>From: <mailto:vantage%40yahoogroups.com>vantage@yahoogroups.com
>[mailto:vantage@yahoogroups.com] On Behalf
>Of Robert Carlson
>Sent: Thursday, August 07, 2008 12:35 PM
>To: <mailto:vantage%40yahoogroups.com>vantage@yahoogroups.com;
><mailto:vantage%40yahoogroups.com>vantage@yahoogroups.com
>Subject: RE: [Vantage] BAQ Calculated Field Format to \$.00 Question
>
>Hi Dale,
>
>Tried that format as an integer, character, & decimal........puts the
>commas in; always returns 5 decimal places, never inserts the dollar
>sign.
>
>This is in 8.03.404B SQL
>
>Thanks for the suggestion though.
>
>At 10:13 AM 8/7/2008, Dale Schuerman wrote:
> >\$->>>,>>>,>>>.99
>
>[Non-text portions of this message have been removed]
>
>

[Non-text portions of this message have been removed]
A voice from the past. (Good to hear from you Bob.)

... Can you set the format to character and then define a mask like '\$ >>>,>>9.99'

... Or perhaps change your calc formula to make a string ("\$ " & yourmath)?

Rob Brown

--- On Thu, 8/7/08, Bob Carlson <rcarlson@...> wrote:
From: Bob Carlson <rcarlson@...>
Subject: [Vantage] BAQ Calculated Field Format to \$.00 Question
To: vantage@yahoogroups.com
Date: Thursday, August 7, 2008, 9:56 AM

Does anyone know of a way to format a calculated field in a BAQ to show

the dollar sign and the decimal display?