Only returning Rows from one data source

I have two data providers. I have a report that has two columns - Movie and order count. This report has 500 rows in it.

I have a spreadsheet with two columns - Movie and sales. This spreadsheet has 5 movies listed.

I want to create a report that lists the movies from my spreadsheet with their sales and their orders.

I have created the spreadsheet as a data provider and I linked them by movie. However, whenever I bring in Movie I get 505 rows, rather than just my 5 rows. Any ideas on how to get only my 5 rows back? I cannot put the spreadsheet in BO becuase it comes from an external system.


khansen97 (BOB member since 2003-12-05)

Assuming the first data provider is universe-based, there is a perfect solution in v6.1. You can use the results of one data provider (your spreadsheet) as the condition for the other. That way you only get back the five records you need. After selecting In List, there is an option to choose another data provider.

The other solution is to filter the nulls from the “larger” data provider. Create a variable called with the formula = If IsNull() Then 1 Else 0, place in the report block and hide it, then filter so that only 0 is displayed.


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

The first solution seemed to work. However when I run the report I get an error:

Any ideas of why this would be happening?


khansen97 (BOB member since 2003-12-05)

I found that the object I am trying to do the filter on is a character, while the column inExcel is listed as numeric. Is there anyway in BO to change this column to character?


khansen97 (BOB member since 2003-12-05)

Excel personal data providers do cause problems like this from time to time. BusObj (more than likely it’s ADO underneath), makes “bad choices” on data types in some cases. The typical “bad choice” is a character field that only contains numbers … BusObj makes it numeric. Assuming this is your problem, the solution is to “trick” BusObj into making the right choices.

The trick is to “dummy up” an Excel file with the correct datatypes. Don’t use real data. Put characters (ABC) in the columns that should be character, numbers (123) in columns that should be numeric, and so forth. Now when you create the personal data provider, it will make the correct choices. You may need to delete and recreate the data provider, if I remember correctly. Finally, go back and put your real data in the spreadsheet, and it should work.


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