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.

Detecting first or last row of group

6 posts in PB Infomaker Report Writer Last posting was on 2008-07-23 22:51:23.0Z
LarryB Posted on 1999-05-26 19:30:51.0Z
Newsgroups: sybase.public.infomaker.general
From: "LarryB" <beifeld@ibm.net>
Subject: Detecting first or last row of group
Date: Wed, 26 May 1999 15:30:51 -0400
Lines: 13
Organization: Wyeth
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_QRNuD26p#GA.206@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:6283
Article PK: 241269

Is there a way to dectect if the current detail row in a grouped report is
the first or last row in that group??. I want to call a nested report after
the last row of each group.

The autosize of a nested report only works in the detail band and not the
group summary band. Any help would be appreciated.

I know how to get current row and how many rows for the group but that does
not seem to help me.

Thanx


John Burgess Posted on 1999-05-27 09:25:34.0Z
Newsgroups: sybase.public.infomaker.general
From: "John Burgess" <john@riskdecisions.co.uk>
Subject: Re: Detecting first or last row of group
Date: Thu, 27 May 1999 10:25:34 +0100
Lines: 22
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_COWy$JCq#GA.206@forums.sybase.com>
References: <348_QRNuD26p#GA.206@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:6281
Article PK: 241268

If you have a unique id column (and if you don't, you can add an invisible
one) then
you can use an expression such as
if( idcol = last( idcol for group n), this val, that val )

LarryB wrote in message ...
>Is there a way to dectect if the current detail row in a grouped report is
>the first or last row in that group??. I want to call a nested report after
>the last row of each group.
>
>The autosize of a nested report only works in the detail band and not the
>group summary band. Any help would be appreciated.
>
>I know how to get current row and how many rows for the group but that does
>not seem to help me.
>
>Thanx
>
>


Toby Fruth Posted on 2008-07-23 22:38:27.0Z
From: Toby Fruth <toby@tageandtoby.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.infomaker.general
Subject: Re: Detecting first or last row of group
References: <348_QRNuD26p#GA.206@forums.sybase.com> <348_COWy$JCq#GA.206@forums.sybase.com>
In-Reply-To: <348_COWy$JCq#GA.206@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4887b2e3$1@forums-1-dub>
Date: 23 Jul 2008 15:38:27 -0700
X-Trace: forums-1-dub 1216852707 10.22.241.152 (23 Jul 2008 15:38:27 -0700)
X-Original-Trace: 23 Jul 2008 15:38:27 -0700, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.infomaker.general:12433
Article PK: 232633

I realize this thread is nine years old, but I would like to see if
anyone has taken this to the next step - analysis of data in first row
of group compared to data in the last row.

In our project management software, the contractor submits RFI
questions. Following RFI's that are directly related to the original
receive the same number, but with a .1, .2, etc., added to the end. For
example, RFI 00001 is a question about tile for a lobby, and is followed
by RFI 00001.1, asking a related question.

Using the suggested syntax below, I can pick up the first row in the
group and the last row in the group.

Each RFI question has a date, the question date. Each RFI answer has an
answer date, the responded_date.

Let's say RFI 00001 has a question date of 2007-01-01 and a
responded_date of 2007-01-05. That's ten days. If there are ten
revisions and the last revision, 00001.10 has a responded date of
2007-01-30, then the delta between the original RFI question_date and
the last revision responded_date is 30 days.

I want to calculate that delta, but I don't know what syntax to use.

I created a couple of computed fields to generate a value that
identifies the first and last rows for each RFI grouping. They look like:

Name of field is compute_30 and it equals 1 for the original RFI:
if ( change_number = first( change_number for group 2 ), 1, 0)

Name of field is compute_29 and it equals one for the last revision of
an RFI:
if ( change_number = last( change_number for group 2 ), 1, 0)


So now, I want the daysafter (question_date, responded_date) where
compute_30 =1 and compute_29 = 1, where the question_date comes from the
original RFI and the responded_date is from the last revision.

Any thoughts?

I tried the following, but it doesn't yield the output I seek:

daysafter( if( compute_30 = 1, change_date , today()), if( compute_29 =
1, responded_date , today()) )

John Burgess wrote:
> If you have a unique id column (and if you don't, you can add an invisible
> one) then
> you can use an expression such as
> if( idcol = last( idcol for group n), this val, that val )
>
>
> LarryB wrote in message ...
>> Is there a way to dectect if the current detail row in a grouped report is
>> the first or last row in that group??. I want to call a nested report after
>> the last row of each group.
>>
>> The autosize of a nested report only works in the detail band and not the
>> group summary band. Any help would be appreciated.
>>
>> I know how to get current row and how many rows for the group but that does
>> not seem to help me.
>>
>> Thanx
>>
>>
>
>


Toby Fruth Posted on 2008-07-23 22:51:23.0Z
From: Toby Fruth <toby@tageandtoby.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.infomaker.general
Subject: Re: Detecting first or last row of group
References: <348_QRNuD26p#GA.206@forums.sybase.com> <348_COWy$JCq#GA.206@forums.sybase.com> <4887b2e3$1@forums-1-dub>
In-Reply-To: <4887b2e3$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4887b5eb@forums-1-dub>
Date: 23 Jul 2008 15:51:23 -0700
X-Trace: forums-1-dub 1216853483 10.22.241.152 (23 Jul 2008 15:51:23 -0700)
X-Original-Trace: 23 Jul 2008 15:51:23 -0700, vip152.sybase.com
Lines: 95
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.infomaker.general:12434
Article PK: 232634

I may have answered my own question. Since I'm grouping on
left(question_date, 5) in order to capture the original RFI number and
all revisions, and sorting by the RFI number, I get rows that look like
this:

RFI # question_date answer_date

00001 2007-01-01 2007-01-05
00001.1 2007-01-06 2007-01-10

The delta between the first question and the last response is calculated
thusly:

daysafter(first( change_date for group 2 ), last( responded_date
for group 2 ) )

In my report there is a group 1 that groups RFI's by the phase of the
project, hence the use of group 2. Replace with applicable group, or
all in case of summary at report end.

Toby Fruth wrote:
> I realize this thread is nine years old, but I would like to see if
> anyone has taken this to the next step - analysis of data in first row
> of group compared to data in the last row.
>
> In our project management software, the contractor submits RFI
> questions. Following RFI's that are directly related to the original
> receive the same number, but with a .1, .2, etc., added to the end. For
> example, RFI 00001 is a question about tile for a lobby, and is followed
> by RFI 00001.1, asking a related question.
>
> Using the suggested syntax below, I can pick up the first row in the
> group and the last row in the group.
>
> Each RFI question has a date, the question date. Each RFI answer has an
> answer date, the responded_date.
>
> Let's say RFI 00001 has a question date of 2007-01-01 and a
> responded_date of 2007-01-05. That's ten days. If there are ten
> revisions and the last revision, 00001.10 has a responded date of
> 2007-01-30, then the delta between the original RFI question_date and
> the last revision responded_date is 30 days.
>
> I want to calculate that delta, but I don't know what syntax to use.
>
> I created a couple of computed fields to generate a value that
> identifies the first and last rows for each RFI grouping. They look like:
>
> Name of field is compute_30 and it equals 1 for the original RFI:
> if ( change_number = first( change_number for group 2 ), 1, 0)
>
> Name of field is compute_29 and it equals one for the last revision of
> an RFI:
> if ( change_number = last( change_number for group 2 ), 1, 0)
>
>
> So now, I want the daysafter (question_date, responded_date) where
> compute_30 =1 and compute_29 = 1, where the question_date comes from the
> original RFI and the responded_date is from the last revision.
>
> Any thoughts?
>
> I tried the following, but it doesn't yield the output I seek:
>
> daysafter( if( compute_30 = 1, change_date , today()), if( compute_29 =
> 1, responded_date , today()) )
>
> John Burgess wrote:
>> If you have a unique id column (and if you don't, you can add an
>> invisible
>> one) then
>> you can use an expression such as
>> if( idcol = last( idcol for group n), this val, that val )
>>
>>
>> LarryB wrote in message ...
>>> Is there a way to dectect if the current detail row in a grouped
>>> report is
>>> the first or last row in that group??. I want to call a nested report
>>> after
>>> the last row of each group.
>>>
>>> The autosize of a nested report only works in the detail band and not
>>> the
>>> group summary band. Any help would be appreciated.
>>>
>>> I know how to get current row and how many rows for the group but
>>> that does
>>> not seem to help me.
>>>
>>> Thanx
>>>
>>>
>>
>>


l Posted on 1999-05-27 15:29:52.0Z
Newsgroups: sybase.public.infomaker.general
From: "l" <me@netcom.com>
Subject: Re: Detecting first or last row of group
Date: Thu, 27 May 1999 11:29:52 -0400
Lines: 34
Organization: b
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_nR7U3UFq#GA.274@forums.sybase.com>
References: <348_QRNuD26p#GA.206@forums.sybase.com> <348_COWy$JCq#GA.206@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:6276
Article PK: 241262

John;

Thanks. That worked. Why didn't I think of that??? ;-)

-Larry

John Burgess wrote in message ...
>If you have a unique id column (and if you don't, you can add an invisible
>one) then
>you can use an expression such as
>if( idcol = last( idcol for group n), this val, that val )
>
>
>LarryB wrote in message ...
>>Is there a way to dectect if the current detail row in a grouped report is
>>the first or last row in that group??. I want to call a nested report
after
>>the last row of each group.
>>
>>The autosize of a nested report only works in the detail band and not the
>>group summary band. Any help would be appreciated.
>>
>>I know how to get current row and how many rows for the group but that
does
>>not seem to help me.
>>
>>Thanx
>>
>>
>
>


Terry Dykstra Posted on 1999-05-27 14:42:01.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra" <tdykstra@cfol.ab.ca>
Subject: Re: Detecting first or last row of group
Date: Thu, 27 May 1999 08:42:01 -0600
Lines: 34
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <348_UaHOn5Eq#GA.51@forums.sybase.com>
References: <348_QRNuD26p#GA.206@forums.sybase.com> <348_COWy$JCq#GA.206@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:6279
Article PK: 241263

Another option is to check the columns that define the group break:
if ( mycolumn[1] = mycolumn, 0, 1)
will be 1 on the last row of the group, 0 for the other rows.

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

John Burgess wrote in message ...
>If you have a unique id column (and if you don't, you can add an invisible
>one) then
>you can use an expression such as
>if( idcol = last( idcol for group n), this val, that val )
>
>
>LarryB wrote in message ...
>>Is there a way to dectect if the current detail row in a grouped report is
>>the first or last row in that group??. I want to call a nested report
after
>>the last row of each group.
>>
>>The autosize of a nested report only works in the detail band and not the
>>group summary band. Any help would be appreciated.
>>
>>I know how to get current row and how many rows for the group but that
does
>>not seem to help me.
>>
>>Thanx
>>
>>
>
>