Does anyone know if this is possible?

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)

system · July 16, 2007, 4:45pm

I’m really sorry. What is the purpose of this function?

Function TwosComp(sBin As String) As String

Dim i As Long
Dim lNum As Long
Dim sTemp As String

For i = 1 To Len(sBin)
    sTemp = sTemp &amp; IIf(Mid(sBin, i, 1) = 0, "1", "0")
Next i

For i = Len(sTemp) To 1 Step -1
    lNum = lNum + ((2 ^ (i - 1)) * Val(Mid$(sTemp, Len(sTemp) - i + 1, 1)))
Next i

TwosComp = Dec2Bin2(lNum + 1, Len(sBin))

End Function

magic (BOB member since 2007-06-26)

system · July 16, 2007, 4:48pm

And finally… what does the “IntCurrentPos” variable do? And why is all this necessary. I know… I’m getting really annoying. It’s just that we’re trying to figure out what the code does and we are having trouble. Thank you again.

Function GetDaily_WeeklySchedule(IntNumber As Long) As String
strSchedule = “”

intBin = Dec2Bin2(IntNumber)

intBin = CStr(intBin)

intBin = Left(intBin, Len(intBin) - 3)

intBin = RevString(CStr(intBin))

'daily processing

intDayString = Mid(intBin, 1, 7)

For i = 1 To Len(intDayString)

IntCurrentPos = Mid(intDayString, i, 1)

Select Case i
Case 1
If IntCurrentPos = “1” Then strDays = strDays & “Monday,”
Case 2
If IntCurrentPos = “1” Then strDays = strDays & “Tuesday,”
Case 3
If IntCurrentPos = “1” Then strDays = strDays & “Wednesday,”
Case 4
If IntCurrentPos = “1” Then strDays = strDays & “Thursday,”
Case 5
If IntCurrentPos = “1” Then strDays = strDays & “Friday,”
Case 6
If IntCurrentPos = “1” Then strDays = strDays & “Saturday,”
Case 7
If IntCurrentPos = “1” Then strDays = strDays & “Sunday,”

End Select
Next
strDays = Left(strDays, Len(strDays) - 1)

intBin = Trim(intBin)

intWeeks = Mid(intBin, 19, Len(intBin) - 18)
IntTotalWeeks = 0 'we have at least 1 week

For x = 1 To Len(intWeeks)
IntCurrentWeekPos = Mid(intWeeks, x, 1)

If IntCurrentWeekPos = 1 Then
IntTotalWeeks = IntTotalWeeks + 1
Exit For
Else
IntTotalWeeks = IntTotalWeeks + 1
End If

Next x

If IntTotalWeeks > 1 Then
strWeeks = “Weeks”
Else
strWeeks = “Week”
End If

strSchedule = “” & strDays & " Every " & IntTotalWeeks & " " & strWeeks
GetDaily_WeeklySchedule = strSchedule

magic (BOB member since 2007-06-26)

system · July 16, 2007, 7:55pm

Hi Magic…
Sorry for the delay…
Let me try to break it down for you

(bear with me as i try to recall why we built all of this — been a long time since i have had to rifle through the code)

ConvertLongDec2Binary
This function takes a long decimal and returns a binary string i.e(0010100). I believe i did this to format the value of DAY_TIMING such that I could parse through it and decipher some of the values. BO stores the day_timing(or days that a report executes) in a string of 1s and 0s (each position of the string being either 1 or 0 depending on whether a report executed on a specific day).
That said, my function parses through the binary string, reading each position of the string to see if the value is either 1 or 0, if it is, then going back to the documentation I was able to determine if a report ran on a specific day. (i.e. if i would find a 1 in the first position; this would mean a report ran on Mondays…if there was a 1 in the second position, then Tuesdays…etc).
The repository documentation breaks out how these strings are defined an what each position represents.

GetDaily_WeeklySchedule
intCurrentPos is merely the current item in the binary string if i remember correctly.
For example if i had a string [001000101110].
I would loop through the string, processing each of the values.
intCurrentPos would be the current value that the loop is processing (e.g. using the example above it would start with 0, then next would be 0 then 1…and so forth.

TwosComp This is used as part of the binary conversion… (honestly not sure what the specific purpose was; one of our developers found a working function on the web to convert to binary, so we pretty much used it “as-is”, since it worked fine in our situation).

jresendez (BOB member since 2004-05-03)

system · July 16, 2007, 7:58pm

Thank you so much. You are a much better person than I am.

magic (BOB member since 2007-06-26)