Author notes: This macro generates information about instances finished/running/pending. It accepts a date range. If start date is omitted it shows instances statuses for a given day starting 12AM until time of run. End date is also option and will default to current date/time.
Rows returned is limited to 64000 rows. But please be careful not to run for a big date range as it may overload your CMS.
Platform: Excel 2003
Version: Initial release 1.0
There is no additional library needed for that function.
All you need checked in Tools->References…:
Visual Basic For Applications
Microsoft Excel 11.0 Object Library
Crystal Enterprise InfoStore Library 11.5
Crystal Enterprise Framework Library 11.5
Microsoft Forms 2.0 Object Library
If you are missing Crystal Enterprise libraries then you need to install client tools of BOXIR2 SP2.
I like to answer all questions but before that I like to thank each one for their feedback and also mention that this tool is meant to be very simple to run and give administrators and IT support an easy way to monitor scheduled reports.
Yes it is possible to add prompt for folder/category. For folder you have to first get the ID or CUID and add one of the following to the infostore query: SI_PARENT_FOLDER= or SI_PARENT_FOLDER_CUID=
For Categories, it is more complicated as a report may belong to multiple categories. SI_CORPORATE_CATEGORIES and SI_PERSONAL_CATEGORIES properties of the infoobject can give you that information.
Notifications property of SchedulingInfo can tell you if notification is set or no.
As for calendar, CalendarTemplate property of SchedulingInfo holds the id of the calendar used. You will have to query infostore again to find out name and any other info about that.
Duration is in minutes.
Prompts are part of ProcessingInfo Objects. However, since a report can have multiple prompts which can hold multiple values, it will be very difficult to display those on a spreadsheet.
Thanks for the feedback.
The date range is used to select only the reports that are scheduled to run during that time frame. If a job has a pending status then it has not started yet for whatever reason.
The query used by the tool uses field SI_UPDATE_TS to speed up the performance. That field shows when the record was updated.
In your case, the missing record may have been updated between Sep 13 and Sep 26, that is why it does not show up.
This tool is not intended to show what will run in the future, rather it shows what is supposed to be running/completed within the time range supplied.
Great piece of kit - many thanks for sharing. Instantly shows me any problems. Also displays future pending jobs nicely so I can keep a watch against bottlenecks etc. Would it be feasible to build the macro into deski?
If you don’t want to enter server name and password every time then you can default both in same way Username is defaulted to Administrator and Authentication is defaulted to Enterprise.
Go to Tools->Macro->Visual Basic Editor
Double Click logonForm to open it.
Right click Server TextBox and select Properties
Scroll down to Text Property and enter the default value you want.
Do the same for Password TextBox. Save and close Visual Basic Editor then refresh. You should see the default values now.