BusinessObjects Board

BO via Excel -- "inlist" Issue

building reports desktopintelligence
#1

When we perform a query using Business objects (via excel), we often use an “inlist” that is an excel name. We do this weekly, if not daily to manage post shipment transactions and statistics.

As most of us that utilize this feature knows, we are limited to approx 250 excel lines when we use this “in-list - excel name” My understanding is that this is a busines objects limiation.

This is very time consuming . We often have to run queries 10+ times- each time with a different in-list, just to complete a task. This happens weekly if not more frequently.

So, my questions are:

  1. Is this truly a BO issue?
  2. Does BO have a workaround that we could implement?

Please share your thoughts. Thanks much

lakkundi (BOB member since 2002-08-15)

BO report restricted based on Excel having 1500 rows
#2

You are limited as to the number of values that you can pick from a list by BusinessObjects. You are limited on the number of values that can be provided to the query by the backend database. For example, Oracle (newer versions) apparently allows up to 8K items in an “In List” condition.

What you can do, in that case, is write a macro to “transpose” your data in Excel into a single cell, separated by commas. So if your worksheet has this data:

1
2
3

You end up with 1,2,3. Then copy / paste that cell into your condition instead of using the “show list of values” option. Then you are bypassing the BusinessObjects limit, and only need to worry about the database limit.

Oh, and about getting the data in Excel into the correct format. 8)

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

Dave, Thanks for reply. But I did not get the first two sentences. Are you saying that it is limitation of both BO and the back end database?

We use Oracle 8.1.7 or 8.0.5. Are these Databases having this limitation of 250 values? or 8K values?

Is there a way around this other than writing macro?

lakkundi (BOB member since 2002-08-15)

#4

BusinessObjects has a limit to the number of values that can be selected from the LOV query. In other words, select “show list of values”, and start picking. When you get to ~250, you have reached the limit that BusinessObjects imposes.

Now, skip the list selection. Instead, starting typing in your values in the In List condition by hand. You can exceed the ~250 item limit, and are then limited by whatever your database will allow you to process.

Put another way… would you really want to select 8,000 items from a pick list? Probably not. 8) But if you could get those 8,000 items into a comma-delimited list, you could (in theory) copy / paste that list into the query and as long as your database allowed it to work, it would run.

There are no quick Excel options that I know of to transpose the data. The macro is not difficult to write.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Here is a sample Excel macro that takes everything from column “A” and transposes it into a single cell, specifically B1. It will turn:

1
2
3
A
B
C

into 1,2,3,A,B,C

I don’t claim to be an Excel coding guru, but it does seem to work. 8) If you want to change the code, it should be obvious where the references to column “A” and to cell “B1” are.

Dave

Option Explicit
Sub TransposeColumn()

    Dim strAllValues As String
    Dim intRowNumber As Integer
    Dim myCellValue As Variant
    
    intRowNumber = 1
    
    myCellValue = Range("A" & intRowNumber).Value
    
    While myCellValue <> ""
        
        If strAllValues <> "" Then
            strAllValues = strAllValues &amp; ","
        End If
        strAllValues = strAllValues &amp; myCellValue
        intRowNumber = intRowNumber + 1
        myCellValue = Range("A" &amp; intRowNumber).Value
        
    Wend
    
    Range("B1").Value = strAllValues
    
End Sub

Dave Rathbun :us: (BOB member since 2002-06-06)

#6

I just had a couple of quick thoughts, they may or may not be of any value.

Is there any way to use ranges instead of the inlist?

Instead of loading the values into Excel spreadsheets, could you load them into a database table and join the table to another table you are already using in the query?

Lee Drake :us: (BOB member since 2002-08-15)

#7

Hi dave-

I was able to use your macro for transposing columns into rows for inputting into BO- it was so helpful

I’m having one problem though- the values that I am using (in Excel) are 5 digit numbers (originally a list from BO copied into Excel)- the problem is when I copy the values from BO into Excel- the format of a 5 digit number is lost on values that start with a zero (ex: 01234 becomes 1234) and I seem unable to get the right format of the numbers before I transpose in order to put back in BO as a selection for my conditions.

Do you know of a fix for this either in BO before I export the original list or in Excel?

Thanks very much.

Kristen :smiley:

khoover (BOB member since 2006-03-13)

#8

You can find a similar utility here.

The solution is to format the Excel column as text, before pasting the values. The contents are then displayed literally, instead of being displayed as numbers.

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

#9

I often use Dwayne’s macro… simple, very convenient.

magicmax :fr: (BOB member since 2006-03-23)