List of Objects used in report

[system]

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 counters

put 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 l

sorry 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)

[system]

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)

[system]

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)

[system]

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)

[system]

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)

[system]

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 Sub

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

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 &amp; ".rep" Then 

        Set docMyDocument = Application.Documents.Open(strDocPath &amp; strCurrentFile,True,True)

…where the first True is “NoAutomaticRefresh” and the second is “ReadOnly”.

Now I’m getting another one (Doh!):

Some obsolete Objects have been removed from the query. (QP0027)

Oh well, can’t win 'em all at least Dwayne’s doesn’t seem to suffer this.

---

philmorris (BOB member since 2002-11-12)

[system]

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)

[system]

Just one more

To get rid of the unwanted ‘Some Obsolete Objects have been removed’ I used the following statement:

Application.Interactive = False

I 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)

[system]

@estaup

“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

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)

[system]

@philmorris

Oops can someone help me with how to delete posts… sorry - I kept getting an error when posting.

Done.

---

MichaelWelter (BOB member since 2002-08-08)

[system]

@philmorris

Top marks to that man .

Thanks. I’m basically a lazy person (aren’t we all if we were honest ), so I write these utilities to make my life easier … OK, so I guess THAT part is not lazy

@philmorris

Definitely a neater solution… although having the .csv file around is quite handy.

You can always export the data provider to csv … I can see “snapshots” being quite useful.

@philmorris

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.

Yep, that was a big help. That and the read-only switch made me more comfortable.

@philmorris

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?

This is from another thread over in SDK land, but I’ll repeat it here. It addresses the speed issue.

@Dwayne Hoffpauir

As explained in the text accompanying the code, there is a “save and close” done on each data provider, causing the extended runtime. I added that step when I was getting inconsistent capture of the query tab names. Of all things I was getting the UNIVERSE name eight times (once for each of the combined query tabs) instead! The save and close fixed that problem.

Comment out the Sendkeys and DataProv.Edit lines of code, and it will speed up SIGNIFICANTLY. Look at your results carefully, but if you are staying consistently within one environment (like production) you are probably OK with the speedier version.

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

Dwayne… I know nothing about VB, but REALLY need this info from our environment…

Can you give me a few hints about how to get started? Example: “Copy the code into a module”… what kind? where? Sorry to be a bit off topic, but I’d really love to use this utility.

@Dwayne Hoffpauir

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 Sub

---

Jill Gerry (BOB member since 2002-10-26)

[system]

@Jill Gerry

Dwayne… I know nothing about VB, but REALLY need this info from our environment…

Can you give me a few hints about how to get started? Example: “Copy the code into a module”… what kind? where? Sorry to be a bit off topic, but I’d really love to use this utility.

This has sorta turned into an SDK topic, but I’ll answer it for you here. From Business Objects, press Alt-F11 which is a shortcut to open the VB editor. Be sure that the project matching your report file is selected on the left, then from the menu at the top choose Insert, Module. Then copy and paste the code into that module. Hopefully the instructions in the previous message will carry you from there.

I also have a BusObj document that I can send to you if you like, with the code and some pre-formatted reports in it, if that might be useful.

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

Thanks Dwayne…

I can try that, but send me the report too if it’s not much trouble!

Thanks!

gerryj@nycap.rr.com

---

Jill Gerry (BOB member since 2002-10-26)

[system]

@Jill Gerry

Thanks Dwayne…

I can try that, but send me the report too if it’s not much trouble!

Thanks!

gerryj@nycap.rr.com

No problem … I’ll dig it up tomorrow

---

Dwayne Hoffpauir (BOB member since 2002-09-19)

[system]

Would you please send me the same report my email id is gj2002@indiya.com

---

JaiGupta (BOB member since 2002-09-12)

[system]

Bob is so cool or have I said it before.

This need arose yesterday, I searched the forum, found this topic, downloaded the code and I’m happily going through my reports.

Thanks to Dwayne and Bob!

---

KSG (BOB member since 2002-07-17)

[system]

Hi all, I get an error of ‘Runtime error ‘20’: Cannot find the Variable by it’s name’ against line 18
Line 18:
ThisDocument.DocumentVariables(“Reports Path”).Formula = ThisDocument.Path.

When I hover the cursor over this line it says:
"ThisDocument.DocumentVariables("Re… =
Do I need to replace “Reports Path” with something?

This error is at the point where I start to create the Data Provider using the ‘GetObjects’ code.

I know nothing about VB and am at a loss how to resolve this. Mention of a pre-formatted report in previous posts has given me hope though! Would someone be able to mail me a copy if they have received it from Dwayne, as I think it is the creating as a BO report part I can’t seem to fix.

Sorry, forgot my email: neil.phillips@hyde-housing.co.uk

---

Neil Phillips (BOB member since 2002-08-22)

[system]

@Neil Phillips

Hi all, I get an error of ‘Runtime error ‘20’: Cannot find the Variable by it’s name’ against line 18
Line 18:
ThisDocument.DocumentVariables(“Reports Path”).Formula = ThisDocument.Path.

When I hover the cursor over this line it says:
"ThisDocument.DocumentVariables("Re… =
Do I need to replace “Reports Path” with something?

This error is at the point where I start to create the Data Provider using the ‘GetObjects’ code.

I know nothing about VB and am at a loss how to resolve this. Mention of a pre-formatted report in previous posts has given me hope though! Would someone be able to mail me a copy if they have received it from Dwayne, as I think it is the creating as a BO report part I can’t seem to fix.

Sorry, forgot my email: neil.phillips@hyde-housing.co.uk

You need a variable in your report named “Report Path” (Data, Variables, Add). That line of code simply records the path name into a report variable, so that the variable can be used to display the path on the report (heading maybe) if you desire. That line can be safely commented out (leading apostrophe).

In the meantime, I will forward the current version (with reports and code) to you via email. An improved version will also be posted for download from the Code Samples forum soon.

---

Dwayne Hoffpauir (BOB member since 2002-09-19)