Without manually opening a report, is there a way to determine what objects are used in a report?
We have thousands of reports and are trying to get rid of unused objects.
Thanks,
Ken
Ken Raffel (BOB member since 2002-08-29)
You could query the (document or universe) repository, look in your BO pdf files for structure of repository.
Andreas 
(BOB member since 2002-06-20)
I know how to query objects in a universe. However, is it possible to query a document as it is stored as a BLOB object in the database. If so, how would you do that?
Thanks,
Ken
Ken Raffel (BOB member since 2002-08-29)
Without opening the reports just based on the repository there is absolutely no chance to get there.
However if you are willing to import (automated) and read the documents.
Then we can help you. Our product stores in a central database all the data providers (universe and others which have been opened on stations where our add-in is installed)
We could install our add-in on 1 PC and have a script retrieve all the documents (if the user has the rights to) and write the results into our database.
But if you have users with many local documents we would recommend that you install the add-in on every client.
If you are at the Conference in Miami we will be present at the Infosol Stand in the exhibit hall or have a look at our web site : www.ebiexperts.com the product you are looking for is “Enterprise Manager for Business Objects”
Hope this helps
Klaus
ClaireB (BOB member since 2002-08-09)
I’ll be posting something shortly that I use for this…
Dave
Dave Rathbun 
(BOB member since 2002-06-06)
There is some code posted in the Code Library that you can use as a starting point. It’s a macro that I use to process a large quantity of documents and determine which objects are used, and from which universes.
Dave
Dave Rathbun (BOB member since 2002-06-06)
That was exactly what I was looking for. Thanks!
Ken Raffel (BOB member since 2002-08-29)
Thanks to Dave for a great starting point with this script!
I’ve modified it to get the class name from the DataProvider.Queries objects, and that works fine.
The only trouble is when scanning hundreds of documents, somw are set to ‘refresh on open’, halting the process and requiring Cancel, then Ok to be clicked to ignore the refresh.
Is it possible to auto-cancel the refresh on open within the script? All it needs is a purged data provider object to explore.
Cheers, it’s good to be back after all these years!
philmorris 
(BOB member since 2002-11-12)
If you can, post the appropriate code additions here, and I’ll get them added to the code sample.
Good question! I don’t know if there is or not. Something to experiment with…
Dave
Dave Rathbun (BOB member since 2002-06-06)
Phil,
Quick thought… Create a user (in Supervisor) and create a dummy (non-working) connection. Select this user, and for every UNIVERSE, change the “Connection” to this dummy connection.
Since the connections won’t work, the queries won’t run and your analysis shouldn’t stop running. And… since this connection is ONLY assigned to this user, it shouldn’t affect any other processing.
Cheers.
-RM
digpen (BOB member since 2002-08-15)
Ok, here’s the bits I modified. You CAN get duplicates where there’s more than one data provider, but this can be cancelled out by counting unique report name by object to get that analysis, or just count it to get a total usage count.
define another counter variable:
Dim i, j, k, l As Integer ' loop countersput another column in the header output:
Print #OutNum, "Universe"; ", "; "Report Name"; ", "; "Class Name"; ", "; "Object Name"; ", Clause"and here’s the central block of code with the loops around each part:
' This is dave's original loop code - this doesn't provide the class name to the object
' For k = 1 To dpMyDataProvider.Columns.Count
' Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; dpMyDataProvider.Columns.Item(k).Name; ", "; ", "; "Object"
' Next k
' This loop DOES return the class name
For l = 1 To dpMyDataProvider.Queries.Count
For k = 1 To dpMyDataProvider.Queries.Item(l).Results.Count
Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; dpMyDataProvider.Queries.Item(l).Results.Item(k).Class; ", "; dpMyDataProvider.Queries.Item(l).Results.Item(k).Object; ", "; "QObject"
Next k
Next l
' this is a loop to output formulae in the document. Disabled for normal use
' For k = 1 To docMyDocument.Variables.Count
' Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; docMyDocument.DocumentVariables.Item(k).Name; ", "; docMyDocument.DocumentVariables.Item(k).Formula; ", "; "Variable"
' Next k
' And to pick up objects used in conditions...
For l = 1 To dpMyDataProvider.Queries.Count
For k = 1 To dpMyDataProvider.Queries.Item(l).Conditions.Count
Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; dpMyDataProvider.Queries.Item(l).Conditions.Item(k).Class; ", "; dpMyDataProvider.Queries.Item(l).Conditions.Item(k).Object; ", "; "Condition"
Next k
Next lsorry this is a little rough and ready, but I’m sure you get the gist. Hope this helps out!
Cheers,
philmorris (BOB member since 2002-11-12)
Outstanding!!
Dave and I were just talking a couple of weeks ago about getting the Class name from objects in a report. I am taking over years of existing reports and universes with hundreds of objects and classes. This will make documenting and cleanup in my life a lot easier.
But Phil, When the object in the condition is an @Prompt is there a way to get the @Prompt text and display it in addition to the object in which it is going to condition against??
Just looking to taking this macro to another level.
estaup (BOB member since 2002-10-15)
I would have thought so but I haven’t tried - under the same object .Query.Item(n).???
there are: Class, Object, operand, OperandCount, Operant Type, Operator
I’d think they’d give you everything you need but I haven’t tried…
philmorris (BOB member since 2002-11-12)
I’ll give it a try a little later today and post back a sample of what is produced for all items.
Thanks again.
estaup (BOB member since 2002-10-15)
I had a little time this morning before a meeting to plug on in to see the results. I am getting the following error using any of the other object items you listed.
“Object doesn’t support this property or method.”
Here is one of the code loops I added to the macro.
' this loop to output OPERAND use in the document.
For m = 1 To docMyDocument.Variables.Count
Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; docMyDocument.DocumentVariables.Item(k).Name; ", "; docMyDocument.DocumentVariables.Item(m).Operand; ", "; "Variable"
Next m
estaup (BOB member since 2002-10-15)
My twist on this topic. The utility I created goes after data provider objects only, but stores them in a VBA data provider so you get the full reporting capabilities of Business Objects itself (formatting, filtering, sorting, and the like). It’s been quite useful for us in determining which reports are impacted by universe changes, for “desk checking” a particularly complex data provider, and so forth.
Copy the code below into a module. Create a new data provider choosing “access in a different way”, then “VB procedures”, and finally the “GetObjects” subroutine. Save the file as your utility. I thought about creating a picklist dialogue or somesuch to choose files, but I chose to simply place the utility and the *.rep files you want to process in a temporary subdirectory by themselves. The utility will cycle through all of the *.rep files in the same directory as the utility. The files are opened read-only, and will not auto refresh even if that option is set in the document. You will need to modify the Open method to provide passwords if your documents are protected in that manner.
I did have a problem where some of the information would get confused if the file wasn’t last saved in the current environment (QA vs production), so I added a “save and close” step for each data provider. Adds to the runtime, but clears up all the connection, universe, etc. information. Notice that it does capture the class and object information, and also designates whether an item is a result object, condition object, or pre-defined condition object.
I’ll gladly share the utility itself (which includes the code and the reports that we use) if there is interest. I could send it to BOB for posting here. Did we also have a place (yahoo or something) to temporarily post files?
Sub GetObjects(dpInterface As DpVBAInterface)
Dim dpCube As DpVBACube
Set dpCube = dpInterface.DpVBACubes(1) 'there is only one cube per data provider
Call DefineCube(dpCube) 'create cube
Dim ReportFile As String
Dim Doc As Document
Dim DataProv As DataProvider
Dim Qry As Query
Dim Res As Result
Dim Cond As Condition
Dim DataArray(1 To 7)
Dim j As Long
'store pathname so that it can be printed on reports
'ThisDocument.DocumentVariables("Reports Path").Formula = ThisDocument.Path
'initialize the list of files to be processed
ReportFile = Dir(ThisDocument.Path & "\*.rep")
Do Until ReportFile = ""
DataArray(1) = ReportFile
Set Doc = Application.Documents.Open(ThisDocument.Path & "\" & ReportFile, True, True)
If Doc.Name <> ThisDocument.Name Then 'skip this utility
'process each data provider
For Each DataProv In Doc.DataProviders
'skip data providers that are not based on a universe
If DataProv.GetType = "DPQTC" Then
'a "save and close" is necessary to ensure accurate object definition
'since there is no method to accomplish this, keystrokes are sent to the Edit method
'SendKeys "%s%y"
'DataProv.Edit
'load data provider into memory (faster)
DataProv.Load
DataArray(2) = DataProv.Name
DataArray(3) = DataProv.UniverseName
For Each Qry In DataProv.Queries
DataArray(4) = Qry.Name
For Each Res In Qry.Results
DataArray(5) = "Result"
DataArray(6) = Res.Class
DataArray(7) = Res.Object
'add record to the cube
j = dpCube.DpVBAColumns.AddLine(DataArray)
Next Res
For Each Cond In Qry.Conditions
DataArray(5) = IIf(Cond.OperandCount = 0, "Predefined", "Condition")
DataArray(6) = Cond.Class
DataArray(7) = Cond.Object
'add record to the cube
j = dpCube.DpVBAColumns.AddLine(DataArray)
Next Cond
Next Qry
'unload data provider from memory
DataProv.Unload
End If
Next DataProv
Doc.Close
End If
ReportFile = Dir() 'get next filename to be processed
Loop
End Sub
Sub DefineCube(dpCube As DpVBACube)
Dim dpColumn As DpVBAColumn
'set the number of columns for this cube
dpCube.DpVBAColumns.SetNbColumns (7)
'define column 1
Set dpColumn = dpCube.DpVBAColumns(1)
dpColumn.Name = "Report Filename"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 2
Set dpColumn = dpCube.DpVBAColumns(2)
dpColumn.Name = "Data Provider"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 3
Set dpColumn = dpCube.DpVBAColumns(3)
dpColumn.Name = "Universe"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 4
Set dpColumn = dpCube.DpVBAColumns(4)
dpColumn.Name = "Query"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 5
Set dpColumn = dpCube.DpVBAColumns(5)
dpColumn.Name = "Type"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 6
Set dpColumn = dpCube.DpVBAColumns(6)
dpColumn.Name = "Class"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
'define column 7
Set dpColumn = dpCube.DpVBAColumns(7)
dpColumn.Name = "Object"
dpColumn.Qualification = boDimension
dpColumn.Type = boCharacterObject
End SubDwayne Hoffpauir (BOB member since 2002-09-19)
Top marks to that man 
Definitely a neater solution… although having the .csv file around is quite handy.
Also, you’ve inadvertently solved my problem in my script by showing me the extra bits to the Document.Open method, where you can turn off the Auto Refresh on Open.
So, here’s a slight mod to the original script to prevent it ‘sticking’ when this property is set on one of the documents (first line included to help find the place in the script, second line modified):
If strCurrentFile <> ThisDocument.Name & ".rep" Then
Set docMyDocument = Application.Documents.Open(strDocPath & strCurrentFile,True,True)…where the first True is “NoAutomaticRefresh” and the second is “ReadOnly”.
Now I’m getting another one (Doh!):
Oh well, can’t win 'em all 
at least Dwayne’s doesn’t seem to suffer this.
philmorris (BOB member since 2002-11-12)
Oops can someone help me with how to delete posts… sorry - I kept getting an error when posting. 
philmorris (BOB member since 2002-11-12)
Just one more 
To get rid of the unwanted ‘Some Obsolete Objects have been removed’ I used the following statement:
Application.Interactive = FalseI put this just before the document open, then the opposite (= True) at the end of the loop, so we can see progress in the form of the current document in the title bar.
This script is now motoring on without any maintenance.
I noticed during this that it seems to be ‘opening’ the data provider for every reference to it during the loops. Can this be limited to a single reference? Would 'With ’ do this?
Moderator - would it be worth posting this version, and Dwayne’s alternative in the code section of the forum?
Thanks to everyone who helped!
philmorris (BOB member since 2002-11-12)
If you place a ‘(’ character after ‘.Operand’ the VB editor asks for Index as Long
I can only assume this means there are many of these too that need looping around. I haven’t tried it but add another counter and loop based on ‘OperandCount’ (I assume) and output this. e.g:
Print #OutNum, strUniverseName; ", "; strCurrentFile; ", "; docMyDocument.DocumentVariables.Item(k).Name; ", "; docMyDocument.DocumentVariables.Item(m).[b]Operand(n)[/b]; ", "; "Variable"
I reckon this might do the trick - haven’t tried though - sorry.
philmorris (BOB member since 2002-11-12)