BOXI Inventorying Report Objects 1

[cesql]

Hi
I want to get a list of report objects from BOXI cms_infoobjects table. In CE10 I just used SQL to query CMS_InfoObjects table. But can't access this table in BOXI. Looks like this can only be done with an SDK which I've not used before. Can someone give me an idea how I can do this?

Any Help is appreciated.

 

[hilfy]

Which version of BO XI are you using? If you're using R2 or below, there is a Query Builder in the Administration Tools. You can then run a query like this to get a list of all of the Crystal reports in the system:

SELECT SI_NAME, SI_ID FROM CI_INFOOBJECTS WHERE SI_KIND = 'CrystalReport'

There is other information you can get about reports as well. I don't know how long this link will be available as all of the businessobjects.com web links are being moved under sap.com, but look here:

http://devlibrary.businessobjects.com/BusinessObjectsXIR2/en/devsuite.htm.

Click on ".NET developer guide and API reference", then on "SDK Fundamentals", and then on "How do I use the query language..."

For an example of the .NET SDK in use, I have a basic tutorial program on my website here

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[cesql]

Thanks. I am using R2. I've used the query language before however it is not usefull for pulling information on all reports at once. I will check out your tutorial since it looks like I need sdk to access more specific info. Thank you again

 

[Viscontenator]

A "make do" way that I have used here is:

0) Open an empty spreadsheet (Excel here). Open the BOE XI administrator page and log in.

1) From the CMC (management console), change your preferences temporarilly to something large. I set mine at 500, but I don't remember wx this is limited internally.

2) Go into the object listing (list all reports). If you have more reports than you can show on a page, then you will have to do this in steps.

3) Click somewhere in the white-space of the listing frame. You don't want to click a link, just make sure that the browser focus is in that frame.

4) "Select" the contents. I wind up doing a left-click-hold just to the left of the word "Type" column header, then I "drag-scroll" to the bottom right, just past the last instance for the last report on the page.

5) "Copy" -- CTRL-C for me.

6) Paste into an empty Excel spreadsheet. This can take a while, depending upon your network and listing size.

7) After the listing has been pasted, click outside the pasted area, select all (CTRL-A), Copy (CTRL-C). Then, go a new worksheet (same workbook), and do a PASTE SPECIAL. The Special is important. You want to paste JUST the values into the new worksheet. You now (hopefully) have your reports listing in TEXT in a new worksheet, with separate columns for "Object Title", "Folder", "Description", and "Instances". "Type" and the checkbox are useless columns.

8) If you have more than one page of listings in the CMC, then repeat this onto new worksheets each time. After you have generated all of your text pages, you can delete the old worksheets containing the pasted material (it has links, and it is not useful). Contatenate all of your listings into one major worksheet, and you can sort/search on either Object or Folder.

It looks involved, but once you do it once or twice to get the hang of it, it's very fast.

 

[cesql]

Thank you. Because I need to pull report info such as si_mail_subj, si_mail_addresses, si_lastrun, si_description, si_name, si_export_format, etc on over 800 reports the Copy/Paste route may not be the solution. Some of the info I need access to is in the ci_systemobjects & possibly the ci_appobjects (as well as ci_infoobjects) tables.

 

[rheard]

OK - ready for a really convoluted solution? I found something similar to this a couple of years ago here on tektips, cant find it again so cant give a credit (sorry!). I've modified it slightly for my needs ie Report Name, Relevant Dates, Email Recipients... once you understand how it works you could modify it for your own needs.
Use this as the SQL in Query Builder - run it and save the output as export.html

SELECT
SI_NAME,
SI_PROCESSINFO.SI_FORMAT_INFO,
SI_ID,
SI_OWNER,
SI_PARENT_FOLDER,
SI_CREATION_TIME,
SI_DATE,
SI_DESCRIPTION,
SI_UPDATE_TS,
SI_DEST_SCHEDULEOPTIONS,
SI_LAST_RUN_TIME,
SI_NEXTRUNTIME,
SI_PATH,
SI_FILES,
SI_FORMAT_INFO,
SI_SCHEDULE_TYPE,
SI_SCHEDULE_INTERVAL_MINUTES,
SI_SCHEDULE_INTERVAL_HOURS,
SI_SCHEDULE_INTERVAL_MONTHS,
SI_TYPE,
SI_ENDTIME,
SI_PROGID_SCHEDULE,
SI_RETRIES_ALLOWED,
SI_RETRY_INTERVAL,
SI_RUN_ON_TEMPLATE,
SI_STARTTIME,
SI_DESTINATION,
SI_SCHEDULEINFO
FROM CI_INFOOBJECTS WHERE SI_PROGID like 'CrystalEnterprise.r%' and SI_LAST_RUN_TIME is not null
ORDER BY SI_NAME

Open the export.html folder into Excel - it should open with a worksheet called 'Export' (if a dialog box appears complaining about missig css file, click OK)

Create 2 new macros, AA and BB using this code:

Sub AA()
'
' AA Macro
' Deletes 1st 10 rows
' Creates new Sheet
' Clears cell borders
' Copies data to new Sheet


' Delete Rows, create new Sheet

    Rows("1:10").Select
    Selection.Delete Shift:=xlUp
    Sheets.Add
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Sheet2"
    Sheets("export").Select
    Sheets("export").Move Before:=Sheets(1)

Dim ws As Worksheet
Set ws = ActiveSheet

' Clear exiting borders
Cells.Borders.LineStyle = xlLineStyleNone


' Copy data to new Sheet

Dim nFound As Boolean
counter = 1: nFound = False:
idnum = "": parentfolder = "": lastrun = "": rptname = "": rptformat = ""
For a = 1 To Range("E65535").End(xlUp).Row
Cells(a, 1).Activate

If ActiveCell.Value = "SI_ID" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 1): idnum = Cells(a, 2)
End If

If ActiveCell.Value = "SI_NAME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 2): rptname = Cells(a, 2)
End If

If ActiveCell.Value = "SI_OWNER" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 3)
End If

If ActiveCell.Value = "SI_PARENT_FOLDER" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 4): parentfolder = Cells(a, 2)
End If

If ActiveCell.Value = "SI_CREATION_TIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 5)
End If

If ActiveCell.Value = "SI_UPDATE_TS" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 6)
End If

If ActiveCell.Value = "SI_STARTTIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 7)
End If

If ActiveCell.Value = "SI_ENDTIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 8)
End If

If ActiveCell.Value = "SI_LAST_RUN_TIME" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 9): lastrun = Cells(a, 2)
End If

If ActiveCell.Value = "SI_DESCRIPTION" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 10)
End If

If InStr(1, Cells(a, 3).Value, "crx") <> 0 And nFound = True Then
Cells(a, 3).Copy Sheets(2).Cells(counter, 11)
End If

If ActiveCell.Value = "SI_PROGID_SCHEDULE" Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 12): rptformat = Cells(a, 2)
End If

'If ActiveCell.Value = "SI_PERSONAL_CATEGORIES" And Cells(a, 2) <> "SI_TOTAL" Then
'nFound = True: Cells(a, 3).Copy Sheets(2).Cells(counter, 13):
'End If

'=======================================
'add each email address on separate line
'=======================================
If InStr(1, Cells(a, 5).Value, "@") <> 0 And nFound = True Then
Cells(a, 5).Copy Sheets(2).Cells(counter, 13):
'=======================================
'copy report info for each email address
'=======================================
'Sheets(2).Cells(counter, 1) = idnum:
'Sheets(2).Cells(counter, 2) = rptname:
'Sheets(2).Cells(counter, 4) = parentfolder:
'Sheets(2).Cells(counter, 9) = lastrun:
'Sheets(2).Cells(counter, 12) = rptformat:
counter = counter + 1
End If

If ActiveCell.Value = "Properties" Then nFound = False: counter = counter + 1
Next a
End Sub

Sub BB()

' BB Macro
' Deletes empty rows
' Add SI_ID to column A1 where necessary
' Insert headers

' Delete empty rows

    LastRow = ActiveSheet.UsedRange.Row - 1 + _
        ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r


' Add SI_ID to column A1

For Each c In Range("A1", Range("A65535").End(xlUp).Address)
If c = "" Then c.Value = c.Offset(-1, 0).Value
Next
  
' Add headers

    With ThisWorkbook.Worksheets("Sheet2")
        .Rows(1).Insert
        .Range("A1").Value = "ID"
        .Range("B1").Value = "NAME"
        .Range("C1").Value = "OWNER"
        .Range("D1").Value = "PARENT_FOLDER"
        .Range("E1").Value = "CREATION_TIME"
        .Range("F1").Value = "UPDATE_TS"
        .Range("G1").Value = "STARTTIME"
        .Range("H1").Value = "ENDTIME"
        .Range("I1").Value = "LAST_RUN_TIME"
        .Range("J1").Value = "DESCRIPTION"
        .Range("K1").Value = "EXPORT_FORMAT"
        .Range("L1").Value = "PROGID_SCHEDULE"
'        .Range("M1").Value = "PERSONAL_CATEGORY"
        .Range("M1").Value = "REPORT_RECIPIENTS"
    End With
End Sub

With the Export worksheet open, run macro AA. This will create and populate a second worksheet called Sheet2.
When completed, switch to the Sheet2 worksheet and run macro BB.

This does a little formatting and header placement - you will have to format the height/width of the rows and columns to match your own data.

Told you it was convoluted...

 

[cesql]

Thanks. You're right is is convoluted but your help is appreciated. I have seen a similar query/macro also and have used it with some success... This would work except that there are scheduled report fields I need access to such as si_prompt- Name & default value or Schedule Event info that doesn't seem to be available thru the QB. I'm starting to wonder if it's even possible....:}. Thanks again!

 

[Wajid55]

Can I find out WHO ran the report?

 

[hilfy]

Yes, SI_OWNER should give you the name of the user who own's an object - which the person who ran an instance.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Wajid55]

Where can I find these SI tables and their structure? or somekind of data dictionary?

 

[hilfy]

I don't know how long this site will be available as they're moving everything to the SAP site, but go here:

http://devlibrary.businessobjects.com/BusinessObjectsXIR2/en/devsuite.htm

Click on the following:
Business Objects Enterprise SDK
.NET Developer Guide and API Reference
Reference
Query Language

This will get you to information about the query language and the tables and fields that are available.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.