create a report using custom LOV

I have to create a report where there is a condition to fetch data for particular invoice numbers and invoice dates. These nos and dates are stored in an excel sheet which I want to make as a source for my report.
I have made use of custom LOVs in the universe which is pointed to that excel sheet. My requirement is that when I refresh the report, it should automatically pick data from that excel sheet and fetch records based on that. I have to schedule this report on BCA (unix based)


pravink81 :india: (BOB member since 2004-06-30)

Which version of Business Objects are you using?
V6 has a new feature which allows you to use the result set of one data provider (your MS Excel spreadsheet) as an input condition for your second data provider (your universe data provider).


Andreas :de: (BOB member since 2002-06-20)

I am working on BO version 5.1.7

How can I use results from one data provider as condition for the other ?? (I want to do the filtration at the data provider level and not at report level )


pravink81 :india: (BOB member since 2004-06-30)

You do need Business Objects v6.x for that.

Your best options for Business Objects v5.x might be:

  1. VBA (cannot be done in your case since you are on UNIX).
  2. Load the MS Excel spreadsheet into the database, then modify the universe to include this new table and build a predefined condition based on it.

Andreas :de: (BOB member since 2002-06-20)

Isn’t there anything which we can tare care of at BO itself since I am reluctant to touch the Database.

The user will be updating this excel sheet once in a while. I have checked “Automatic refresh before use” and “Export with universe” options in the LOV properties in universe. But now, when I use this LOV in my report and refresh it, it pop ups a window which gives the excel location on the server and also asks number of records to be selected from that sheet. I want to skip this window since I would be scheduling it to BCA ( I would be selecting all the rows from the excel). Is it possible ?? ( I am ok with hard coding ).


pravink81 :india: (BOB member since 2004-06-30)

I have a user with a similar need … to periodically “feed” a list of serial numbers (received from a vendor via spreasheet) to a BusObj data provider. As we’ve discussed, the long-term answer is v6, but in the meantime here is another option.

Our solution is a tiny macro on the Excel side to quickly build a string used as an operand for an In List. Build the BusObj report with an In List operator and a simple prompt for the operand. In Excel, select the range containing the list, run the macro, then toggle to BusObj and paste the result in the prompt. This can be done while refreshing the report, or while scheduling it in BCA.

Sub CreateList()
'This procedure creates a semi-colon delimited list of all the values
'in the currently selected range, and copies it to the clipboard.  To
'make use of the clipboard, it is necessary to add a reference to the
'MS Forms library (in the VB Editor, Tools, References... and select
'Microsoft Forms x.x Object Library)

    Dim Rng As Range
    Dim ClipObj As DataObject
    Dim TheList As String
    Dim i As Long

    Set Rng = Selection.Cells
    If Rng.Count > 1000 Then
        MsgBox "No more than 1,000 cells may be selected", _
            vbCritical, "Create List"
        Exit Sub
    End If

    TheList = Rng(1)
    If Rng.Count > 1 Then
        For i = 2 To Rng.Count
            TheList = TheList & ";" & Rng(i)
        Next i
    End If

    Set ClipObj = New DataObject
    ClipObj.SetText TheList
    ClipObj.PutInClipboard
    MsgBox "A list of " & Format(Rng.Count, "#,##0") & _
        " values has been copied to clipboard", vbInformation, _
        "Create List"

End Sub

We use Oracle, hence the 1,000 value limit. Should you have more than 1,000 values, we just add additional “OR’d” conditions. Not sure if this technique is useful to you, but if your spreadsheet doesn’t change that often this might be a reasonable workaround until you move to v6.


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

Thanks for your help Dwayne.

But your solution would not work in my scenario since we are on unix envirnment and cannot use macros. Second, your macro puts the list into clipboard but you still would be required to manually paste it to BO prompt. I need an entirely automated process. Whenever the report runs, it should automatically pick up all the values in the excel sheet and fetch data based on it.


pravink81 :india: (BOB member since 2004-06-30)