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!
Report Error: Nested reports cycle detected while trying to load.
Please suggest.
Hi Sergey,
How can i enable feature in report ?
Thanks & Regards,
Gaurav Katiyar
Hi Gaurav,
This is not a standard feature in Acumatica. It requires some customization.
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
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.
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
Hi Gaurav,
InventoryTranDetEnqFilter is exactly DAC.
namespace PX.Objects.IN
{
#region FilterDAC
public partial class InventoryTranDetEnqFilter : PX.Data.IBqlTable
{
…………..
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.
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.
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
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
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.