XIR2 scheduling instances viewer

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

Feel free to give feedback and enhance.

Moderator edit: Version 2 has been added here. - MichaelWelter
Instances Status.xls (88.0 KB)


rachidb :morocco: (BOB member since 2006-07-06)

How to search for user in the distribution list.
BO reports - number of hits and scheduled infomation
Monitoring jobs in XI R2
Item not found in collection
XIR2 Scheduling Instances - anyone got it working in XI 3.0?
Report Inventory
find scheduled reports in query builder for today
who can tell me how to get webi report schedul's status
BO Enterprise "InfoView" job scheduler
List of Failed Reports in CMC
BO XI Job Monitor?
[XiR2] How may I know which reports are being refreshed now?
BI 4.1 : Macro for Scheduling Details
How to create a report of failed reports at a certain time
last run and schedule type - BOXI documentr
Report for administration purpose
Best Practice to manage LOTS of scheduled instances?
List of scheduled jobs XIR2
there's no error message for 'Scheduling Failed' events
Reccurring Report Schedules
How tp ge the failed report instance with a folder
Querybuilder: Find all “Scheduled objects”
Create a excel report on failed instances with error message
Help with Scheduled Report Administration - Best Practices?
Report failing but unable to determine which report.
Recipient list for all scheduled reports
Audit Actions
Query
How to build a report that contains all reports in BOE
SDK Query Tool?
See the list of Schedules in Business Objects
ways to report the successful/failed/running/pending reports
Listing default settings for reports.
Create a list of Reports and their schedule information
To View list of scheudled that are pending in the webI Serve
Monitoring BusinessObjects reports and triggers
Find pending jobs in CMC
Log of Scheduled Reports
report schedule inventory
Can i check all recurring instances at one shot?
how to get mail addresses from an instance?

Getting a “Can’t Find project or libary” on “date” which I belive is part of LocalTimeToUTC…

what library/references do you need for this function?


RedLineE90 (BOB member since 2008-06-12)

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.

Regards,
Rachid


rachidb :morocco: (BOB member since 2006-07-06)

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.

Subash


subash351 (BOB member since 2008-06-08)

Hi rachidb,

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 & Regards
Reema


reemagupta (BOB member since 2002-09-18)

Reema & Subash.
I am just back to the office this week. I will check both your requests and get back to you.

Rachid


rachidb :morocco: (BOB member since 2006-07-06)

this is Great. One question is avergage duration and delay in seconds or minutes


RDTs_ROCK (BOB member since 2004-06-04)

Hi

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?

Many Thanks
Charles


cdavies :uk: (BOB member since 2005-01-28)

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.

Rachid


rachidb :morocco: (BOB member since 2006-07-06)

Hi -

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 again,

Deb Franke


dfranke :us: (BOB member since 2002-08-28)

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.

Rachid


rachidb :morocco: (BOB member since 2006-07-06)

Thanks Rachid -

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?

Thanks

Deb


dfranke :us: (BOB member since 2002-08-28)

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.

Rachid


rachidb :morocco: (BOB member since 2006-07-06)

Thanks for the quick reply !
I’ll take a look.


dfranke :us: (BOB member since 2002-08-28)

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?

Regards Mike.


Mick-UK (BOB member since 2006-05-26)

Hi Rachidb -

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.

You really save me a lot of time !

Deb F.


dfranke :us: (BOB member since 2002-08-28)

You are welcome.
You may wanna share the new tool so someone else may benefit from it.


rachidb :morocco: (BOB member since 2006-07-06)

Hi -

Sure, I would be happy to share my two “spinoffs” of your original idea. Would be good if I can have you review what I did first.

Do you have a suggestion on how we could do that?

Deb


dfranke :us: (BOB member since 2002-08-28)

You can either post snippets of the code you modified or post the macro into BOB’s Uploads and we can all look at it.

Thanks,
Rachid


rachidb :morocco: (BOB member since 2006-07-06)

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.

Rachid


rachidb :morocco: (BOB member since 2006-07-06)