BusinessObjects Board

Providing a list at run time (from excel)

general discussion
#1

I apologize if this question was asked before. I tried searching but the search wasn’t a friend this time :cry:

I have this unique requirement that the user wants to provide a list of id’s at run time. The list can be as long as couple of thousand id’s, hence can not be manually entered and needs to be imported from excel. BTW, the list is very dynamic.

Now, I know that there is (or at least was)a limitation of how many values can be provided in ‘in list’. In addition, I do not think of any other way possible to accomplish this out of the box.

The only thing that I can think of is to run another query based on the list the user has, and then combine the queries to filter the ones not in the list.

Can anyone throw some light on this and be the torch bearer for me :slight_smile:

Moderators, I am posting this in General section because I am open to Deski or Webi solutions.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#2

You can do that, in client or Webi (xi)… I have used this for client reports where the list of product ids is read into a query - that list is maintained in an XLS, and linked into the report as a dataprovider.

I think at that point you could use the “results of query” to get the data into your filter, where the query is against this spreadsheet.

You could also use a macro to read the values from that provider, stick them in a variable, and then plug that into the original query and refresh.

Hope that gives you some ideas,
Brent

bdouglas :switzerland: (BOB member since 2002-08-29)

#3

Brent,

Thanks for your reply. The users are new to Business Objects and this solution requires multiple queries and merging queries. Needless to say that it is a bit advanced for new users.
But, thanks for the response. I’ll keep an eye on this thread to see if there are other ideas.

Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#4

I know what that’s like : /

The low-budget solution is to cut and paste from Excel (or whatever) into an In-list prompt on a report… We do that, using a simple maco that concatenates all items in a column to a semicolon seperated list.

Not elegant, but the Finance users love it for their GL reporting.

B.

bdouglas :switzerland: (BOB member since 2002-08-29)

#5

Brent,

Let me ask you this, to my understanding there is a limit of in list values you can provide as an input. Is that right? I have read that its more of a SQL length limit that BOBJ has…How many values have you tried in list?

Also, is there a way that you can share the macro?

Thanks,
kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#6

I’m looking for that macro now… The limit to your SQL is probably going to be set by your database, in our case, the ODBC version. Teradata lets us do a meg of SQL, I think.

Also, the max number of items in list is set at the universe - but there were bugs in 6.5x that made this behavior inconsistent, so it was very confusing for our users (and front line support).

I did find that macro - it’s below (runs attached to a button on the input sheet).

B.


Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>0,R[-1]C&amp;"";""&amp;RC[-1],R[-1]C)"
    Range("B2").Select
    Selection.Copy
    Range("B3:B1000").Select
    ActiveSheet.Paste
    ActiveWindow.LargeScroll Down:=2
    Range("B1000").Select
    ActiveWindow.SmallScroll Down:=2
    Range("B1000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B1001").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("B1001").Select
    Application.CutCopyMode = False
    Selection.Copy
End Sub

bdouglas :switzerland: (BOB member since 2002-08-29)

#7

Similar solution here … Create an InList from an Excel spreadsheet … my users LOVE IT! Because we are on Oracle, it is subject to the 1,000 value limit. The users add more conditions and OR them together in batches of 1,000 if needed.

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

#8

Thanks a lot Brent! I really appreciate it!

Kashif Saeed :pakistan: (BOB member since 2004-06-02)

#9

Glad to help, that’s the spirit of this place.

Altho - some day, and that day may never come, I’ll call upon you do me a service…

B.

bdouglas :switzerland: (BOB member since 2002-08-29)