cancel
Showing results for 
Search instead for 
Did you mean: 

how to seduling BO report

rameshparchuri5
Participant
0 Kudos

Hi need help on this

in BO, user request the schedule every day. specific time like 3 AM ..

but in the report, it has mandatory a date prompt . how to schedule the report

how to set up any logic.

exampl: schedule via mail like ( 01-11-2023 3 AM to 02-11-2023 3 AM)

Accepted Solutions (0)

Answers (2)

Answers (2)

rameshparchuri5
Participant
0 Kudos

thanks for the help .. I hope you are logic will work

on small question. in IDT how to create the current Date object. i checked in IDT but did not find any function

Thanks

nscheaffer
Active Contributor
0 Kudos

There are number of different ways to do this. Since it will not work to hard-code dates in your schedule one option is to create a secondary query that will return the dates you want and use them to filter your primary query. This answer of mine to another similar questions as well as this webinar I gave awhile back goes over these options in great detail.

I'll summarize what I think is the most straightforward option and that is to create that secondary query as a free-hand SQL query. Using SQL Server that would look something like this...

DECLARE
    @DaysOffset    INT
  , @ReferenceDate DATE;

SET @DaysOffset = -1;
SET @ReferenceDate = DATEADD (DAY, @DaysOffset, GETDATE ());

SELECT
    @DaysOffset                                                                               AS [DaysOffset]
  , @ReferenceDate                                                                            AS [ReferenceDate]
  , CONVERT (DATETIME, FORMAT (DATEADD (DAY, -1, @ReferenceDate), 'MM-dd-yyyy') + ' 3:00 am') AS [StartDateTime]
  , CONVERT (DATETIME, FORMAT (@ReferenceDate, 'MM-dd-yyyy') + ' 3:00 am')                    AS [EndDateTime];

This query will prompt you for how many days you want to go back for your Reference Date for your End Date. You could have another prompt for your Start Date, but if it will always be just one day previous you can do that date math in your query to back up another day.

That query will yield this...

You can then use StartDateTime and EndDateTime as inputs to your primary pre-existing query...

If your backend database is something other than SQL Server you may need to adjust syntax accordingly.

Will that work for you?