providing list of values in freehand SQL

I have a report using freehand SQL as the datasource. I am using BO 4.0.5 and SQL Server6.5. I want to provide the users with a LOV in the prompt for a given field. On a previous report I used the following syntax which worked:

create table #ba (baID int, baName char(32))

insert #ba VALUES(0, ‘xyz’)
… .

select @busa = (select distinct baID from #ba where baName = @variable(‘what Business Area’)

Now, I try the following which does not provide values:

select distinct ptmscontactid, contactname into #dm from et, ct
where ct.ci = et.ptci

select @dm = (select distinct ptci from #dm where cn = @variable(‘Enter dm’))

What am I missing here?

Thanks,
Michael


Listserv Archives (BOB member since 2002-06-25)

Michael,

It’s not what you’re missing, it’s what
BusinessObjects is missing. Freehand SQL is not a recognized by BusinessObjects as being part of a universe and thus, you can not use LOV’s defined in a universe.

If the LOV is to contain a static list then you could build the list using the @prompt command. This will allow the Values button to be highlighted when they enter the prompt.

If you wish a dynamic LOV, like what is
available “inside” BusinessObjects, then at least in 4.1, you have to create it yourself using ReportScript.

You’ll have to create your own prompt screen and a method for selecting multiple values, if applicable. (4.1 ReportScript, does not allow selecting multiple values from a list. ) You’ll need to build the query to return you the results, then filter them since there is no way I’ve seen to return distinct values using BOQuery.

You can call a script from freehand SQL using the @script command. Don’t use the on-line example from BusinessObjects, it won’t work. Search the archives of this list and you’ll see an example that does work.

A dynamic LOV is something I’ve been working, as time permits, for the past few weeks. I’m about 60% there and have hit several walls and with nothing but the vague manual to work with, it’s been rough solving problems.

When I’m done, it won’t look anything like the “normal” prompt a user will see but it will work … hopefully.

Best of Luck!
Jim Droppa
Source Consulting

On Wed, 4 Nov 1998 08:17:22 -0600, Michael Malone wrote:

I have a report using freehand SQL as the datasource. I am using BO 4.0.5 and SQL
Server6.5. I want to provide the users with a LOV in the prompt for a given field. On a previous report I used the following syntax which worked:

create table #ba (baID int, baName char(32))

insert #ba VALUES(0, ‘xyz’)
… .

select @busa = (select distinct baID from #ba
where baName =
@variable(‘what Business Area’)

Now, I try the following which does not provide values:

select distinct ptmscontactid,
contactname into #dm from et, ct
where ct.ci = et.ptci

select @dm = (select distinct ptci from #dm where cn =
@variable(‘Enter dm’))

What am I missing here?

Thanks,
Michael

_______________________________________________________ Get your free, private e-mail at http://mail.excite.com/


Listserv Archives (BOB member since 2002-06-25)