Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Set a prompt based on a calculation?

6 posts in PB Infomaker Report Writer Last posting was on 1999-05-13 18:35:03.0Z
Scott Lemasters Posted on 1999-05-09 06:52:36.0Z
Newsgroups: sybase.public.infomaker.general
From: "Scott Lemasters" <georgehills2@worldnet.att.net>
Subject: Set a prompt based on a calculation?
Date: Sat, 8 May 1999 23:52:36 -0700
Lines: 11
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_9jPihhem#GA.264@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6362
Article PK: 241351

Can i set a prompt on a calculated field? The DB doesn't contain a column
that sums two particular columns, so in a report, it simply calculates the
sum in both the detail and summary sections. I would like to set a prompt
so the user can input a certain dollar value, so it returns records where
the sum of these two particular columns exceeds the user defined dollar
value.

Without amending the DB, can I set such a prompt, or anything else, where
the user can define a criteria based on a calculated column?


Ewa MN. Posted on 1999-05-13 14:02:29.0Z
Newsgroups: sybase.public.infomaker.general
Date: Thu, 13 May 1999 10:02:29 -0400
From: "Ewa MN." <knowacz_NoSpam@Angelfire.com>
Reply-To: knowacz@angelfire.com
Organization: somewhere in the bush
X-Mailer: Mozilla 3.0Gold (Win95; I)
MIME-Version: 1.0
Subject: Re: Set a prompt based on a calculation?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 36
Message-ID: <348_373ADB75.2247@Angelfire.com>
References: <348_9jPihhem#GA.264@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6333
Article PK: 241318


Scott Lemasters wrote:
>
> Can i set a prompt on a calculated field? The DB doesn't contain a column
> that sums two particular columns, so in a report, it simply calculates the
> sum in both the detail and summary sections. I would like to set a prompt
> so the user can input a certain dollar value, so it returns records where
> the sum of these two particular columns exceeds the user defined dollar
> value.
>
> Without amending the DB, can I set such a prompt, or anything else, where
> the user can define a criteria based on a calculated column?

Hi there,
I think it is possible to do, however it may affect performance in case
of large database. You may investigate the following.
IN SYBASE and IN ORACLE the same works: you may use the expression
itself on the left-hand side of your "where" clause.
Say you have two columns: sales_june and sales_july and you want to
prompt user so he/she could enter total of the two months as retrieval
parameter.
In SQL you may define your retrieval argument say :tot2month and then
you will use on the left-hand side of "Where" statement: .... where
(sales_june + sales_july) >= :tot2month.

Then if user will get prompt and enter the value, the rows retrieved
will be only these with the total of two columns >= the inintial value.
In case you'd like to get all records, just enter 0.
HTH and good luck!

Regards,
Eva


Terry Dykstra Posted on 1999-05-13 15:04:53.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Set a prompt based on a calculation?
Date: Thu, 13 May 1999 09:04:53 -0600
Lines: 43
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_c4ZToFVn#GA.298@forums.sybase.com>
References: <348_9jPihhem#GA.264@forums.sybase.com> <348_373ADB75.2247@Angelfire.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6331
Article PK: 241316

The issue is whether you can prompt based on a calculation in the report,
not the SQL. And then you cannot do it, as I mentioned in an earlier post.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.

Ewa MN. wrote in message <373ADB75.2247@Angelfire.com>...
>Scott Lemasters wrote:
>>
>> Can i set a prompt on a calculated field? The DB doesn't contain a
column
>> that sums two particular columns, so in a report, it simply calculates
the
>> sum in both the detail and summary sections. I would like to set a
prompt
>> so the user can input a certain dollar value, so it returns records where
>> the sum of these two particular columns exceeds the user defined dollar
>> value.
>>
>> Without amending the DB, can I set such a prompt, or anything else, where
>> the user can define a criteria based on a calculated column?
>
>Hi there,
>I think it is possible to do, however it may affect performance in case
>of large database. You may investigate the following.
>IN SYBASE and IN ORACLE the same works: you may use the expression
>itself on the left-hand side of your "where" clause.
>Say you have two columns: sales_june and sales_july and you want to
>prompt user so he/she could enter total of the two months as retrieval
>parameter.
>In SQL you may define your retrieval argument say :tot2month and then
>you will use on the left-hand side of "Where" statement: .... where
>(sales_june + sales_july) >= :tot2month.
>
>Then if user will get prompt and enter the value, the rows retrieved
>will be only these with the total of two columns >= the inintial value.
>In case you'd like to get all records, just enter 0.
>HTH and good luck!
>
>Regards,
>Eva


Ewa MN. Posted on 1999-05-13 18:08:52.0Z
Newsgroups: sybase.public.infomaker.general
Date: Thu, 13 May 1999 14:08:52 -0400
From: "Ewa MN." <knowacz_NoSpam@Angelfire.com>
Reply-To: knowacz@angelfire.com
Organization: somewhere in the bush
X-Mailer: Mozilla 3.0Gold (Win95; I)
MIME-Version: 1.0
Subject: Re: Set a prompt based on a calculation?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 13
Message-ID: <348_373B1534.3CF7@Angelfire.com>
References: <348_9jPihhem#GA.264@forums.sybase.com> <348_373ADB75.2247@Angelfire.com> <348_c4ZToFVn#GA.298@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6327
Article PK: 241312


Terry Dykstra wrote:
>
> The issue is whether you can prompt based on a calculation in the report,
> not the SQL. And then you cannot do it, as I mentioned in an earlier post.
>
> --

Hello,
Yes, of course I agree with you.
But in case such thing is not possible from report design view and sql
can do it, I can't see the reason why can't you use this approach - as
long of course as it will not cause lot of computations on the server's
site.
Regards,
Eva Maciasz


Terry Dykstra Posted on 1999-05-13 18:35:03.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Set a prompt based on a calculation?
Date: Thu, 13 May 1999 12:35:03 -0600
Lines: 27
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_i$pJE7Wn#GA.277@forums.sybase.com>
References: <348_9jPihhem#GA.264@forums.sybase.com> <348_373ADB75.2247@Angelfire.com> <348_c4ZToFVn#GA.298@forums.sybase.com> <348_373B1534.3CF7@Angelfire.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6325
Article PK: 241310

It's a very good method. I use it myself quite often. I actually prefer to
do it in SQL, because it just reduces the number of rows sent over the
network. But if it kills the server, then you probably have no choice but
doing it on the PC, provided it can handle that many rows.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.

Ewa MN. wrote in message <373B1534.3CF7@Angelfire.com>...
>Terry Dykstra wrote:
>>
>> The issue is whether you can prompt based on a calculation in the report,
>> not the SQL. And then you cannot do it, as I mentioned in an earlier
post.
>>
>> --
>
>Hello,
>Yes, of course I agree with you.
>But in case such thing is not possible from report design view and sql
>can do it, I can't see the reason why can't you use this approach - as
>long of course as it will not cause lot of computations on the server's
>site.
>Regards,
>Eva Maciasz


Terry Dykstra Posted on 1999-05-10 16:44:12.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Set a prompt based on a calculation?
Date: Mon, 10 May 1999 10:44:12 -0600
Lines: 24
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_0DUQSPwm#GA.264@forums.sybase.com>
References: <348_9jPihhem#GA.264@forums.sybase.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.infomaker.general:6355
Article PK: 241340

No. But you can set a filter based on the calculated value.
The real problem is that you cannot hardcode a filter based on the computed
field that contains the sum. There is a known bug in that the retrieve will
work, but the screen will be blank. So you are forced to put in the filter
after the initial retrieve. If this bug didn't exist, you could create a
dummy retrieval argument and use it as part of the filter expression.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.

Scott Lemasters wrote in message <9jPihhem#GA.264@forums.sybase.com>...
>Can i set a prompt on a calculated field? The DB doesn't contain a column
>that sums two particular columns, so in a report, it simply calculates the
>sum in both the detail and summary sections. I would like to set a prompt
>so the user can input a certain dollar value, so it returns records where
>the sum of these two particular columns exceeds the user defined dollar
>value.
>
>Without amending the DB, can I set such a prompt, or anything else, where
>the user can define a criteria based on a calculated column?
>
>