Community
Showing results for 
Search instead for 
Do you mean 
Reply

Report expression problem / question

Copper Contributor
Posts: 22
Country: United_Kingdom

Report expression problem / question

Hi,

     I've created a report which calculate VAT (Tax) and the total cost of the product(s) including VAT. The problem I have is that if the VAT comes out to say £11.906 I then formats  the results to two decimal places the result becomes £11.91. I want it to show £11.90.

 

I use the following expressions in the stages of the calculations / report   

 

format (Product1Price1* (Product1VAT1/100),"currency")  This converts it to currency

 

formatnumber(Product1Price1* (Product1VAT1/100),2)   This gives me the VAT PRICE

 

format(sum(Product1Price1+ (Product1Price1* (Product1VAT1/100))),"currency")  This gives me the total price inc VAT as currency

 

formatnumber(sum(Product1Price1+ (Product1Price1* (Product1VAT1/100))),2)     This gives me the total price inc VAT 

 

format(sum( Custom10 + Custom11 + Custom31 + Custom32 + Custom33 + Custom34 + Custom35 + Custom36),"currency")

 

This gives me the grand total of all the products (there can be up to eight)

 

The reports works, but as I stated at the top I want the VAT to round down in all cases.

 

I've come across a VB command called Math.floor  but I can't get it to work

 

Help !! Does anyone have any thoughts on how to do this? What is the correct syntax to round down in all cases?

 

Thanks in advance for any help / support

 

Siggi    

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: Report expression problem / question

Are you doing this as expression statements in the field or as a VB script?

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 22
Country: United_Kingdom

Re: Report expression problem / question

In the Data Expression field on the report design screen

Copper Contributor
Posts: 22
Country: United_Kingdom

Re: Report expression problem / question

report design screen.JPG

Copper Contributor
Posts: 34
Country: United_Kingdom

Re: Report expression problem / question

Wow.  I am afraid I would suggest you buy Sage Accounts and link the products through Opportunities (then you also won't be limited to 8) and then convert an Opp to an Invoice?  (Assuming you are running ACT! Premium 2012?)

 

Cat

 

Catriona Cripps

Wildcat Solutions

Copper Contributor
Posts: 22
Country: United_Kingdom

Re: Report expression problem / question

All I need to do is find out the syntax for rounding down to two decimal places – everything else is fine

Below is the tab from the contact screen that is used to input the prices as the customer did not want to use the opportunity screens

pricing 2.JPG

 

Invoice below (created by report)

 

pricing 3.JPG

Note the rounding  £238.10 x 5% = £11.905 which comes out as £11.91. I want to always round down, so that it comes out as £11.90 

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: Report expression problem / question

What you want can be done but not the way you're using. The field expressions are very limited and you need a more complex script. Right click on the section or the report and choose Edit Program Scripts to open the script editor.

 

There is a third party book with comprehensive training information on the ACT! reports, here is the link.

https://www.packtpub.com/sage-act-2011-dashboard-and-report-cookbook/book

 

Full disclusure I wrote the reports chapters of the book.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 22
Country: United_Kingdom

Re: Report expression problem / question

Hi Roy and thanks for the info.. I suspect I will end up buying the book so that I have more info for future projects as the expression commands are very limited.

 

I have however managed to resolve the rounding problem. I went back to basics and instead of doing all the calculations in the reports section, I just pick up the totals from the add on facility called “automatic Field calculations” by exponenciel. This add-on had already been installed to do various field calculations, so it was easy to pick up the correct data.  

I’ll order the book when I get my next ACT! Project.

 

Regards

Siggi