Business Objects 5 - Conditions Over 2 Data Providers 2

[SHardy]

I have a BO report with two data providers. The first is a BO query, with a condition included that asks the user to select one or more "region codes". The second data provider is a freehand SQL query.

Is there a way that I can apply the same region code(s) as a condition in the SQL provider.

As I currently see it, it would appear that the SQL will only allow a single code to be selected. Therefore, curerently, the SQL appears to only allow one or all codes.

Any help would be greatly appreciated.

Thank you,
Simon

 

[blom0344]

Please get rid of freehand SQL dataproviders in a production BO document.
Freehand SQL is there for developers to allow for testing and quick shortcuts.
What you are after requires universe-based DP's.

Ties Blom
Information analyst

 

[skrandel]

First, I agree with blom on this one 100%.

There seems to be an issue with Freehand SQL and multiple selections. However, we can't tell this unless we see your code.

Steve Krandel
Symantec

 

[SHardy]

Hi,

Sorry I didn't get back sooner.

As far as I can see, a freehand SQL provider is required. My reason is that my report is using a crosstab. I want all possible field values to be returned across the page as headings/ columns even if unused by some subjects.

I do not want the Universe to have OUTER joins built in just for the purpose of this report. I am sure that this would then cause problems else where.

An example of what I am trying to do:

If my report had the following sections defined - Region, Salesperson

and was reporting Product Code by Campaign Type in a cross tab, with Product Code down the side, and Campaign Type as columns across the page.

I would not want the width of the table to be changing from page to page. To prevent this I would have an SQL provider that returns all possible Campaign Types, all reported salespeople and all reported regions.

I would then link these fields to the BO query.

In the report design, I would use the SQL Campaign Type field in the crosstab. This would then force all columns to show for all salespeople, regardless of data being present or not.


This explains why I am using Freehand SQL as a provider, but the original problem still stands. When taking a variable from the BO Query across to the SQL I am only able to select a single value as a condition.

Is there any way around this???

Ta

 

[blom0344]

The Nature of a crosstab is that it scales according to the combinations in the report. (As you found out)

What could work in this case is not to define a crosstab , but a table with fixed columns:

The column variables would read like:

=<measure> Where (<Campaign Type> = "Abacadabra")

You would need to define a set of variables this way, but the table would have a fixed width (even if a column stays empty)

Drawback is that you need to know which Campaign Types to cover..

Ties Blom
Information analyst

 

[skrandel]

Let's answer your original question. If you have a prompt, then you can have the EXACT same prompt text in the Free-Hand SQL. If the text is EXACTLY the same, then the prompt will only appear one time and the value will be used in both places.

Steve Krandel
Symantec

 

[SHardy]

Hi,

Thanks for your valuable comments.

Certainly, for crosstabs the proposed solution would work. May be a bit of a pain if a large number of columns, but certainly a much better way of approaching it.

However, when I have gone back to the report that sparked the question, it was not a crosstab, but the requirements are a lot more complex. As SQL providers will only accept a single value, I will have to keep separate reports for each region.

Thank you both.

 

[blom0344]

A wild thought...

Would it work to use the SQL dataproviders to fetch a cartesian product between the dimensions?
Obviously, this is usually avoided, but it would get the all combinations possible (without even the need for a prompt)
Technically you would write an SQL without joining the tables.

As I said, a pretty wild one...

Ties Blom
Information analyst