Small and medium business | Business Central, N...

Generating Reports with Defined Filters

(0) Share

Report

Posted on by SBG33

Hello,

I am attempting to schedule a daily Service Task Report so that the system automatically generates a daily report for all the services completed on the previous day. I input the formula "CD-1D" in the Finishing Date field, however the reports that get generated daily pull the data using the Finishing Date as the date I scheduled the report minus 1 day. It doesn't recognize the current date as the date the report is being pulled. In fact, when I input the formula to schedule the report, the Finished Date gets automatically calculated at that time and all the subsequent reports that get generated use that same Finished Date. Please see my screenshots below for reference.

Inputting Formula:

Date Automatically Calculated:

Now when the system runs the daily report, it uses 7/13/2022 as the Finished Date each time rather than the "CD-1D" formula I used.

Please let me know if you know how to resolve this. Thank you!

All responses (8)

Answers (0)

Suggested answer

Inge M. Bruvik 1,029 Moderator on at

Like (0)

Report

RE: Generating Reports with Defined Filters

[quote user="SBG33"]
Hi MahGah,

I am not a developer but using Vaishnavi's instructions, is it possible to walk me through setting this up? Do I need to use Visual Studio Code for this?
[/quote]
If you are not a developer this is typically something you should reach out to your partner to get assistance with.
Suggested answer

MahGah 15,445 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Hi

I believe Vishnavi is more qualify than me for this task since she knows development and I do not. But you are almost correct on that.

See this video for quick start guide www.youtube.com/watch

Or these series of video

www.youtube.com/watch
SBG33 5 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Hi MahGah,

I am not a developer but using Vaishnavi's instructions, is it possible to walk me through setting this up? Do I need to use Visual Studio Code for this?
MahGah 15,445 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Also, please this idea and vote

experience.dynamics.com/.../
Suggested answer

Vaishnavi J 3,060 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Hi,

You need to create a report extension of Service Tasks and there you have to write the logic in OnOpen Page Trigger of the Request page. Since you would be scheduling a report.

reportextension 50118 ServiceTaskss extends "Service Tasks"

{

dataset

{

modify("Service Item Line")

{

RequestFilterFields = "Finishing Date";

}

}

requestpage

{

trigger OnOpenPage()

begin

"Service Item Line".SetFilter("Finishing Date", Format(CalcDate('CD-1D', Today)));

end;

}

}

If my answer was helpful to you, please verify it so that other users know it worked. Thank you very much
Suggested answer

MahGah 15,445 on at

Like (1)

Report

RE: Generating Reports with Defined Filters

Hi SBG33

The problem with date formula in report is they change to static date and then when you schedule them they stay the same. Microsoft had a concept of "Schedule" which works for report for static fields (such as customer No) but "schedule" is not useful for dynamics data such as date. Hence, you need a job queue codeunit that run that report and populate date based on formula. This requires development. Vaishnavi provided the code but if you are not developer and have no knowledge of it then I do not think this is possible out of the box.
SBG33 5 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Hi Vaishnavi,

Thank you for your response. I don't see a trigger option in the request page. Please see screenshot below:
Suggested answer

Vaishnavi J 3,060 on at

Like (0)

Report

RE: Generating Reports with Defined Filters

Hi SBG33

In your request page of the Service Report add a trigger OnOpenPage write the below logic

trigger OnOpenPage()

begin

Finishing Date:= CalcDate('CD-1D', Today);

end;

This will take the finishing date as previous day.

If my answer was helpful to you, please verify it so that other users know it worked. Thank you very much