Wednesday, 18 October 2017

Rates per Day in the Report

Hi All,

Today I would like to share you my experience in building report that needs to translate currency rate on the fly.
The main challenge in this task as you may not have rates for every day. So that mean if your document is for 20th of Jan but there is no rate for that date, you need to find the latest available from list of the rates. Unfortunately this task is solvable just with standard Joins and standard DACs as we need to have little bit more tricky logic.

So to fix that I have created an SQL view that can find last available rate for the particular date.Than I have added custom DAC to Acumatica that later can be used in the Report Designer.  You can find full code of the view and the DAC in later in this article.

Here you can see join conditions
You also can use select following SQL statement where you can check how does it work.

Source code:

Have a nice reporting!

No comments: