Dynamic Date filters for Reports scheduled via Job Queue

When creating a Job Queue Entry that schedules to run a report, it is possible to gather request page parameters.

Unfortunately, up to now, if you have to enter a dynamic date like e.g. TODAY or WORKDATE then these tokens are converted into a non-culture dependent value representing the input date. Therefore, if you would like to have a report that e.g. change dynamically such date this is not currently possible out-of-the-box.

This is well-known by partners and developers and there have been a couple of IDEAS of improvement to Microsoft in relation to this specific topic. See below the most relevant ones

https://experience.dynamics.com/ideas/idea/?ideaid=5ac29655-3e41-ec11-a3ee-0003ff45ebbf

Some reports are run regularly and require date parameters. Examples are:
* Inventory valuation
* Customer Statement
These reports cannot be run successfully on a job queue, as the report date parameters are fixed.
Likewise, these cannot be scheduled to run say every month, and appear in the report inbox, as the date parameters will only be correct for the first month.
It would be great to be able to use date formulas in the report request page when running the report on a job queue or when schedule the report.
For example the inventory valuation report could be scheduled to run on day 1 of each month, from W-D1-1M..W-D1-1D, and appear in the report inbox.

https://experience.dynamics.com/ideas/idea/?ideaid=79eca196-a2c8-e911-b083-0003ff68dcb2

In Pages it's possible to use dynamic filters like %MYCUSTOMERS to create views that support users with "their" data. The advantage is the flexibility of the filter range - it's always up to date.

If I use the same filter in a report (created by a job queue entry or using a report filter template) the dynamic filter expression won't be stored in the report options (templates). Instead you'll figure out that the report always uses the same filter range (depending on the values when the report runs first). It doesn't matter that "%MYCUSTOMERS" contains more customers now.

The same problem comes with date filtering.

Using job queue entries for routine activities is fine, but with fixed filter values sometimes it's unusable.

This blog post has the intent to inspire you in creating your own private IP to supersede these designed behaviors and make report recurring schedules more flexible.

HIGH LEVEL OVERVIEW

Request page parameters are stored by the application in the form of an XML file. Job Queue Entry table publishes specific events where it is possible to hook up the XML file and change it JIT with the desired value. In this way, reports could be printed with dynamic report request page parameters.

PROTOTYPE PROOF OF CONCEPT

This proof of concept is made of

1 Page Extension

Just add an action in Job Queue Entry in Related > Job Queue > Show Request Page in XML Format. This is useful to inspect request page options and dataitems. 

1 Table + 1 Page

Contains the node that needs to be changed in the XML file, considering if these are Options fields or DateItem strings.

2 Enum

One for the node type (Options or DataItem) and one for the type of substitution needed (e.g. TODAY, WORKDATE, etc.)

1 Codeunit

This is the heart and soul of the substitution. It subscribes to OnAfterGetXmlContent in Job Queue Entry table.

DEMO

Let’s Consider report 111. A typical XML formatted request page parameter could be

While report 7050 might have something like e.g.

 Let’s assume that we would like to have a dynamic filtering and change Date Filter in report 111 and as of date in report 7050 in order to always print TODAY date.

This means that report 111 should change dynamically this node

<DataItem name="Customer">VERSION(1) SORTING(Field1) WHERE(Field55=1(..2022-07-04))</DataItem>

While report 7050 should change dynamically this other node

<Field name="DateReq">2022-04-11</Field>

After deploying the extension, then, it is needed to setup the substitution fields in Job Queue Rep.Field Sub. List. As per below. This should be self-explanatory.

From now on, every existing or new Job Queue Entry that runs with report 111 or report 7050 will run by changing dynamically the Dates specified above with TODAY date. And this is done by simply subscribing to one specific event:

    [EventSubscriber(ObjectType::Table, Database::"Job Queue Entry", 'OnAfterGetXmlContent', '', false, false)]

    procedure OnAfterGetXmlContent(var JobQueueEntry: Record "Job Queue Entry"; var Params: Text)

    begin

        if Params <> '' then

            UpdateParams(Params,JobQueueEntry."Object ID to Run");

    end;

and implementing the logic to update the XML file with parameters. Of course.

In this proof of concept, I took into considerations only date substitutions but consider this as the backbone to replace every single value from the stored XML parameter.

You can fork or download the source code from GitHub or download the file from this blog post

dtacconi/JobQueueReportDynamicsParameters (github.com)