I am running into a problem with one of my reports and I am hoping I may be able to get some advice about how to approach it.
First off, I am using Business Objects v. 5.1.4. I currently have a report with 5 data providers and I have need to apply a filter behind the scenes in a VB macro to only two of these data providers. I have written a VB macro for a previous report that uses the AddComplexFilter to apply a filter to an entire report when it is refreshed, but this will not work in this case because the filter I added will cause the information from the other three data providers to disappear from the report.
So my question is: Is there a way to apply a filter to a data provider rather than a report, either using VB or other methods?
(This all makes sense in my head, but it might not in anyone else’s so let me know if I need to clarify anything. :))
I am using information from one data provider to filter the information on the OTHER data provider. (As far as I know, I cannot apply a condition on the query of one data provider using information returned from another data provider. Or can I?)
Let’s say that I have variable A in data provider 1 and variable B in data provider 2. Variable B is a subset of the values in variable A. I need to filter records from data provider 1 that have variable A = variable B.
I have done this in the past by running a VB macro that collects all of the values in variable B into a string (i.e. “1, 2, 3”). I then use the AddComplexFilter function like this:
Call rep.AddComplexFilter("Variable A", "<Variable A> = In List (1, 2, 3)"
This will give me the results that I want when the report only contains the two data providers, but when I try to add other data providers to the report that do not include variables A or B, filtering the report this way does not quite work.
It is true that you can only create global filters with VBA. You can’t apply a filter to a table with VBA … directly. But you can do it indirectly. Create a report variable named with a formula like ==“X”. On the table, add a filter on , but instead of picking values from the list, click the Define button and write a formula that says =. Now you can use VBA to change the formula behind .
To set filter: ThisDocument.DocumentVariables("MyFilter").Formula = "=<FilterField>=""X"""
To remove filter: ThisDocument.DocumentVariables("MyFilter").Formula = "=(1=1)"
[Sorry I have not replied in a more timely manner. I have been out of the office.]
Dwayne, what you are suggesting that I do I have done already through VB code, I believe. Unfortunately, the filter is applied to the entire report. When you say add the filter to the table, I do so, but there is only one table in my report so it is applied to the whole report.
That helps me to better understand your question. So, the five data providers are linked on some number of common dimensions, correct? And because of the “full outer join” behavior of linked data providers, you are getting too many records in the table, correct? The trick to eliminate records that don’t match, is to add a “Not IsNull()” filter to one non-linked field from each appropriate data provider. Take a look at this page, specifically the two presentations by Steve Bickerton … excellent resources.
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)
(BOB member since 2002-09-19)
(BOB member since 2002-09-19)