I have a Generic Inquiry that sums transactions over a date range specified by Required Parameters StartDate and ThruDate with Default Values of @YearStart and @MonthEnd-1 respectively:
As I needed the ThruDate in Excel exports of this GI, I added it to the Results Grid:
The unexpected result is the Parameter (Thru Date) and the Result column (Statement Date) get different values when the Inquiry is executed:
If the Parameter is manually changed in the results screen, the dates match.
The value in the resultant column is the date being applied (not the defaulted date displaying at top) verified by the calculated values and manually entering the dates.
Page 1 / 1
Seems like it did @MonthEnd+1 rather than @MonthEnd-1.
What about replacing @MonthEnd-1 with DateAdd(@MonthEnd,’d’,-1) ?
Thanks @TimRodman,
I am using the @MonthEnd to as a default for a parameter in the GI, so the DateAdd() is not available to me there, or I am naïve of it’s availability. As I need to allow user to be able override the default value, defaulting the parameter is preferred.
I was delighted that the @MonthEnd-1 was giving me the last date of the prior month, as desired, in the Parameters section. When seeing some unexpected results, I output the parameter value to the grid and found it to be different (the next day) from the value in the parameters.
I think that UTC, not the date subtraction, is your issue. I tested this with @MonthEnd and see the same behavior. I’ve seen similar behavior on other Generic Inquiries.
According to the Acumatica documentation:
All the date-relative parameters use the date (in UTC) of the server used to run the Acumatica ERP instance as the current date.
Running a SQL Trace on the query you may see something like this:
DECLARE @P0 nvarchar(MAX) SET @P0='6/30/2021 11:59:59 PM' DECLARE @P1 SmallDateTime(4) SET @P1='7/1/2021 3:59:59 AM'
SELECT TOP (15) EPMProject]. ContractID] AS nPMProject_ContractID], @P0 AS tThruDate] FROM eContract] MPMProject] WHERE ... AND ( RPMProject]. ExpireDate] <= @P1)
Notice that P0 and P1 are different, where P0 is the result column and P1 is the value used in the WHERE clause.
On the other hand, when the user enters a manual date for the parameter, the UTC conversion does not happen. As a result, the system uses the date as specified.
Another point to bear in mind is that the UTC adjustment will only cause a visible date change for the date-relative “end”dates. This is because the relative start date parameters are of the form ‘6/1/2021 00:00:00 AM’, therefore adding some number of hours to this doesn’t shift the date.
Maybe you could make your ThruDate default to @MonthStart (instead of @MonthEnd - 1), and set your condition to be < eThruDate]. If you do this then users would need to aware that when they enter an end date manually the results will only include records BEFORE that date.
I would love to get some further clarification from other Acumatica GI wizards (if some are monitoring this conversation) to confirm that I am understanding this correctly and maybe there’s a better way to do this!
Laura
@brianmcmillin100 Ah, good point, that won’t work in a Generic Inquiry parameter. I was thinking of Report Designer parameters which allow you to write formulas.
Seems like @lauraj46 has this one figured out. Maybe Acumatica just needs to introduce separate “UTC” options for the date relative options like they have in the expression editor:
Agree with @TimRodman that UTC options on Date Relative options would be helpful!
It would be great to have the ability to build an arbitrary formula for parameter defaults like you can in the result fields and the conditions. With that capability you could write the formula to make an adjustment for the UTC.