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.

SQL statement

Source code:

Have a nice reporting!

11 Replies to “Rates per Day in the Report”

  1. Hi Sergey,

    I have to 2 parameters in report – Warehouse & Location
    So i am using below View name in Parameter’s
    1. =Report.GetFieldSchema(‘InventoryTranDetEnqFilter.SiteID’) => This is for Warehouse
    2. =Report.GetFieldSchema(‘InventoryTranDetEnqFilter.LocationID,Warehouse’) => This is for Location

    Can i add some more parameters in this Report.GetFieldSchema() function (second point having two parameters) ?

    InventoryTranDetEnqFilter – This inquiry filter, we have added in the Inquiry Graph, Is it Correct ?

    If yes, then how can i achieve this in my report without using this filter ?

    Regards,
    Gaurav Katiyar

    1. Hi Gaurav, second parameter in GetFieldSchema is needed for special PXSelectorAttribute where you have a BQL Select with Optional or Current parameters. As there is no graph in the report, you need pass the additional parameter.

      1. Hi Sergey,

        In the above question, Where we have created this filter named as ‘InventoryTranDetEnqFilter’ ?
        It’s Not a DAC ?
        I think, this will create from Filter screen…Is it correct ?

        Regards,
        Gaurav Katiyar

        1. Hi Gaurav,
          InventoryTranDetEnqFilter is exactly DAC.
          namespace PX.Objects.IN
          {
          #region FilterDAC
          public partial class InventoryTranDetEnqFilter : PX.Data.IBqlTable
          {
          …………..

          1. Hi Sergey,

            I am using 2 parameters in my report..InventoryCD & LotSerialNbr
            Below are DAC selectors, which i am using in my report parameter ..

            [PXSelector(typeof(Search),
            typeof(InventoryItem.inventoryCD), typeof(InventoryItem.descr),
            SubstituteKey = typeof(InventoryItem.inventoryCD))]
            public virtual string InventoryCD

            [PXSelector(typeof(Search2<INLotSerialStatus.lotSerialNbr,
            InnerJoin…………
            Where<INLotSerialStatus.inventoryID, Equal<Current2>>>),
            new Type[] {
            typeof(INLotSerialStatus.lotSerialNbr)
            })]
            public virtual string LotSerialNbr

            In my report, I am using below view in parameters
            =Report.GetFieldSchema(‘MyDAC.InventoryCD’)
            =Report.GetFieldSchema(‘MyDAC.LotSerialNbr,[1st Parameter Name]’)

            I am unable to get LotSerialNbr data as per InventoryCD..
            In the above, Am i missing something ?..Plz help.

          2. Hi Sergey,

            As i said earlier, I am doing the same thing.
            The only issue i am facing is..
            My Second lookup selector is not refresh or updated with filtered data based on First lookup selector on the Report.

            Please suggest if i missed something.

          3. Hi Sergey,

            I have created FilterDAC for those report properties & decorate with PXSelector inside the FilterDAC.
            & Used below functions for parameter in report
            =Report.GetFieldSchema(‘MyDAC.InventoryCD’)
            =Report.GetFieldSchema(‘MyDAC.LotSerialNbr,[1st Parameter Name]’)

            But issue remain same…plz help

          4. In my report, I am using below view in parameters

            =Report.GetFieldSchema(‘MyDAC.InventoryCD’)
            => This view is for Parameter (Inventory CD)
            =Report.GetFieldSchema(‘MyDAC.LotSerialNbr,[1st Parameter Name]’)
            => This view is for Parameter (LotSerial Nbr)

            I am using below selectors on MyDAC

            [PXSelector(typeof(Search<INLotSerialStatus.lotSerialNbr, Where<INLotSerialStatus.inventoryID, Equal<Current>>>), typeof(INLotSerialStatus.lotSerialNbr))]
            public virtual string LotSerialNbr {….

            [PXSelector(typeof(Search),
            typeof(InventoryItem.inventoryCD), typeof(InventoryItem.descr), SubstituteKey = typeof(InventoryItem.inventoryCD))]
            public virtual int? InventoryID {….

            I am unable to get filtered data for “LotSerialNbr” as per “InventoryCD” selection..
            Please help

            1. Hi Gaurav,
              I’m really sorry for the delay. I tried to check, but not sure what is wrong.
              Could you please create a support ticket with this problem. Our support engineers have more experience with reports than me.
              Very sorry for inconveniences.

Leave a Reply

Your email address will not be published. Required fields are marked *