1 2 Previous Next 22 Replies Latest reply on Mar 7, 2013 11:54 AM by Christian

    Run automatic reports each month

    Christian

      Title

      Run automatic reports each month

      Post

           Hi Everyone,

           I am attempting to create a report which lists all records that were "logged out" in the past month.  The logged out records would be tracked by a "logged out date" field.

           It would be even better if I could somehow run this report automatically, at the end of each month, and e-mail it to specific people.

           Is this possible to do?  If so, how would I get this started?  I have a script set up so far, but I'm really not sure whether I am even close to getting this like it should be...  indecision

      Screen_Shot_2013-02-23_at_5.23.39_PM.png

        • 1. Re: Run automatic reports each month
          philmodjunk

               Constrain Found Set takes the records in your current found set and filters out all that don't match your specified criteria. Thus, Perform Find makes more sense.

               Here's a script to find all records dated with dates in the current month. You'd run this script after close of business on the last day of the month. A slight modification would let you find records from the previous month:

               Enter Find Mode [] --> clear the pause check box
               Set Field [Student Services Mail Log::LogOut Date ; //Put expression shown below here ]
               Set Error Capture [on]
               Perform Find []

               Here's the expression to use to make a date range find for the current month:

               Let ( [ m = Month ( get ( CurrentDate ) ;
                          y = Year ( Get ( CurrentDate ) ] ;
                         Date ( m  ; 1 ; y ) & "..." & Date ( M + 1 ; 0 ; y )
                      )

               And for the previous month:

               Let ( [ m = Month ( get ( CurrentDate ) - 1 ;
                          y = Year ( Get ( CurrentDate ) ] ;
                         Date ( m  ; 1 ; y ) & "..." & Date ( M + 1 ; 0 ; y )
                      )

               For more examples of using scripts to perform finds see this thread: Scripted Find Examples

          • 2. Re: Run automatic reports each month
            Christian

                 Thank you for your help, Phil!  I'm running across an error with this script though...  Any thoughts?  I've attached a screen shot.

                  

                 Thanks!

            • 3. Re: Run automatic reports each month
              philmodjunk

                   I left out some )

                   Let ( [ m = Month ( get ( CurrentDate ) ) ;
                              y = Year ( Get ( CurrentDate ) ) ] ;
                             Date ( m  ; 1 ; y ) & "..." & Date ( M + 1 ; 0 ; y )
                          )

                   And for the previous month:

                   Let ( [ m = Month ( get ( CurrentDate ) - 1 ) ;
                              y = Year ( Get ( CurrentDate ) ) ] ;
                             Date ( m  ; 1 ; y ) & "..." & Date ( M + 1 ; 0 ; y )

              • 4. Re: Run automatic reports each month
                Christian

                     That worked perfectly!  Thank you!

                     Now, if I wanted to sort by Department (DO Other Department), and THEN logout date, would I include a "Sort Record" or Sort by field" in the script?

                • 5. Re: Run automatic reports each month
                  philmodjunk

                       After the Perform Find step, you can include a sort records step to sort the records into the desired order.

                  • 6. Re: Run automatic reports each month
                    Christian

                         So, I have everything working exactly like I want (THANK YOU, PHIL!), but after presenting it to the staff, of course, they want more...  Now, in addition to showing the report sorted by department, I need to be able to run separate reports by department.  The departments are part of a value list "Other Departments".

                         Would I need to creat a separate script for each department in order to accomplish this?

                    • 7. Re: Run automatic reports each month
                      philmodjunk

                           Take a look at the example scripts in that thread I suggested in my very first post here.

                           You can set up a single script where the user selects a department in a global field and then the script incorporates that selection as criteria used in the scripted find. That way you can use one script, but can produce a report for any single department.

                           It's also possible to design a summary report where records are grouped by department using a sub summary layout part "when sorted by" the department field. If you set up the page break options correctly, each department can be printed on a separate page or at least start on a new page. This would enable you to generate a single report, but then deliver the separate portions of that report to each department...

                      • 8. Re: Run automatic reports each month
                        Christian

                             Again, you've made my life easy!  I've already set up the report to be grouped by department using the sub-summary layout.  So, I just designated it to break after each occurence, and that works great for us!

                             Thanks again for all your help!

                        • 9. Re: Run automatic reports each month
                          Christian

                               I honestly thought I would be finished with this thread.  I now have an issue when a user is trying to perform a find for a range other than the previous month.  I opened up the "login" field and entered a date range, but I get the error "No records match this find criteria." (screen shot attached)

                               I know that there are records available for this month...  Any ideas as to why I am getting an error?

                          • 10. Re: Run automatic reports each month
                            philmodjunk

                                 What exactly did you enter into the LogIn Date field? (I can only see part of the data...)

                            • 11. Re: Run automatic reports each month
                              Christian

                                   I'm sorry.  I entered: 2/5/2013...2/26/2013

                                    

                              • 12. Re: Run automatic reports each month
                                philmodjunk

                                     That is a perfectly valid find criteria for finding all records dated from 2/5 to 2/26.

                                     This suggests that some other detail to your find request or your criteria was used on the wrong date field.

                                     Also, make sure that this field is really a field of type date, not text.

                                     None of which I can see from here.

                                • 13. Re: Run automatic reports each month
                                  Christian

                                       Aaahhh...  It was definitely a text field instead of a date field.  What a dumb oversight on my part.

                                       Thank you again!

                                  • 14. Re: Run automatic reports each month
                                    Christian

                                         I hate to come back to this post, but the script for the previous month is still pulling the current month's information.  I've attached a screen shot of the script as it is input.

                                         Any suggestions?

                                    1 2 Previous Next