I’ve got a report with 2 queries in it.
Query 2 uses some of the results of query 1 in the conditions section.
Now, when I was using a normal “InList” and doing a copy/paste of the values, query 2 ran in a couple of minutes. Now I’m doing this the new way, query 2 doesn’t seem to finish at all (I’ve left it running for over an hour).
Anyone any ideas on why this could be?
I’m using Business Objects 12.1 if it helps.
Apologies for the double post, I think I dropped the last one in the wrong place.
Plessiusa - The original way was to get a list of values via Excel and then do a copy/paste into an InList condition manually, overwriting what was already there to update the report. There could have been up to 5 InList conditions, each one containing up to 950 values to keep the list under the limit.
What I’ve done is to read the values into BOXI from Excel (after they’ve been split into columns of no more than 950) via a query and then use the results of that query to populate the InList values in the second query.
Mak 1 - There can be up to 9000 values, but I’ve duplicated the query with the most values to split the conditions. The first condition has 5 InList conditions as detailed above (and runs fine), the second has 4 (most of which are zero).
I’ve also put in a condition in both queries that says the value has to be grater than zero to get around that particular problem.
Does the query actually get executed at the DB side, ask your DBA?
What happens when you run the queries individually?
You may be better off trying to get these Excel sheets created as database tables.
Another alternative from Bobs downloads, is here:-
I would check whether the query is actually going anywhere.
So the provider of the list runs OK on its own, but when you refresh the 2nd query it does not run?
Are there any special characters in your Excel generated list, that feeds the other query?
What are the data type(s) of the Excel columns, are they the same as the object type(s) in BO?
If you can you should, IMO. Personally, I’m not a fan of Excel sheets in a reporting solution.
Purely, a given, alternative if you cannot get this to work any other way…:).
I think I need to explain things a bit more clearly.
Query 1 gets the lists of values from the Excel spreadsheet after the values have been sorted into columns.
Query 2 runs the first set of values (first 5 columns-worth of 950 items each) against the data warehouse and brings back various pieces of information.
Query 3 brings back exactly the same pieces of information from the data warehouse as Query 2 but uses the left over values from Query 1 (last 4 columns-worth of 950 items each but most of these are zero).
If I could find out how to post some sample data from Query 1 I would do, it might make things easier to understand.
This may sound a bit dim, but as the Query 2 works, shouldn’t the Query 3? It’s basically exactly the same query with slightly different conditions (see above).
As far as I can see there are no special characters in the data set from the Excel spreadsheet that feeds the conditions and they are numeric which matches the data format in the query.
::edit::
Possibly a bit of a random question, but as I’m relying on the results of one query to populate the conditions of another, will BOXI run Query 1 first before running Query 2 (to use the layout above)? If not, that could be the cause of the problem.
If BOXI runs the queries in the order the appear in the list regardless, is it possible to reorder the list without rebuilding the entire report?
(BOB member since 2008-02-13)
(BOB member since 2003-11-27)
(BOB member since 2004-03-22)