Does anyone know if this is possible?

system · July 3, 2007, 4:22am

Is it possible to create a batch program that runs on its own and checks the scheduled reports on the BCA server against Corporate Documents to see if the report has been modified and thus needs to be rescheduled.

magic (BOB member since 2007-06-26)

system · July 6, 2007, 2:20pm

sounds totally possible (not sure how easy it would be to implement though).
I’m going to assume that you are on 6.x since you are referring to the BCA?

jresendez (BOB member since 2004-05-03)

system · July 6, 2007, 4:19pm

Thanks for the response. Yeah, I’m actually using BO 6.5. Do you have any ideas as to how I may be able to write such a script. Thanks agains… I really appreciate your help.

magic (BOB member since 2007-06-26)

system · July 6, 2007, 5:45pm

I don’t have an ‘exact’ sample of how to do it…but i can provide the sample VBA script that i had for 6.5 that i used to create a report that displayed the reports that were scheduled to process on the BCA on the next day.(i.e. my report would run monday afternoon, letting the BusObj administrators know which reports would run on tuesday morning and what time those reports were scheduled to start).

Its not an exact solution, but i think it will provide an idea for what may be involved to create what you are looking for. There are several functions that i wrote to decrypt the timestamps that will save you lots of time if you need to know dates for scheduled reports (as they are stored in a UTC or julian time format in the repository?). There should be documentation on the BO support site that should outline the repository architecture by table that you will need to create your solution.

hope this serves as a good starting point.
bca_report_sample.txt (18.0 KB)

jresendez (BOB member since 2004-05-03)

system · July 6, 2007, 7:10pm

Thank you so much. I really do appreciate this.

magic (BOB member since 2007-06-26)

system · July 6, 2007, 7:13pm

no problem!

Hope it helps!

jresendez (BOB member since 2004-05-03)

system · July 6, 2007, 8:03pm

Sorry to bother you again. I was just wondering why your solution is so complex? You seem to have a lot more knowledge of visual basic then most of my compatriots. However, do you think there is an easier solution to checking scheduled BCA reports against the Corp Documents to see if the reports have been modified.

Basically,

I just need to check whether the scheduled reports has been modified recently
and I mean to accomplish this by checking to see if the publish date of the report in Corp Docs is greater than the submit date of the report on the BCA console.
I was hoping to write a script that runs daily that checks these parameters then automatically schedules affected reports.

Thanks again… I really do appreciate the time you’ve put in to helping me.

magic (BOB member since 2007-06-26)

system · July 6, 2007, 8:36pm

Hi Magic…
The solution is more complex due to the fact that business objects doesn’t make it easy to pull information from the repository directly (as you may have noticed), in addition to our business requirement at the time called for a lot of validation and again, we have to jump through a lot of hoops to get basic information (such as start dates, end dates…etc) from the repository directly.

What i can recommend is that you try to track down the 6.x repository documentation that is buried somewhere on the Business Objects support site; once you find it, you can see how you can map from reports in the repository to the scheduled reports table (i believe the table that holds the scheduled reports is called pending_jobs or ds_pending_jobs)

what you are looking for is pretty straightforward, however i don’t recall the repository structure well enough to point you towards an exact solution off the top of my head. If you can track down the doc, post it, and i will try to map it out for you.

thanks!
j

jresendez (BOB member since 2004-05-03)

system · July 6, 2007, 9:21pm

Thank you again.

I was looking through the code example you provided and I have one question:

What is the work flow relating to the way the code is structured (i.e. why do you load data from the data provider of a report first?; why do you use sql to query the database?)

Thanks again. You’ve helped me more than I could possible have ever asked.

magic (BOB member since 2007-06-26)

system · July 9, 2007, 12:55pm

The work flow is fairly basic… (you need to keep in mind that this code existed in a .rep report initially)

Sub LoadDataProvider() executes first because this is what is executed when you would click refresh on your report. I created this report using a VBA procedure as a dataprovider.

I used SQL to query the database because i needed specific information from the repository that was not readily available via pre-built universe(s) in business objects. Because I queried directly off of the repository, several of the values i needed came back in a ‘raw’ format (meaning i had to refer back to the documentation to decrypt several lookup values as well as write a few trick functions to convert the timestamps to actual date/time (as the default time is stored in seconds).

I found a link to the repository documentation here on BOB.

Hope this helps get you started on your solution (good luck!)

jresendez (BOB member since 2004-05-03)

system · July 9, 2007, 4:24pm

Thanks you so much. You have been an amazing help. I don’t think I could have ever helped out a stranger as much as you have.

magic (BOB member since 2007-06-26)

system · July 9, 2007, 4:27pm

not a problem at all!
glad to help…and it was no trouble, as most of my assistance came from logic and situations that i already had run into in the past! (why re-invent the wheel?)

jresendez (BOB member since 2004-05-03)

system · July 9, 2007, 9:03pm

One final question. Do you know where I can get SDK documentation or case examples… without purchasing a license?

Thanks again.

magic (BOB member since 2007-06-26)

system · July 9, 2007, 9:12pm

Jorge:

You posted some code a while back running XIR2 instances in a VB program (not VBA). I’m trying this as well and am running across a problem with the Quit method hanging up. I recall you using some sort of Process variable and then I suspect issuing a kill command on that process number.

Can you enlighten me. BO claims there is no problem with their Quit method…

dirmiger (BOB member since 2002-08-28)

system · July 9, 2007, 9:20pm

Sorry to keep bothering you. However, I was looking through the code and it’s little hard to follow without the report it’s creating or running against.

Do you happen to still have the report the code generates (if is infact an .rea file)? And, if possible, could you send that to me?

Thanks again.

magic (BOB member since 2007-06-26)

system · July 10, 2007, 6:55am

Dirmiger, read this link for resolving error when Application.quit is called in your code

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=8708360&sliceId=&dialogID=3734349&stateId=1%200%203732545

Cheers

haider (BOB member since 2005-07-18)

system · July 10, 2007, 1:23pm

Dirmiger,
There was an initial issue w/the Application.Quit command in SP1 (which they claimed to have fixed in the later MHF and service packs). What i did in the interim is simply capture the process ID and issue a kill command, which works perfectly. I initially wanted to get rid of this after we applied the MHF1 (since they mentioned it should have been fixed), however i figured since it was working fine ‘as-is’; why mess with it.

Here is my Kill Process function (you simply need to pass the ProcessID to it)

First you initialize the process id after you instantiate the BusObj Application object:


 iBoProcessID = BoApp.ProcessId

Then when you have completed your processing, simply call the killProcess function:


 Call KillProcess(iBoProcessID)

Below is the killprocess function:


'---------------------------------------------------------------------------------------
' Procedure             : KillProcess
' Date Created          : 11/6/2006 15:48
' Author                   : Jorge Resendez /Crate &amp; Barrel 
'---------------------------------------------------------------------------------------
'
Public Function KillProcess(target_process_id)
On Error Resume Next
    strComputer = "."

    Set objShell = WScript.CreateObject("WScript.Shell")

    Set objWMIService = GetObject("winmgmts:" _
        &amp; "{impersonationLevel=impersonate}!\\" &amp; strComputer &amp; "\root\cimv2")
    Set colProcessList = objWMIService.ExecQuery _
        ("Select * from Win32_Process Where ProcessID = " &amp; target_process_id)

    For Each objprocess In colProcessList
    WriteLog "BEGIN -- Terminating BusObj ProcessID Thread = " &amp; target_process_id
    If objprocess.Name <> "" Then
        objprocess.Terminate
    End If
    WriteLog "END -- Terminating BusObj ProcessID Thread = " &amp; target_process_id
    Next

    Set colProcessList = Nothing
    Set objWMIService = Nothing
    Set objShell = Nothing
End Function

[/code]

jresendez (BOB member since 2004-05-03)

system · July 10, 2007, 1:36pm

Magic,
Here is the sample report…
I would guess that you would only need to change the connection string to get it to work for you.

Regarding the developer SDK documentation: I had a URL for it, however it no longer works…you’ll have to dig through the BO site to see if it’s still available. Try http://diamond.businessobjects.com/

good luck!

Jorge
Daily Reports_Sample.rep (133.0 KB)

jresendez (BOB member since 2004-05-03)

system · July 10, 2007, 4:40pm

You are life saver. Thank you so much again.

magic (BOB member since 2007-06-26)

system · July 16, 2007, 4:43pm

One final question… why are you doing this? Why is there a need to convert to Binary?

Function ConvertLongDec2Binary(strNumber As String)

Dim text1$: text1 = strNumber
Dim NumberHi&, NumberMd&, NumberLo&, temp&, bin$
Dim xText$: xText = Right$(“000000000000000000000” & text1, 21)
NumberHi = Left$(xText, 7)
NumberMd = Mid$(xText, 8, 7)
NumberLo = Right$(xText, 7)
bin = “”
Do
temp = NumberLo Mod 2
bin = CStr(temp) & bin
If (NumberHi Mod 2) = 1 Then NumberMd = NumberMd + 10000000
NumberHi = NumberHi \ 2
If (NumberMd Mod 2) = 1 Then NumberLo = NumberLo + 10000000
NumberMd = NumberMd \ 2
NumberLo = NumberLo \ 2
Loop Until NumberHi + NumberMd + NumberLo = 0
'MsgBox “<” & bin & “>”
ConvertLongDec2Binary = bin

magic (BOB member since 2007-06-26)