PDA

View Full Version : WebApp Report - Constrain Count



Niklas Dahlquist
19-Oct-2005, 02:46 AM
Hi,

Instead of <<Previous and Next>> links at the bottom of a report I would
like to have number of pages (Like in Google-search for example).

To accomplish this I need to know how many total found records there is in a
specific constrain.

How can this be done. If I count in the Procedure OnBody I will get the
number of records of the first page, which won´t help much :D

Any ideas?

Kind Regards
Niklas

wila
19-Oct-2005, 03:09 AM
Niklas,

Basically it comes down to running the report twice.
- one time with all the output surpressed so that you can count the
number of "hits" and use that to calculate your page count.
- a second time to actually display the page requested.

This is one of those scenarios where using SQL is so much easier and
more efficient... so if you have the possibility, you might want to use
ESQL for this.

--
Wil

Niklas Dahlquist wrote:
> Hi,
>
> Instead of <<Previous and Next>> links at the bottom of a report I would
> like to have number of pages (Like in Google-search for example).
>
> To accomplish this I need to know how many total found records there is in a
> specific constrain.
>
> How can this be done. If I count in the Procedure OnBody I will get the
> number of records of the first page, which won´t help much :D
>
> Any ideas?
>
> Kind Regards
> Niklas
>
>

Raveen Ryan Sundram
19-Oct-2005, 04:39 PM
Wil,

Could you please explain why SQL would be better for this scenerio? Could it
return the total records in the constrain?

Raveen


"Wil van Antwerpen" <info@antwise.com> wrote in message
news:iTHOfRI1FHA.2056@dacmail.dataaccess.com...
> Niklas,
>
> Basically it comes down to running the report twice.
> - one time with all the output surpressed so that you can count the number
> of "hits" and use that to calculate your page count.
> - a second time to actually display the page requested.
>
> This is one of those scenarios where using SQL is so much easier and more
> efficient... so if you have the possibility, you might want to use ESQL
> for this.
>
> --
> Wil
>
> Niklas Dahlquist wrote:
>> Hi,
>>
>> Instead of <<Previous and Next>> links at the bottom of a report I would
>> like to have number of pages (Like in Google-search for example).
>>
>> To accomplish this I need to know how many total found records there is
>> in a
>> specific constrain.
>>
>> How can this be done. If I count in the Procedure OnBody I will get the
>> number of records of the first page, which won´t help much :D
>>
>> Any ideas?
>>
>> Kind Regards
>> Niklas
>>

wila
20-Oct-2005, 01:59 AM
Hi Raveen,

It's just more natural.

Instead of having to run the same report twice you can query for the
rowcount in one go. Just run the query statement, fetch your result and
you're done. The closest example in VDF code would be using a for_all
loop with a single line to increment your counter.

Then if you run the report afterwards, the resultset is still cached in
most modern SQL servers, which makes a big difference.
Also SQL comes with natural delimiters like the SET ROWCOUNT or TOP to
delimit the resultset.
This way you can make sure that for example only the first 10 pages of
results are returned as most web users will not look much further as the
first pages in the result anyways.

In addition one normally doesn't run his SQL server on the same machine
as a webapp. So if you have a large database this will make a big
difference as all database related work is being done on another machine.

For more ideas and a more extensive discussion of this problem have a
look here:
http://www.aspfaq.com/show.asp?id=2120

--
Wil

Raveen Ryan Sundram wrote:
> Wil,
>
> Could you please explain why SQL would be better for this scenerio? Could it
> return the total records in the constrain?
>
> Raveen
>
>
> "Wil van Antwerpen" <info@antwise.com> wrote in message
> news:iTHOfRI1FHA.2056@dacmail.dataaccess.com...
>
>>Niklas,
>>
>>Basically it comes down to running the report twice.
>>- one time with all the output surpressed so that you can count the number
>>of "hits" and use that to calculate your page count.
>>- a second time to actually display the page requested.
>>
>>This is one of those scenarios where using SQL is so much easier and more
>>efficient... so if you have the possibility, you might want to use ESQL
>>for this.
>>
>>--
>>Wil
>>
>>Niklas Dahlquist wrote:
>>
>>>Hi,
>>>
>>>Instead of <<Previous and Next>> links at the bottom of a report I would
>>>like to have number of pages (Like in Google-search for example).
>>>
>>>To accomplish this I need to know how many total found records there is
>>>in a
>>>specific constrain.
>>>
>>>How can this be done. If I count in the Procedure OnBody I will get the
>>>number of records of the first page, which won´t help much :D
>>>
>>>Any ideas?
>>>
>>>Kind Regards
>>>Niklas
>>>
>
>

Raveen Ryan Sundram
20-Oct-2005, 02:15 PM
Thanks Wil


"Wil van Antwerpen" <info@antwise.com> wrote in message
news:FECQfPU1FHA.5020@dacmail.dataaccess.com...
> Hi Raveen,
>
> It's just more natural.
>
> Instead of having to run the same report twice you can query for the
> rowcount in one go. Just run the query statement, fetch your result and
> you're done. The closest example in VDF code would be using a for_all loop
> with a single line to increment your counter.
>
> Then if you run the report afterwards, the resultset is still cached in
> most modern SQL servers, which makes a big difference.
> Also SQL comes with natural delimiters like the SET ROWCOUNT or TOP to
> delimit the resultset.
> This way you can make sure that for example only the first 10 pages of
> results are returned as most web users will not look much further as the
> first pages in the result anyways.
>
> In addition one normally doesn't run his SQL server on the same machine as
> a webapp. So if you have a large database this will make a big difference
> as all database related work is being done on another machine.
>
> For more ideas and a more extensive discussion of this problem have a look
> here:
> http://www.aspfaq.com/show.asp?id=2120
>
> --
> Wil
>
> Raveen Ryan Sundram wrote:
>> Wil,
>>
>> Could you please explain why SQL would be better for this scenerio? Could
>> it return the total records in the constrain?
>>
>> Raveen
>>
>>
>> "Wil van Antwerpen" <info@antwise.com> wrote in message
>> news:iTHOfRI1FHA.2056@dacmail.dataaccess.com...
>>
>>>Niklas,
>>>
>>>Basically it comes down to running the report twice.
>>>- one time with all the output surpressed so that you can count the
>>>number of "hits" and use that to calculate your page count.
>>>- a second time to actually display the page requested.
>>>
>>>This is one of those scenarios where using SQL is so much easier and more
>>>efficient... so if you have the possibility, you might want to use ESQL
>>>for this.
>>>
>>>--
>>>Wil
>>>
>>>Niklas Dahlquist wrote:
>>>
>>>>Hi,
>>>>
>>>>Instead of <<Previous and Next>> links at the bottom of a report I would
>>>>like to have number of pages (Like in Google-search for example).
>>>>
>>>>To accomplish this I need to know how many total found records there is
>>>>in a
>>>>specific constrain.
>>>>
>>>>How can this be done. If I count in the Procedure OnBody I will get the
>>>>number of records of the first page, which won´t help much :D
>>>>
>>>>Any ideas?
>>>>
>>>>Kind Regards
>>>>Niklas
>>>>
>>