8 Replies Latest reply on Dec 7, 2017 1:59 PM by Allen Morgan

    Date Range - Smart Reporting 9.1

    T. D.

      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.






        • 1. Re: Date Range - Smart Reporting 9.1
          Mohammad Rehman

          You should enter 0 in the first current date, see screen shot.




          • 2. Re: Date Range - Smart Reporting 9.1
            T. D.

            Thanks Mohammad!

            • 3. Re: Date Range - Smart Reporting 9.1
              Allen Morgan

              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


              • 4. Re: Date Range - Smart Reporting 9.1
                T. D.

                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.

                • 5. Re: Date Range - Smart Reporting 9.1
                  Allen Morgan

                  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,


                  Allen Morgan

                  • 6. Re: Date Range - Smart Reporting 9.1
                    T. D.

                    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.

                    • 7. Re: Date Range - Smart Reporting 9.1
                      Mohammad Rehman

                      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.



                      • 8. Re: Date Range - Smart Reporting 9.1
                        Allen Morgan

                        Hi Mohammad,


                        Thanks for replying.


                        We are running Remedy/ITSM 9.1.03 SP3


                        Thanks again - any thoughts would be appreciated.




                        Below is the Server info from Smart Reporting:


                        System Information
                        Application Version:            7.3          
                        Build:            20170619          
                        Java Version:1.8.0_73