Restriction condition on date

[sridharkannan77]

I am having the following condition in restriction
(
(
FromDate >= '2024-06-01 00:00:00'
AND ToDate <= '2024-06-13 00:00:00'
AND (RecordType IN ('Login') OR RecordType IN ('Sanction') )
)
OR
(
RecordType IN ('WIP')
AND FromDate >= '2024-03-01 00:00:00' (This is From Date -3 months)
AND ToDate <= '2024-06-13 00:00:00'
)
)

If the Record Type is "Login" or "Sanction", the from date should be the current month's 1 date by default (ThisMonth)
If the Record Type is "WIP", the from date should be 3 months back from current month .i.e. (Thismonth-3M)

When the report is executed, when the user changes the fromdate and todate, the records are showing correctly for the recordtype "login" or "sanction". But the fromdate doesn't go back to 3 months for the record type "WIP". It always picks up 3 months from the default from date... And not the date changed by the user..

Ex: If today is 14-Jun-2024, the from date tp pick the records for record type "login" is 01-Jun-24 and the fromdate to filter out records for "WIP" is 01-Mar-24 (This works fine when the report is executed for the first time)

Now when the user changes the from date to "14-May-24",
this fromdate becomes the fromdate for record type "Login" and it works fine
However the fromdate for Recordtype "WIP" should now be 01-Feb-24 and records should be filtered accordingly.. But this still takes the from date as "01-Mar-24" instead of "01-Feb-24"

Any help will be appreciated

[ariacom]

For this I would recommend to use Input Value and then a Script to set the value in the restriction:

• Select General node in the report tree view, then create an Input Value of type DateTime and prompt it.
• Create a script the copy the value to your restriction in your models, here is the sandard script sample:

@using System.Data
@using System.Data.Common
@{
    //Copy date input values to all date restrictions in the report
    ReportTask task = Model;
    Report report = task.Report;
    //Take the first input value that has been defined in the report
    var inputValue = report.InputValues[0];
    if (inputValue.Date1 == DateTime.MinValue) {
        inputValue.Date1 = DateTime.Now;
    }

    foreach (var model in report.Models) {
        foreach (var restriction in model.Restrictions.Where(i => i.IsDateTime /* i.SQLColumn == "Orders.OrderDate" */)) {
            restriction.Operator = Operator.Between;
            restriction.Date1Keyword = "";
            restriction.Date2Keyword = "";
            restriction.Date1 = inputValue.Date1.AddYears(-1);
            restriction.Date2 = inputValue.Date1;
        }
    }
}

with the script you should be able to achieve what you want.