E10 BAQ calculated field question

Yahoo Archive
1
You are most welcome :) Glad to be of help. C# and SQL are my two strongest languages, so as you can imagine I was very happy for the change to a pure MS environment !
2

I have an amount field that I need to convert to a string field (which is going to be combined with a larger string).  This field cannot contain a decimal point and while the field itself has 3 decimal places, I need to display only two.  Also, it can only be 10 characters long and must be zero filled.  So for example I have:

 

353.450 and this must be converted to 0000035345  (btw, this is data that will be transferred to our bank and they require this format)

 

I have everything in place to do what I want but am not sure about the syntax to have it only display 2 decimal places.  What I have so far is:

 

RIGHT('0000000000' + REPLACE(CONVERT(varchar(10),CheckHed.CheckAmt),'.',''),10)

 

This does everything I want it to do EXCEPT to display only 2 decimal places.  So from my example above, my calculation displays 0000353450

 

Any MS gurus know the syntax to make this work?  Also, if there is a more elegant way to do the above, I’m open to suggestions.

 

In Crystal syntax, I just used the following:

 

Right ("0000000000" + ToText ({CheckHed.CheckAmt},2,'',''),10)

 

I really, really miss Crystal…

 

Thanks,

 

 

Mike Lowe

Director of I.T.

Connor Manufacturing Services

USA-Singapore-China-Malaysia

Phone: +1 (503) 465-3904

Cell: +1 (949) 793-3433                                                          

Web:    www.ConnorMS.com

 

3
Is this for SSRS or is this C# syntax you are wanting?
4

I’m doing this in the actual BAQ as a calculated field.  So I’m guessing C#?

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, October 01, 2015 11:03 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10 BAQ calculated field question

 

 

Is this for SSRS or is this C# syntax you are wanting?

5
Are you using C# syntax or SSRS?
6

If it is actual C# this will do the job.


decimal joe = 353.450M;


joe.ToString("00000000.00").Replace(".","");





//Returns 0000035345





7

I probably muddied the waters by mentioning Crystal.  

 

Not C# ( I don’t think)  It’s SQL.  Here’s an example of my query:

 

                [BankAcct].[Company] as [BankAcct_Company],

                [BankAcct].[BankAcctID] as [BankAcct_BankAcctID],

                [BankAcct].[CheckingAccount] as [BankAcct_CheckingAccount],

                [CheckHed].[CheckDate] as [CheckHed_CheckDate],

                [CheckHed].[Name] as [CheckHed_Name],

                [CheckHed].[CheckNum] as [CheckHed_CheckNum],

                [CheckHed].[CheckAmt] as [CheckHed_CheckAmt],

                [CheckHed].[Posted] as [CheckHed_Posted],

                (BankAcct.CheckingAccount + RIGHT('0000000000' + CONVERT(varchar(10),CheckHed.CheckNum),10) + Replace(convert(varchar(8), CheckHed.CheckDate,1),'/', '') + RIGHT('0000000000' + REPLACE(CONVERT(varchar(10),CheckHed.CheckAmt),'.',''),10)) as [Calculated_DetailLine]

from Erp.CheckHed as CheckHed

inner join Erp.BankAcct as BankAcct on

                CheckHed.Company = BankAcct.Company

And

                CheckHed.BankAcctID = BankAcct.BankAcctID

 

where (CheckHed.CheckDate = @CheckDate)

order by  CheckHed.CheckNum

 

Again, I’m doing this within the BAQ Query Designer.

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, October 01, 2015 11:10 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10 BAQ calculated field question

 

 

Are you using C# syntax or SSRS?

8

In SQL Then you would want to use this:


if input is 174.430

REPLACE(REPLACE(STR(ABS(Number01), 11, 2), ' ', '0'), '.', '')

return will be 0000017443

9
Replaceing the Number01 with whatever value you are wanting to convert. You don't need to do any Convert()here though. That operation is not need. You can just 'stringify' everythng and its less work on the DB :)
10

Worked beautifully.  Thanks so much!  Still getting used to working in a pure MS/SQL environment after being on Progress for a long, long time.

 

Thanks again!

 

Mike

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Thursday, October 01, 2015 11:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: E10 BAQ calculated field question

 

 

Replaceing the Number01 with whatever value you are wanting to convert. You don't need to do any Convert()here though. That operation is not need. You can just 'stringify' everythng and its less work on the DB :)