I need to get a list of all the report tab names in all of our corporate documents. The report names are only stored in the repository in the BLOB column called X_DOC_B_CONTENT in the OBJ_X_DOCUMENTS entity. Since it is a BLOB and binary there appears to be no secure way to extract the report tab names for each BLOB.
My only guess so far is to use the following code:
String [] indexes = webiDocument.getReportList();
I propose to open all corporate documents, one by one, and extract the list of report tab names for each one.
If anyone has a better way to do it or a complete passage of code I would greatly appreciate it.
To be honest, since you are talking about full client reports (since webi reports can’t have tab names) you would be better off using VBA. From there it would be fairly easy. The hard part would be retrieving all docs from corporate documents to a local folder. Then loop through all REP files, opening each one. Count the report tabs with SomeDocument.Reports.Count(), then loop from 1 to that number getting the report name.
You might be able to do it with the Webi SDK, but it would be more difficult, I think.
I will try it. It just needs to run once a day at night and I can schedule it with BCA. Also I can use the ExportToRDBMS to have it re-create the table with the Document and Report Tab columns.
You may already have figured this out but just in case I figured out how to create a report tab from a Data Providor, then save the report as text and use the text file as a new Data Provider to then allow ExportTo RDBMS of the second DP.
This allows me to “massage” the data in the first DP using the report formulas and formats. So then BCA becomes an ETL tool. THe only downfall is that the ExportToRDBMS drops/creates the table each time. Soooo I run a third DP that does a PL/SQL select from the ExportToRDBMS and inserts the “new” rows to a premenant table.
Finally we then run other reports against the ExportToRDBMS table or the permenant table. Thus we are effectively doing what a PL/SQL developer does in creating temp tables to then select from or a DataWarehouse developer does with Informatica and Oracle, for example, in ETL’s.
I’m not sure exactly what all you are trying to accomplish, but this thread may give some other ideas. I use VBA to open a series of documents and “harvest” the objects used in each report. The twist is that I record the information in a VBA data provider, which has the advantage of being able to use the full power of BusObj reporting, and to export the microcube if needed. Easily modified to grab report tab names, I’m sure.
ExportToRDBMS exports the [i]raw[/i] data. If the Report tabs are all based on the same DataProvider, there is no reason to filter or look at each report independently.
If you are trying to “Export” the Report Tab itself, there are plenty of VBA macros that will save each tab as a separate document (or combine them into a single XLS file).
Also note that the “Exported” table will be the Name of the DataProvider. If you have multiple DP, you should not have to worry much about this since no two DPs can have the same name.
Your right. I wasn’t realy clear in my quick summarization.
I created another topic where I tried to be more thurough but I got C- in English, so… Antway, here is the URL - https://bobj-board.org/t/19513. If the topic could be made more clear and I attach example .rep’s and VBA I was hoping to make it usefull to the group as an entry in the examples forum.
Perhaps if your interested we could get it ready together. I have saved soo much time using the examples from BOB I feel compelled to return the favor.
Thanks. I was able to get the list of document and report name combinations from the open document and the documents in a particular folder (another topic of your’s - https://bobj-board.org/t/16473).
Can you see a way to get the list of documents from the Corporate Documents in our repository? I looked through all the “busobj” objects in the VBA Object Browser and couldn’t find one.
Please let me know as I only need the list for our CorpDocs.
You’ll need to do it the hard way… establish a direct connection and write the SQL to get the document names. Once you get the names, you can retrieve them (in a loop) using the standard object model.
THe code is complete. Here is the VBA. Each time it is run it re-creates an Oracle table of report names by document name and that table was added to our BusObj.unv and other universes buit against the repository.
The first part is just environmental stuff. THe second part reads all reports that have been sent to BCA that are saved in the specified folder. The third goes through the corp docs in the repositoryusing the report .
Each .rep is opened, the tab names are written to disk and finally the disk file is loaded to a DP and exported to Oracle where it can be seen through the connection in universes mentioned above.
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long
Private Sub Document_AfterRefresh()
Dim myDoc As busobj.Document
Dim myRep As busobj.Report
Dim Dps As DataProviders
Dim Dp As DataProvider
Dim Cols As Columns
Dim Col As Column
Dim ConnectionsCount
Dim ComputerName As String
Dim ComputerNameLen As Long
Dim Result As Long
Dim Buffer As String * 100
Dim BuffLen As Long
Dim Flag As Boolean
TrueFlag = "True"
Dim i, j, k As Integer ' loop counters
BuffLen = 100
GetUserName Buffer, BuffLen
UserName = Left(Buffer, BuffLen - 1)
ComputerNameLen = 256
ComputerName = Space(ComputerNameLen)
Result = GetComputerName(ComputerName, ComputerNameLen)
If Result <> 0 Then
NameOfComputer = Left(ComputerName, ComputerNameLen)
Else
NameOfComputer = "Unknown"
End If
' Set the file names for the text export, blatlist and .bat files
Pathing = "c:\Compass\TrashBin\"
RepFilePathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\*.rep"
RepPathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\"
If (NameOfComputer = "2BBOTEST01") Then
Pathing = "d:\Compass\TrashBin\"
RepFilePathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\*.rep"
RepPathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\"
End If
If (NameOfComputer = "2BBOWEB02") Then
Pathing = "d:\Compass\TrashBin\"
RepFilePathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\*.rep"
RepPathing = "\\sbms-res-23\Groups\CompassBusObj\Delivered To Production\BCA Only\"
End If
'DocName = ActiveDocument.Name
DP3FileName = Pathing & DocName
hFile = FreeFile
Open DP3FileName & ".txt" For Output Access Write As hFile
FileName = Dir(RepFilePathing, vbNormal)
While FileName <> ""
DocName = Left$(FileName, InStr(FileName, ".") - 1)
' MsgBox (DocName)
If DocName = "BO_-_Corp_and_BCA_Docs_VBA" Then GoTo SkipThisReport
Application.Documents.Open (RepPathing & FileName)
For j = 1 To ActiveDocument.Reports.Count
Print #hFile, DocName & vbTab & ActiveDocument.Reports.Item(j).Name
Next j
ActiveDocument.Close
SkipThisReport:
FileName = Dir()
Wend
Set myDoc = busobj.ActiveDocument
Set Dps = ThisDocument.DataProviders
Set Dp = Dps.Item(1)
Set Cols = Dp.Columns
Set Col = Cols.Item("Document Name")
'Application.ExchangeMode = boRepositoryMode
'Application.ExchangeDomain = "Document"
Application.ExchangeMode = boRepositoryMode
Application.ExchangeDomain = "C_Production"
MacroXPathName = "C:\compass\trashbin"
Dim l1 As busobj.Document
For i = 1 To Col.Count
'MsgBox ("DN=" & Col.Item(i))
DocName = Col.Item(i)
If DocName = "BO_-_Corp_and_BCA_Docs_VBA" Then GoTo SkipThisReportAlso
On Error Resume Next
Kill (MacroXPathName + "\" + Col.Item(i))
Call Application.Documents.Receive(Col.Item(i), MacroXPathName)
DocName = MacroXPathName & Col.Item(i)
Set l1 = Application.Documents.Open(DocName, TrueFlag, TrueFlag)
For j = 1 To ActiveDocument.Reports.Count
Print #hFile, Col.Item(i) & vbTab & ActiveDocument.Reports.Item(j).Name
Next j
l1.Close
SkipThisReportAlso:
Next i
Close hFile
ActiveDocument.DataProviders.Item(3).Refresh
Call myDoc.DataProviders.Item(3).ExportToRDBMS("BOVIEW - Prod", boSharedConnection)
ActiveDocument.Close
End Sub
(BOB member since 2002-06-06)