XIR2 scheduling instances viewer

system · November 12, 2009, 4:34pm

Hello,

Is there a way we can find / add which event is associated with the report and when the event came in, to this excellent spreadsheet ?

Thanks,
Hats

haritam (BOB member since 2006-07-25)

system · November 12, 2009, 5:02pm

I guess, I posted my question before looking at Version 3 of this spreadsheet. I am all set and this spreadsheet is very helpful.

Thanks,
Hats

haritam (BOB member since 2006-07-25)

system · November 13, 2009, 10:05pm

Hi Rachid,

Please find the attached document with the screen shot of the error message.

I am unable to attach the error message, as I don’t see any facility to upload the file.

Here is the exact error message:

Run-time error ‘-2147210697 (80042a37)’:

Item 1 was not found in the collection.

vsshyam (BOB member since 2003-11-11)

system · November 14, 2009, 2:25am

That error means the query probably did not return anything.
However, on a second glance at the code, there is an error in the query and for whatever reason it works.
The query should be using SI_NAME instead of S_NAME. Make the change and see if it resolves your error.
Thanks for catching this anyways.

Rachid

rachidb (BOB member since 2006-07-06)

system · November 16, 2009, 3:49pm

Hi Rachid,

Is there any chance I could incorporate some of your tool’s features into a Deskie report?

For example, I need to get it to display when a report is scheduled and who scheduled it. Our activity universe doesn’t have any dimensions in relation to this so I was just wondering if there was any way of getting that information without modifying the universe.

Thanks a lot in advance and really great work on the spreadsheet.

nnoori (BOB member since 2009-11-16)

system · November 17, 2009, 2:01am

Check the following topic:

Change the code in that topic with the code in this macro and that should do it.

Rachid

rachidb (BOB member since 2006-07-06)

system · November 17, 2009, 2:35pm

Thanks Rachid, but I can’t seem to be able to get it to work when I try to obtain the Schedule Times. I use the query

Set oInfoObjects = oInfoStore.Query("SELECT SI_SCHEDULEINFO.SI_SUBMITTER, SI_SCHEDULEINFO.SI_STARTTIME FROM CI_INFOOBJECTS ) and I’m not sure what to put in the WHERE section, I’ve tried different things but to no avail.

Could you lend some help please?

Many thanks,

Nawar

nnoori (BOB member since 2009-11-16)

system · November 17, 2009, 8:03pm

In your WHERE clause you can limit by whatever you want. Make sure to include SI_INSTANCE=1 so it only selects schedules.
A good choice also would be the time the reports were run:
SI_SCHEDULEINFO.SI_STARTTIME BETWEEN ‘" & start_date & "’ and ‘" & end_date & "’"

start_date and end_date would be in UTC timezone and in the format:
yyyy.mm.dd.hh:nn:ss

Hope this helps.
Rachid

rachidb (BOB member since 2006-07-06)

system · November 18, 2009, 10:51am

Really weird thing happened, I used this query in deskie as you suggested…

Set oInfoObjects = oInfoStore.Query(“SELECT SI_SCHEDULEINFO.SI_STARTTIME FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 1 AND SI_SCHEDULEINFO.SI_STARTTIME BETWEEN ‘2007.06.06.16:12:23’ and ‘2007.10.11.00:00:00’”)

…and it fetched me report names, 16 of them. However this query is absolutely fine in the query builder, it returns the data that I want it to return. Is that normal?

P.S. The macro from the topic that your first redirected me to works absolutely fine, so I take it it’s not a bug. Particularly the line
SELECT top 10000 SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=‘CrystalEnterprise.USER’ order by SI_NAME

Thanks a lot for the quick reply, btw

nnoori (BOB member since 2009-11-16)

system · November 18, 2009, 2:08pm

Not normal but nothing surprises me when developing using COM SDK. That is why, as I have heard, it will no longer be supported pretty soon.
I usually step through the code using the debugger to understand what gets returned by the query. This way you can tell what properties in the InfoObjects collection get fetched.

Rachid

rachidb (BOB member since 2006-07-06)

system · November 18, 2009, 3:50pm

Hi Rachid,

I made a change to SI_NAME and executed the macro and still receiving the error message. The wonder is: it works fine for some rows where it is managed destination. But for some rows, it will prompt the error message. The Inbox id’s are valid one only, but still it is prompting the error message.

So, I added a line “On Error GoTo 0” and manually move the cursor to this line and proceed

Please suggest me how to overcome this error message.

Thanks,
Shyam.

vsshyam (BOB member since 2003-11-11)

system · November 18, 2009, 4:42pm

I suggest that you put a breakpoint on that line then find out the ID, then run the query from Query Builder.
To bypass the error:
Put “On Error Resume Next” before that line and “On Error Goto 0” just following the line.

Rachid

rachidb (BOB member since 2006-07-06)

system · December 9, 2009, 3:39pm

Helpful tool. Nice work

D_Block85 (BOB member since 2009-12-07)

system · January 7, 2010, 5:47am

Hello,

First of all thansk a lot for wonderful tool. I was using the First release for getting the instance tool and it working fine but i am truing updated worksheet 2 and 3 its giving me nexttime run error. Could you please let me know the issue or upload the new one please…

sid786 (BOB member since 2008-10-06)

system · January 11, 2010, 12:08pm

Obviously this won’t be affecting many people yet but this won’t compile on Office 2010 (beta 2) on Vista (both 64-bit) as the following API calls are invalid:

Public Declare Function SystemTimeToFileTime Lib _
  "kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As FILETIME) As Long

Public Declare Function LocalFileTimeToFileTime Lib _
  "kernel32" (lpLocalFileTime As FILETIME, lpFileTime As FILETIME) As Long

Public Declare Function FileTimeToSystemTime Lib _
  "kernel32" (lpFileTime As FILETIME, lpSystemTime As SYSTEMTIME) As Long

If I can find the 64-bit variants then I’ll update my post…

rjp (BOB member since 2007-02-26)

system · January 27, 2010, 11:22am

why i doesnt see any “pending” instances?

I change in Instances_status_v1

FROM CI_INFOOBJECTS WHERE SI_SCHEDULE_STATUS = 9

and i see all pending reports

BO XI3.1 sp2

kkl (BOB member since 2009-07-16)

system · January 27, 2010, 9:28pm

The macro is only for scheduled jobs within the time frame you select. It will not show all pending/recurring jobs.

rachidb (BOB member since 2006-07-06)

system · February 5, 2010, 8:55pm

Do you know if there’s a way to bring in the Recurring instance to this?
Also is there a way to add the To email addresses?

joefonseca79 (BOB member since 2007-07-03)

system · February 5, 2010, 9:50pm

To addresses are in version 3 part of Destination Details column.
As for recurring instances the query inside the macro needs to be changed to include only the recurring status like this:
WHERE SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS=9

rachidb (BOB member since 2006-07-06)

system · February 25, 2010, 5:33am

Thanks rachid for this wonderful tool.I am curious is there a way that we can trap which report server actually processed the request forwarded by the job server.

dip_anj (BOB member since 2007-02-26)