Author: Rachidb
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.
Hi Rachidb,
It is really useful for us. It is greatful for us if you change vba code to select folders/category aslo as input parameter.
please help me.
It is a great tool. Is it possible to get details like what specific calendar the report is scheduled with and also if notification is set for the report or not?
Thanks for sharing the tool. I’ve amended my copy so that the user can specify the submitter of the report rather than download the full list of scheduled reports.
However is it possible to add the prompt values of the reports that are set at schedule time from the infoobjects file?
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.
Subash:
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.
Reema:
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.
RDTs_ROCK:
Duration is in minutes.
Charles:
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.
I want to also say thank you for this handy tool !
I have a question about the “Pending” status instances.
I read the macro but was not able to figure it out.
How does the date range effect which “Pending” status instances extracted?
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.
Again, this is a great tool. Thanks for sharing it with us. This has so much more functionality than the BO supplied Instance Manager !
I’m still a little confused concerning the “pending” reports.
I used the tool again today.
I used a date range from 26 Sep 2008 - 31 Dec 2008.
When I looked at the “pending” all but one of my pending reports is there. The missing report is supposed to run during my date range (Oct 5)
If I change the date range to 13 Sep 2008 - 31 Dec 2008
Now all of the pending reports are showing.
Any idea why my report scheduled to run on 5 Oct 2008 wouldn’t appear when I used my first date range?
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?
Again I want to say thank you for this tool. I learned a lot by studying it !
I actually have now created two separate tools based on what you did. One that only gets the running history. The other that gets all pending / recurring reports.
Tip:
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.
(BOB member since 2006-07-06)
(BOB member since 2005-01-28)
(BOB member since 2002-08-28)