BusinessObjects Board

Launch BO reports from Excel with dynamic conditions

sdk development
#1

Hi!

I need to launch everyday a business object report in BO 6.1.3 (desktop) modifying a list of phone numbers in a dimension called “Phone”. I would like to have an Excel file where I set the phone numbers I would like to look for in the BO universe, launch the report and save the results. I have modified an example I found, but I don’t know how to modify the conditions fo the report with VBA code in my Excel file. If I just want to refresh the report, it works fine, but never adds or updates de value of the condition. Any idea?

In my Excel I have info in cells which I pass as variable to the VBA function:

C2 = Business Object login
C3 = Business Object password
C4 = BO report path
C5 = Exported results path
C6 = list of phone numbers separated with "," o ";" (whatever is needed)

The VBA code I run from Excel:

Sub Exec_BO_Query()
    Dim BoApp As Object
    On Error Resume Next
    Set BoApp = CreateObject("BusinessObjects.application")
    With BoApp
        .Visible = True
        .LoginAs Worksheets("Configuration").Range("C2").Value, Worksheets("Configuration").Range("C3").Value, False
        .Documents.Open (Worksheets("Configuration").Range("C4").Value)
             With .ActiveDocument
                  .Queries.Item(1).Conditions.Add "Customer data", "Phone", "In list", Worksheets("Configuration").Range("C6").Value 
                  .Refresh
                  .SaveAs Worksheets("Configuration").Range("C5").Value
                  .Close
            End With
        .Application.Quit
    End With
    Set BoApp = Nothing
    Workbooks(ThisWorkbook.Name).RefreshAll
End Sub

I don’t know how to use properly the Conditions.add (even I don’t know if is the right object to use) but nothing happens with the condition when I launch the report (the condition y send via VBA doesn’t appear in the report) .
The dimension “Phone” is inside a Folder called “Customer data”, I don’t know if that’s exactly a Class in the BO object model and the report I’m using to test this is quite simple, just 2 fields (“phone”, and “contract”).

Also, I would like to know how to add more conditions and how to define if is an AND or an OR condition.

The syntax according to the documentation is this one:

var.Add(class, objectorcondition, [operator], [operand1], [operand1type], [operand2], [operand2type])

var is the name of the Conditions variable that you declare.
class is a string that identifies the class.
objectorcondition is a string that identifies the condition.
operator identifies the operation. This parameter is optional.
operand1, operand2 are each operands for the condition. These parameters are optional.
operand1type, operand2type are each operand types for the corresponding operator. These parameters are optional.

Thanks for any help!

juagar (BOB member since 2018-01-31)

#2

More info if it helps.

I’m testing with a simple report with just 2 fields, “Contract” and “Customer”. I would like to get all contracts owned by a customer or a list of thems so I can add a condition like Customer Equal to “1” or Customer In list “1;2;3”.
With excel, I would like to write in a cell all the customers I would like to look for in that report, so my problem is that I don’t know how to pass them to the report through VBA code.

I have tried something like

ActiveDocument.Queries.Item(1).Conditions.Add "Data", "Customer", "In list", "1;2;3"

“Data” is the folder where are the fields “Contract” and “Customer” in my BO universe, but I don’t know if I’m using properly the Conditions.Add method or if I have to use something different.

I also have tried to create a promt and filling it doing this:

ActiveDocument.Variables("Customer").Value = "1;2;3"

or

ActiveDocument.Variables("Write here the list of customers:").Value = "1;2;3"

But none of these ways work. Anyone can help me?

Once I make it work, instead of “1;2;3” values I will send the values of the Excel cell with Worksheets(“Configuración”).Range(“C6”).Value

juagar (BOB member since 2018-01-31)