Show non prompt query condition list of values?

[ainkca]

Hi all,

I'm using BO 5.1.8

Is there a way to print the list of values in my query condition when it's not a user prompt? I'd like to show that list on the report.

I know I could just hard code them on the report but I didn't want to have to keep changing it as revisions are necessary. (likely every few months)

Any suggestions? Thanks for any insight.

 

[skrandel]

Nope. This is not possible. You could probably get clever with substringing the query SQL code to find the conditions.

You won't see anything like this until you get to WebI XIR2 where you can see this information in the Interactive HTML viewer.

Steve Krandel
Symantec

 

[ainkca]

Well, not the answer I was hoping for, but it does answer my question. Thanks so much for your response.

 

[blom0344]

As Steve points out this is not standard functionality, but it is quite doable to present the relevant part of the SQL statement by mixing pos and substr functions

Ties Blom

 

[ainkca]

How would I do it? I understand what you're saying but I don't think I know what syntax to use. Ie, how do I tell it what part of the SQL statement to use? Pardon my for sounding so dumb...

 

[blom0344]

You need the function:

=DataProviderSQL("Query1")

in this case if you rename your dataprovider as Query1.

Now assign this formula to a variable <X> and display it in a cell. [If it throws an error you may need to activate the 'edit connections' settings for the Business Objects module in Supervisor]

The cell should now display the full SQL statement.
Use the pos() function to track down where the 'In' part of the where clause is, like:

= Substr(<X>,Pos(<X>,"In"),(length(<X>-Pos(<X>,"In")))

You may need to tweak a little..

Ties Blom