I'm creating a report that I want to run automatically.
I have a filter of Scheduled Start Date BETWEEN. I want the system to run the report from the current date plus 7 days.
I tried the Select Filter Value, but it insists on a current date. I selected current date (see below), but it says the Start Date is incorrectly filled out.
Suggestions?
THANKS!!!!!!!!!!!!!!!!!!
Dec 7, 2017 11:14 AM
You should enter 0 in the first current date, see screen shot.
Thanks
Mohammad
Hi Mohammad,
I saw this thread and was hoping maybe you can also help me.
I also need to do dynamic date comparisons (between) and am struggling with doing it using the date filters and specific time of day.
I have gone down the road at just having the dates I want to compare in Remedy then just pull them into my report filters but it's not optimal.
Here is what I am looking to do:
I need to compare if a date (aka Change Request Scheduled Start Date) is between Today at 4 PM and Tomorrow at 3:59 PM
It's a daily 24 hour change report but using specific times.
Any help you could provide would be greatly appreciated!
Kind Regards,
Allen Morgan
NASDAQ
Allen, you could do the same thing I am doing with my change reports. Run the report using Broadcast and run it at 4pm.
In your report have the Scheduled Start Date to be Current Date (0) Days and the next Current Date + 24 hours instead of Days. I don't believe you can do 23 hours and 59 minutes.
Hi T.D.,
Thank you for your reply.
I painted was only one of a number of scenarios.
And this report needs to .be dynamic since it will be embedded on web page so anyone can see the current state of all changes between those time periods based on the day they view the report.
The state of changes will move during the day which is why it's needs to be dynamic times associated.
I have been able to do it in Remedy and SQL Server.
On Server in Europe (-1 of UTC) to get today at 4:00 PM EDT
DATEDIFF(s,'19700101',DATEADD(hour,(21),DATEADD(day, DATEDIFF(day,0, GETDATE()), 0)))
Return Epoch equivalent of 2017-12-07 22:00:00.000
On Server in Europe (-1 of UTC) to get tomorrow at 3:59 PM EDT
DATEDIFF(s,'19700101',DATEADD(second,-1,DATEADD(hour,45,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))))
Return epoch equivalent of: 2017-12-08 21:59:59.000
Form in Remedy holds the changeable values and a filter runs on GET that sets them in the form.
Then I just have an outer Join to this form.
As I noted - I don't think this is optimal (calling SQL - I played with Remedy date time functions and felt SQL was easier)
Smart Reporting (Yellowfin) is powerful so I think it would be better long term to be able to do it there.
I also believe we could add a custom function to Smart Reporting's customfunctions.xml (BMC has added some of their own ARDBC functions in this file).
My issue there is I have no documentation on the ARJDBC api and I could not find any when searching.
Probably with a ARDDBC reference - a custom function (E.g. TodayAt359, ARJDBC as example) could be added.
I was just hoping that maybe Mohammad, you or someone else might know a way to do it.
Again thanks,
Regards,
Allen Morgan
I would like to help you but I have not played with custom functions as of yet.
My change report needs to run every Friday at 11:00 am - this I did in Broadcast. In the report I set the scheduled start date to be ZERO days and the next date PLUS 7 days - so this runs the report from Friday to Friday.
Sorry I could not be of more help.
Dec 7, 2017 1:53 PM
Hi Allen,
Would you please share the smart report version? I have not done it but will try to test and share my results with you.
Regards
Mohammad
Hi Mohammad,
Thanks for replying.
We are running Remedy/ITSM 9.1.03 SP3
Thanks again - any thoughts would be appreciated.
Regards,
Allen
Below is the Server info from Smart Reporting:
| System Information | |
| Application Version: | 7.3 |
| Build: | 20170619 |
| Java Version: | 1.8.0_73 |
| |