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.

Datawindow Sort / GroupCalc Issue

12 posts in DataWindow Last posting was on 2008-12-10 17:47:34.0Z
M. Burdette Posted on 2008-12-08 16:52:56.0Z
Sender: 2829.493d4889.1804289383@sybase.com
From: M. Burdette
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Datawindow Sort / GroupCalc Issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <493d50e8.2968.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub493d50e8"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Dec 2008 08:52:56 -0800
X-Trace: forums-1-dub 1228755176 10.22.241.41 (8 Dec 2008 08:52:56 -0800)
X-Original-Trace: 8 Dec 2008 08:52:56 -0800, 10.22.241.41
Lines: 689
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88335
Article PK: 417591

Good Morning,
I am using PB10.5 Build 4523. Connected to SQL Server 2000
via PB Microsoft OLE DB.

I have built a simple region, territory sales report. The
report is grouped by region. The group is in the datawindow
as well as the sql.

The issue I am having occurs when I change the sort order in
script, if the sort causes the first row in the first group
to move to another position it will add a group trailer row
to the first row and then the rest of the rows in the group
will get a trailer row at the bottom of the group as they
should. The totals in both group trailers are the same.
There is only one group header row for the entire group.

Hopefully the attachment makes it clear what is happening.

Once this occurs, no amount of sorts or groupcalcs will
restore the split row. It is always the first row of the
first group that splits off. The value that is being
grouped on (region) is always the first thing in the sort
string, and it is in the sql as well.

Also, once it splits in a report will multiple regions, if I
reverse the sort on the region, whatever region takes over
as the first group has its first territory split out.

I have stripped this report down to just its bare bones, no
text, no computed fields, no line objects. I have tried on
other machines as well, and it still occurs. Doesn't matter
if it is in the development environment or as an executable
app.

I and the other programmers in my group have been having
this issue more often now the we use 10.5 for most new
development. However, this would also happen on occasion in
PB6.5 as well.

I have searched around the groups and not found anything
that really help, any help provide will be greatly
appreciated.

Thanks,
Mike

rpt_grp_err.bmp

"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-12-08 18:09:06.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <493d50e8.2968.1681692777@sybase.com>
Subject: Re: Datawindow Sort / GroupCalc Issue
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <493d62c2@forums-1-dub>
Date: 8 Dec 2008 10:09:06 -0800
X-Trace: forums-1-dub 1228759746 10.22.241.152 (8 Dec 2008 10:09:06 -0800)
X-Original-Trace: 8 Dec 2008 10:09:06 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88336
Article PK: 417589

If you have GROUP BY in the SQL, that would suggest you are already summing
by region, and that's what you are seeing. You want to do that in either the
DW or the SQL, not both. If you sort by region in the SQL that will put the
DW in the initial order you want.
The usual way to do this would be computed fields in the group trailer, Sum
(current_mtd for group 1) and Sum (prior_mtd for group 1), which would give
you the results you are looking for after the DW has been resorted by
region. It might also need a GroupCalc after the Sort().

<M. Burdette> wrote in message news:493d50e8.2968.1681692777@sybase.com...
> Good Morning,
> I am using PB10.5 Build 4523. Connected to SQL Server 2000
> via PB Microsoft OLE DB.
>
> I have built a simple region, territory sales report. The
> report is grouped by region. The group is in the datawindow
> as well as the sql.
>
> The issue I am having occurs when I change the sort order in
> script, if the sort causes the first row in the first group
> to move to another position it will add a group trailer row
> to the first row and then the rest of the rows in the group
> will get a trailer row at the bottom of the group as they
> should. The totals in both group trailers are the same.
> There is only one group header row for the entire group.
>
> Hopefully the attachment makes it clear what is happening.
>
> Once this occurs, no amount of sorts or groupcalcs will
> restore the split row. It is always the first row of the
> first group that splits off. The value that is being
> grouped on (region) is always the first thing in the sort
> string, and it is in the sql as well.
>
> Also, once it splits in a report will multiple regions, if I
> reverse the sort on the region, whatever region takes over
> as the first group has its first territory split out.
>
> I have stripped this report down to just its bare bones, no
> text, no computed fields, no line objects. I have tried on
> other machines as well, and it still occurs. Doesn't matter
> if it is in the development environment or as an executable
> app.
>
> I and the other programmers in my group have been having
> this issue more often now the we use 10.5 for most new
> development. However, this would also happen on occasion in
> PB6.5 as well.
>
> I have searched around the groups and not found anything
> that really help, any help provide will be greatly
> appreciated.
>
> Thanks,
> Mike
>


M. Burdette Posted on 2008-12-08 18:35:30.0Z
Sender: 2bf9.493d6433.1804289383@sybase.com
From: M. Burdette
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <493d68f1.2c8e.1681692777@sybase.com>
References: <493d62c2@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Dec 2008 10:35:30 -0800
X-Trace: forums-1-dub 1228761330 10.22.241.41 (8 Dec 2008 10:35:30 -0800)
X-Original-Trace: 8 Dec 2008 10:35:30 -0800, 10.22.241.41
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88337
Article PK: 417590

I misspoke....The data returned to the dw from the db is
grouped by territory, and sorted by region asc, territory
asc. The datawindow groups by region and the values in the
group trailer are the sums of the territory level sales.
Any change to the sort string includes the region as the
first field in the new sort statement. I am certain the
data and sort string are correct.

The problem is the extra group trailer that appears out of
no where on the sort / group calc, it doesn't even have a
corresponding group header as shown in the original
attachment.

My sort code executes in the following order...

dw_rpt.SetRedraw( FALSE )
dw_rpt.SetSort( ls_sort ) // region a, curmtd d
dw_rpt.Sort( )
dw_rpt.GroupCalc( )
dw_rpt.SetRedraw( TRUE )

This sort code is in the double-clicked event of dw_rpt...

> If you have GROUP BY in the SQL, that would suggest you
> are already summing by region, and that's what you are
> seeing. You want to do that in either the DW or the SQL,
> not both. If you sort by region in the SQL that will put
> the DW in the initial order you want.
> The usual way to do this would be computed fields in the
> group trailer, Sum (current_mtd for group 1) and Sum
> (prior_mtd for group 1), which would give you the results
> you are looking for after the DW has been resorted by
> region. It might also need a GroupCalc after the Sort().
>
> <M. Burdette> wrote in message
> > news:493d50e8.2968.1681692777@sybase.com... Good Morning
> > , I am using PB10.5 Build 4523. Connected to SQL Server
> > 2000 via PB Microsoft OLE DB.
> >
> > I have built a simple region, territory sales report.
> > The report is grouped by region. The group is in the
> > datawindow as well as the sql.
> >
> > The issue I am having occurs when I change the sort
> > order in script, if the sort causes the first row in the
> > first group to move to another position it will add a
> > group trailer row to the first row and then the rest of
> > the rows in the group will get a trailer row at the
> > bottom of the group as they should. The totals in both
> > group trailers are the same. There is only one group
> header row for the entire group. >
> > Hopefully the attachment makes it clear what is
> happening. >
> > Once this occurs, no amount of sorts or groupcalcs will
> > restore the split row. It is always the first row of
> > the first group that splits off. The value that is
> > being grouped on (region) is always the first thing in
> > the sort string, and it is in the sql as well.
> >
> > Also, once it splits in a report will multiple regions,
> > if I reverse the sort on the region, whatever region
> > takes over as the first group has its first territory
> split out. >
> > I have stripped this report down to just its bare bones,
> > no text, no computed fields, no line objects. I have
> > tried on other machines as well, and it still occurs.
> > Doesn't matter if it is in the development environment
> > or as an executable app.
> >
> > I and the other programmers in my group have been having
> > this issue more often now the we use 10.5 for most new
> > development. However, this would also happen on
> > occasion in PB6.5 as well.
> >
> > I have searched around the groups and not found anything
> > that really help, any help provide will be greatly
> > appreciated.
> >
> > Thanks,
> > Mike
> >
>
>


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-12-08 18:50:07.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <493d62c2@forums-1-dub> <493d68f1.2c8e.1681692777@sybase.com>
Subject: Re: Datawindow Sort / GroupCalc Issue
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <493d6c5f$1@forums-1-dub>
Date: 8 Dec 2008 10:50:07 -0800
X-Trace: forums-1-dub 1228762207 10.22.241.152 (8 Dec 2008 10:50:07 -0800)
X-Original-Trace: 8 Dec 2008 10:50:07 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88338
Article PK: 417592

Can you post the SQL?

<M. Burdette> wrote in message news:493d68f1.2c8e.1681692777@sybase.com...
>
> I misspoke....The data returned to the dw from the db is
> grouped by territory, and sorted by region asc, territory
> asc. The datawindow groups by region and the values in the
> group trailer are the sums of the territory level sales.
> Any change to the sort string includes the region as the
> first field in the new sort statement. I am certain the
> data and sort string are correct.
>
> The problem is the extra group trailer that appears out of
> no where on the sort / group calc, it doesn't even have a
> corresponding group header as shown in the original
> attachment.
>
> My sort code executes in the following order...
>
> dw_rpt.SetRedraw( FALSE )
> dw_rpt.SetSort( ls_sort ) // region a, curmtd d
> dw_rpt.Sort( )
> dw_rpt.GroupCalc( )
> dw_rpt.SetRedraw( TRUE )
>
> This sort code is in the double-clicked event of dw_rpt...
>
>> If you have GROUP BY in the SQL, that would suggest you
>> are already summing by region, and that's what you are
>> seeing. You want to do that in either the DW or the SQL,
>> not both. If you sort by region in the SQL that will put
>> the DW in the initial order you want.
>> The usual way to do this would be computed fields in the
>> group trailer, Sum (current_mtd for group 1) and Sum
>> (prior_mtd for group 1), which would give you the results
>> you are looking for after the DW has been resorted by
>> region. It might also need a GroupCalc after the Sort().
>>
>> <M. Burdette> wrote in message
>> > news:493d50e8.2968.1681692777@sybase.com... Good Morning
>> > , I am using PB10.5 Build 4523. Connected to SQL Server
>> > 2000 via PB Microsoft OLE DB.
>> >
>> > I have built a simple region, territory sales report.
>> > The report is grouped by region. The group is in the
>> > datawindow as well as the sql.
>> >
>> > The issue I am having occurs when I change the sort
>> > order in script, if the sort causes the first row in the
>> > first group to move to another position it will add a
>> > group trailer row to the first row and then the rest of
>> > the rows in the group will get a trailer row at the
>> > bottom of the group as they should. The totals in both
>> > group trailers are the same. There is only one group
>> header row for the entire group. >
>> > Hopefully the attachment makes it clear what is
>> happening. >
>> > Once this occurs, no amount of sorts or groupcalcs will
>> > restore the split row. It is always the first row of
>> > the first group that splits off. The value that is
>> > being grouped on (region) is always the first thing in
>> > the sort string, and it is in the sql as well.
>> >
>> > Also, once it splits in a report will multiple regions,
>> > if I reverse the sort on the region, whatever region
>> > takes over as the first group has its first territory
>> split out. >
>> > I have stripped this report down to just its bare bones,
>> > no text, no computed fields, no line objects. I have
>> > tried on other machines as well, and it still occurs.
>> > Doesn't matter if it is in the development environment
>> > or as an executable app.
>> >
>> > I and the other programmers in my group have been having
>> > this issue more often now the we use 10.5 for most new
>> > development. However, this would also happen on
>> > occasion in PB6.5 as well.
>> >
>> > I have searched around the groups and not found anything
>> > that really help, any help provide will be greatly
>> > appreciated.
>> >
>> > Thanks,
>> > Mike
>> >
>>
>>


M. Burdette Posted on 2008-12-08 19:03:21.0Z
Sender: 2cd8.493d6ce6.1804289383@sybase.com
From: M. Burdette
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <493d6f79.2d22.1681692777@sybase.com>
References: <493d6c5f$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Dec 2008 11:03:21 -0800
X-Trace: forums-1-dub 1228763001 10.22.241.41 (8 Dec 2008 11:03:21 -0800)
X-Original-Trace: 8 Dec 2008 11:03:21 -0800, 10.22.241.41
Lines: 103
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88339
Article PK: 417593

SELECT
region_no, region_name, territory_no, territory_name,
SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
FROM gross_sales_query
GROUP BY territory_no, territory_name, region_no,
region_name ORDER BY region_no ASC

There are a few more large year-to-date sum statements, but
they would just clutter up the post. I have also stripped
the dw down to the above sql during my testing and it still
has the behavior stated below...

> Can you post the SQL?
>
> <M. Burdette> wrote in message
> news:493d68f1.2c8e.1681692777@sybase.com... >
> > I misspoke....The data returned to the dw from the db is
> > grouped by territory, and sorted by region asc,
> > territory asc. The datawindow groups by region and the
> > values in the group trailer are the sums of the
> > territory level sales. Any change to the sort string
> > includes the region as the first field in the new sort
> > statement. I am certain the data and sort string are
> correct. >
> > The problem is the extra group trailer that appears out
> > of no where on the sort / group calc, it doesn't even
> > have a corresponding group header as shown in the
> > original attachment.
> >
> > My sort code executes in the following order...
> >
> > dw_rpt.SetRedraw( FALSE )
> > dw_rpt.SetSort( ls_sort ) // region a, curmtd d
> > dw_rpt.Sort( )
> > dw_rpt.GroupCalc( )
> > dw_rpt.SetRedraw( TRUE )
> >
> > This sort code is in the double-clicked event of
> dw_rpt... >
> >> If you have GROUP BY in the SQL, that would suggest you
> >> are already summing by region, and that's what you are
> >> seeing. You want to do that in either the DW or the
> SQL, >> not both. If you sort by region in the SQL that
> will put >> the DW in the initial order you want.
> >> The usual way to do this would be computed fields in
> the >> group trailer, Sum (current_mtd for group 1) and
> Sum >> (prior_mtd for group 1), which would give you the
> results >> you are looking for after the DW has been
> resorted by >> region. It might also need a GroupCalc
> after the Sort(). >>
> >> <M. Burdette> wrote in message
> >> > news:493d50e8.2968.1681692777@sybase.com... Good
> Morning >> > , I am using PB10.5 Build 4523. Connected to
> SQL Server >> > 2000 via PB Microsoft OLE DB.
> >> >
> >> > I have built a simple region, territory sales report.
> >> > The report is grouped by region. The group is in the
> >> > datawindow as well as the sql.
> >> >
> >> > The issue I am having occurs when I change the sort
> >> > order in script, if the sort causes the first row in
> the >> > first group to move to another position it will
> add a >> > group trailer row to the first row and then the
> rest of >> > the rows in the group will get a trailer row
> at the >> > bottom of the group as they should. The
> totals in both >> > group trailers are the same. There is
> only one group >> header row for the entire group. >
> >> > Hopefully the attachment makes it clear what is
> >> happening. >
> >> > Once this occurs, no amount of sorts or groupcalcs
> will >> > restore the split row. It is always the first
> row of >> > the first group that splits off. The value
> that is >> > being grouped on (region) is always the first
> thing in >> > the sort string, and it is in the sql as
> well. >> >
> >> > Also, once it splits in a report will multiple
> regions, >> > if I reverse the sort on the region,
> whatever region >> > takes over as the first group has its
> first territory >> split out. >
> >> > I have stripped this report down to just its bare
> bones, >> > no text, no computed fields, no line objects.
> I have >> > tried on other machines as well, and it still
> occurs. >> > Doesn't matter if it is in the development
> environment >> > or as an executable app.
> >> >
> >> > I and the other programmers in my group have been
> having >> > this issue more often now the we use 10.5 for
> most new >> > development. However, this would also
> happen on >> > occasion in PB6.5 as well.
> >> >
> >> > I have searched around the groups and not found
> anything >> > that really help, any help provide will be
> greatly >> > appreciated.
> >> >
> >> > Thanks,
> >> > Mike
> >> >
> >>
> >>
>
>


"Paul Horan[Sybase]" <phoran_remove Posted on 2008-12-08 19:19:46.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <493d6c5f$1@forums-1-dub> <493d6f79.2d22.1681692777@sybase.com>
Subject: Re: Datawindow Sort / GroupCalc Issue
Lines: 117
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <493d7352$1@forums-1-dub>
Date: 8 Dec 2008 11:19:46 -0800
X-Trace: forums-1-dub 1228763986 10.22.241.152 (8 Dec 2008 11:19:46 -0800)
X-Original-Trace: 8 Dec 2008 11:19:46 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88340
Article PK: 417594

I agree with Jerry - either group in the SQL or the datawindow, but you
absolutely don't need to do it in both places.

a) retrieve the detail WITHOUT summing/grouping in the SQL. Hide the detail
band and only show the summary bands.
or -
b) sum in the SQL and just show that as the visible detail band.

Paul Horan[Sybase]
paulhoran.pbdjmagazine.com

<M. Burdette> wrote in message news:493d6f79.2d22.1681692777@sybase.com...
>
> SELECT
> region_no, region_name, territory_no, territory_name,
> SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
> SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
> FROM gross_sales_query
> GROUP BY territory_no, territory_name, region_no,
> region_name ORDER BY region_no ASC
>
> There are a few more large year-to-date sum statements, but
> they would just clutter up the post. I have also stripped
> the dw down to the above sql during my testing and it still
> has the behavior stated below...
>
>> Can you post the SQL?
>>
>> <M. Burdette> wrote in message
>> news:493d68f1.2c8e.1681692777@sybase.com... >
>> > I misspoke....The data returned to the dw from the db is
>> > grouped by territory, and sorted by region asc,
>> > territory asc. The datawindow groups by region and the
>> > values in the group trailer are the sums of the
>> > territory level sales. Any change to the sort string
>> > includes the region as the first field in the new sort
>> > statement. I am certain the data and sort string are
>> correct. >
>> > The problem is the extra group trailer that appears out
>> > of no where on the sort / group calc, it doesn't even
>> > have a corresponding group header as shown in the
>> > original attachment.
>> >
>> > My sort code executes in the following order...
>> >
>> > dw_rpt.SetRedraw( FALSE )
>> > dw_rpt.SetSort( ls_sort ) // region a, curmtd d
>> > dw_rpt.Sort( )
>> > dw_rpt.GroupCalc( )
>> > dw_rpt.SetRedraw( TRUE )
>> >
>> > This sort code is in the double-clicked event of
>> dw_rpt... >
>> >> If you have GROUP BY in the SQL, that would suggest you
>> >> are already summing by region, and that's what you are
>> >> seeing. You want to do that in either the DW or the
>> SQL, >> not both. If you sort by region in the SQL that
>> will put >> the DW in the initial order you want.
>> >> The usual way to do this would be computed fields in
>> the >> group trailer, Sum (current_mtd for group 1) and
>> Sum >> (prior_mtd for group 1), which would give you the
>> results >> you are looking for after the DW has been
>> resorted by >> region. It might also need a GroupCalc
>> after the Sort(). >>
>> >> <M. Burdette> wrote in message
>> >> > news:493d50e8.2968.1681692777@sybase.com... Good
>> Morning >> > , I am using PB10.5 Build 4523. Connected to
>> SQL Server >> > 2000 via PB Microsoft OLE DB.
>> >> >
>> >> > I have built a simple region, territory sales report.
>> >> > The report is grouped by region. The group is in the
>> >> > datawindow as well as the sql.
>> >> >
>> >> > The issue I am having occurs when I change the sort
>> >> > order in script, if the sort causes the first row in
>> the >> > first group to move to another position it will
>> add a >> > group trailer row to the first row and then the
>> rest of >> > the rows in the group will get a trailer row
>> at the >> > bottom of the group as they should. The
>> totals in both >> > group trailers are the same. There is
>> only one group >> header row for the entire group. >
>> >> > Hopefully the attachment makes it clear what is
>> >> happening. >
>> >> > Once this occurs, no amount of sorts or groupcalcs
>> will >> > restore the split row. It is always the first
>> row of >> > the first group that splits off. The value
>> that is >> > being grouped on (region) is always the first
>> thing in >> > the sort string, and it is in the sql as
>> well. >> >
>> >> > Also, once it splits in a report will multiple
>> regions, >> > if I reverse the sort on the region,
>> whatever region >> > takes over as the first group has its
>> first territory >> split out. >
>> >> > I have stripped this report down to just its bare
>> bones, >> > no text, no computed fields, no line objects.
>> I have >> > tried on other machines as well, and it still
>> occurs. >> > Doesn't matter if it is in the development
>> environment >> > or as an executable app.
>> >> >
>> >> > I and the other programmers in my group have been
>> having >> > this issue more often now the we use 10.5 for
>> most new >> > development. However, this would also
>> happen on >> > occasion in PB6.5 as well.
>> >> >
>> >> > I have searched around the groups and not found
>> anything >> > that really help, any help provide will be
>> greatly >> > appreciated.
>> >> >
>> >> > Thanks,
>> >> > Mike
>> >> >
>> >>
>> >>
>>
>>


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2008-12-08 21:11:37.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <493d6c5f$1@forums-1-dub> <493d6f79.2d22.1681692777@sybase.com> <493d7352$1@forums-1-dub>
Subject: Re: Datawindow Sort / GroupCalc Issue
Lines: 125
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <493d8d89@forums-1-dub>
Date: 8 Dec 2008 13:11:37 -0800
X-Trace: forums-1-dub 1228770697 10.22.241.152 (8 Dec 2008 13:11:37 -0800)
X-Original-Trace: 8 Dec 2008 13:11:37 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88343
Article PK: 417597

And most DBMS will sort by the GROUP BY in order to do the SUM, and then
sort again for the ORDER BY. Explain Plan will reveal that. If you need to
change the sort for the DW, (a) will be the better choice.

"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> wrote in message
news:493d7352$1@forums-1-dub...
>I agree with Jerry - either group in the SQL or the datawindow, but you
>absolutely don't need to do it in both places.
>
> a) retrieve the detail WITHOUT summing/grouping in the SQL. Hide the
> detail band and only show the summary bands.
> or -
> b) sum in the SQL and just show that as the visible detail band.
>
> Paul Horan[Sybase]
> paulhoran.pbdjmagazine.com
>
> <M. Burdette> wrote in message news:493d6f79.2d22.1681692777@sybase.com...
>>
>> SELECT
>> region_no, region_name, territory_no, territory_name,
>> SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
>> SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
>> FROM gross_sales_query
>> GROUP BY territory_no, territory_name, region_no,
>> region_name ORDER BY region_no ASC
>>
>> There are a few more large year-to-date sum statements, but
>> they would just clutter up the post. I have also stripped
>> the dw down to the above sql during my testing and it still
>> has the behavior stated below...
>>
>>> Can you post the SQL?
>>>
>>> <M. Burdette> wrote in message
>>> news:493d68f1.2c8e.1681692777@sybase.com... >
>>> > I misspoke....The data returned to the dw from the db is
>>> > grouped by territory, and sorted by region asc,
>>> > territory asc. The datawindow groups by region and the
>>> > values in the group trailer are the sums of the
>>> > territory level sales. Any change to the sort string
>>> > includes the region as the first field in the new sort
>>> > statement. I am certain the data and sort string are
>>> correct. >
>>> > The problem is the extra group trailer that appears out
>>> > of no where on the sort / group calc, it doesn't even
>>> > have a corresponding group header as shown in the
>>> > original attachment.
>>> >
>>> > My sort code executes in the following order...
>>> >
>>> > dw_rpt.SetRedraw( FALSE )
>>> > dw_rpt.SetSort( ls_sort ) // region a, curmtd d
>>> > dw_rpt.Sort( )
>>> > dw_rpt.GroupCalc( )
>>> > dw_rpt.SetRedraw( TRUE )
>>> >
>>> > This sort code is in the double-clicked event of
>>> dw_rpt... >
>>> >> If you have GROUP BY in the SQL, that would suggest you
>>> >> are already summing by region, and that's what you are
>>> >> seeing. You want to do that in either the DW or the
>>> SQL, >> not both. If you sort by region in the SQL that
>>> will put >> the DW in the initial order you want.
>>> >> The usual way to do this would be computed fields in
>>> the >> group trailer, Sum (current_mtd for group 1) and
>>> Sum >> (prior_mtd for group 1), which would give you the
>>> results >> you are looking for after the DW has been
>>> resorted by >> region. It might also need a GroupCalc
>>> after the Sort(). >>
>>> >> <M. Burdette> wrote in message
>>> >> > news:493d50e8.2968.1681692777@sybase.com... Good
>>> Morning >> > , I am using PB10.5 Build 4523. Connected to
>>> SQL Server >> > 2000 via PB Microsoft OLE DB.
>>> >> >
>>> >> > I have built a simple region, territory sales report.
>>> >> > The report is grouped by region. The group is in the
>>> >> > datawindow as well as the sql.
>>> >> >
>>> >> > The issue I am having occurs when I change the sort
>>> >> > order in script, if the sort causes the first row in
>>> the >> > first group to move to another position it will
>>> add a >> > group trailer row to the first row and then the
>>> rest of >> > the rows in the group will get a trailer row
>>> at the >> > bottom of the group as they should. The
>>> totals in both >> > group trailers are the same. There is
>>> only one group >> header row for the entire group. >
>>> >> > Hopefully the attachment makes it clear what is
>>> >> happening. >
>>> >> > Once this occurs, no amount of sorts or groupcalcs
>>> will >> > restore the split row. It is always the first
>>> row of >> > the first group that splits off. The value
>>> that is >> > being grouped on (region) is always the first
>>> thing in >> > the sort string, and it is in the sql as
>>> well. >> >
>>> >> > Also, once it splits in a report will multiple
>>> regions, >> > if I reverse the sort on the region,
>>> whatever region >> > takes over as the first group has its
>>> first territory >> split out. >
>>> >> > I have stripped this report down to just its bare
>>> bones, >> > no text, no computed fields, no line objects.
>>> I have >> > tried on other machines as well, and it still
>>> occurs. >> > Doesn't matter if it is in the development
>>> environment >> > or as an executable app.
>>> >> >
>>> >> > I and the other programmers in my group have been
>>> having >> > this issue more often now the we use 10.5 for
>>> most new >> > development. However, this would also
>>> happen on >> > occasion in PB6.5 as well.
>>> >> >
>>> >> > I have searched around the groups and not found
>>> anything >> > that really help, any help provide will be
>>> greatly >> > appreciated.
>>> >> >
>>> >> > Thanks,
>>> >> > Mike
>>> >> >
>>> >>
>>> >>
>>>
>>>
>
>


"Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_ Posted on 2008-12-08 21:45:35.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_@_teamsybase.com>
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
References: <493d6c5f$1@forums-1-dub> <493d6f79.2d22.1681692777@sybase.com> <493d7352$1@forums-1-dub>
In-Reply-To: <493d7352$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: <493d957f$2@forums-1-dub>
Date: 8 Dec 2008 13:45:35 -0800
X-Trace: forums-1-dub 1228772735 10.22.241.152 (8 Dec 2008 13:45:35 -0800)
X-Original-Trace: 8 Dec 2008 13:45:35 -0800, vip152.sybase.com
Lines: 122
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88344
Article PK: 417598

PMJI, but I think you (Paul) and Jerry are missing something. The SQL
statement will bring back, as detail rows, a territory's total sales. M.
Burdette wants a datawindow that shows these detail rows but also has a
summary by region. I do not see a duplication of grouping efforts.

To the OP,
Can you export the datawindow and email it to me? Take out the obvious
"NO_SPAMMING" and all of the underscores from my email address.

Paul Horan[Sybase] wrote:
> I agree with Jerry - either group in the SQL or the datawindow, but you
> absolutely don't need to do it in both places.
>
> a) retrieve the detail WITHOUT summing/grouping in the SQL. Hide the detail
> band and only show the summary bands.
> or -
> b) sum in the SQL and just show that as the visible detail band.
>
> Paul Horan[Sybase]
> paulhoran.pbdjmagazine.com
>
> <M. Burdette> wrote in message news:493d6f79.2d22.1681692777@sybase.com...
>> SELECT
>> region_no, region_name, territory_no, territory_name,
>> SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
>> SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
>> FROM gross_sales_query
>> GROUP BY territory_no, territory_name, region_no,
>> region_name ORDER BY region_no ASC
>>
>> There are a few more large year-to-date sum statements, but
>> they would just clutter up the post. I have also stripped
>> the dw down to the above sql during my testing and it still
>> has the behavior stated below...
>>
>>> Can you post the SQL?
>>>
>>> <M. Burdette> wrote in message
>>> news:493d68f1.2c8e.1681692777@sybase.com... >
>>>> I misspoke....The data returned to the dw from the db is
>>>> grouped by territory, and sorted by region asc,
>>>> territory asc. The datawindow groups by region and the
>>>> values in the group trailer are the sums of the
>>>> territory level sales. Any change to the sort string
>>>> includes the region as the first field in the new sort
>>>> statement. I am certain the data and sort string are
>>> correct. >
>>>> The problem is the extra group trailer that appears out
>>>> of no where on the sort / group calc, it doesn't even
>>>> have a corresponding group header as shown in the
>>>> original attachment.
>>>>
>>>> My sort code executes in the following order...
>>>>
>>>> dw_rpt.SetRedraw( FALSE )
>>>> dw_rpt.SetSort( ls_sort ) // region a, curmtd d
>>>> dw_rpt.Sort( )
>>>> dw_rpt.GroupCalc( )
>>>> dw_rpt.SetRedraw( TRUE )
>>>>
>>>> This sort code is in the double-clicked event of
>>> dw_rpt... >
>>>>> If you have GROUP BY in the SQL, that would suggest you
>>>>> are already summing by region, and that's what you are
>>>>> seeing. You want to do that in either the DW or the
>>> SQL, >> not both. If you sort by region in the SQL that
>>> will put >> the DW in the initial order you want.
>>>>> The usual way to do this would be computed fields in
>>> the >> group trailer, Sum (current_mtd for group 1) and
>>> Sum >> (prior_mtd for group 1), which would give you the
>>> results >> you are looking for after the DW has been
>>> resorted by >> region. It might also need a GroupCalc
>>> after the Sort(). >>
>>>>> <M. Burdette> wrote in message
>>>>>> news:493d50e8.2968.1681692777@sybase.com... Good
>>> Morning >> > , I am using PB10.5 Build 4523. Connected to
>>> SQL Server >> > 2000 via PB Microsoft OLE DB.
>>>>>> I have built a simple region, territory sales report.
>>>>>> The report is grouped by region. The group is in the
>>>>>> datawindow as well as the sql.
>>>>>>
>>>>>> The issue I am having occurs when I change the sort
>>>>>> order in script, if the sort causes the first row in
>>> the >> > first group to move to another position it will
>>> add a >> > group trailer row to the first row and then the
>>> rest of >> > the rows in the group will get a trailer row
>>> at the >> > bottom of the group as they should. The
>>> totals in both >> > group trailers are the same. There is
>>> only one group >> header row for the entire group. >
>>>>>> Hopefully the attachment makes it clear what is
>>>>> happening. >
>>>>>> Once this occurs, no amount of sorts or groupcalcs
>>> will >> > restore the split row. It is always the first
>>> row of >> > the first group that splits off. The value
>>> that is >> > being grouped on (region) is always the first
>>> thing in >> > the sort string, and it is in the sql as
>>> well. >> >
>>>>>> Also, once it splits in a report will multiple
>>> regions, >> > if I reverse the sort on the region,
>>> whatever region >> > takes over as the first group has its
>>> first territory >> split out. >
>>>>>> I have stripped this report down to just its bare
>>> bones, >> > no text, no computed fields, no line objects.
>>> I have >> > tried on other machines as well, and it still
>>> occurs. >> > Doesn't matter if it is in the development
>>> environment >> > or as an executable app.
>>>>>> I and the other programmers in my group have been
>>> having >> > this issue more often now the we use 10.5 for
>>> most new >> > development. However, this would also
>>> happen on >> > occasion in PB6.5 as well.
>>>>>> I have searched around the groups and not found
>>> anything >> > that really help, any help provide will be
>>> greatly >> > appreciated.
>>>>>> Thanks,
>>>>>> Mike
>>>>>>
>>>>>
>>>
>
>


"Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_ Posted on 2008-12-10 17:47:34.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_@_teamsybase.com>
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
References: <493d6c5f$1@forums-1-dub> <493d6f79.2d22.1681692777@sybase.com> <493d7352$1@forums-1-dub> <493d957f$2@forums-1-dub>
In-Reply-To: <493d957f$2@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: <494000b6$1@forums-1-dub>
Date: 10 Dec 2008 09:47:34 -0800
X-Trace: forums-1-dub 1228931254 10.22.241.152 (10 Dec 2008 09:47:34 -0800)
X-Original-Trace: 10 Dec 2008 09:47:34 -0800, vip152.sybase.com
Lines: 140
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88376
Article PK: 417629

I received your email and have been playing with the datawindow.
Unfortunately, I can't recreate the issue. I'm running PB11.2 Build
8407. I suggest looking for stray code that might be affecting the sort
process. Are you using a framework datawindow control? If so, is the
Sort() method overridden?

Another thought: You're sorting by region (region_name, as the object is
called in the datawindow object) and grouping by region_no. Do either of
these columns have trailing spaces that might make the values different?

Beyond that, I'm stumped. (Especially with the trailer totals adding up
properly.) If you have a support contract with Sybase, you might see if
their engineers can shed any light on the subject.

Jason 'Bug' Fenter [TeamSybase] wrote:
> PMJI, but I think you (Paul) and Jerry are missing something. The SQL
> statement will bring back, as detail rows, a territory's total sales. M.
> Burdette wants a datawindow that shows these detail rows but also has a
> summary by region. I do not see a duplication of grouping efforts.
>
> To the OP,
> Can you export the datawindow and email it to me? Take out the obvious
> "NO_SPAMMING" and all of the underscores from my email address.
>
>
>
> Paul Horan[Sybase] wrote:
>> I agree with Jerry - either group in the SQL or the datawindow, but
>> you absolutely don't need to do it in both places.
>>
>> a) retrieve the detail WITHOUT summing/grouping in the SQL. Hide the
>> detail band and only show the summary bands.
>> or -
>> b) sum in the SQL and just show that as the visible detail band.
>>
>> Paul Horan[Sybase]
>> paulhoran.pbdjmagazine.com
>>
>> <M. Burdette> wrote in message
>> news:493d6f79.2d22.1681692777@sybase.com...
>>> SELECT
>>> region_no, region_name, territory_no, territory_name,
>>> SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
>>> SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
>>> FROM gross_sales_query
>>> GROUP BY territory_no, territory_name, region_no,
>>> region_name ORDER BY region_no ASC
>>>
>>> There are a few more large year-to-date sum statements, but
>>> they would just clutter up the post. I have also stripped
>>> the dw down to the above sql during my testing and it still
>>> has the behavior stated below...
>>>
>>>> Can you post the SQL?
>>>>
>>>> <M. Burdette> wrote in message
>>>> news:493d68f1.2c8e.1681692777@sybase.com... >
>>>>> I misspoke....The data returned to the dw from the db is
>>>>> grouped by territory, and sorted by region asc,
>>>>> territory asc. The datawindow groups by region and the
>>>>> values in the group trailer are the sums of the
>>>>> territory level sales. Any change to the sort string
>>>>> includes the region as the first field in the new sort
>>>>> statement. I am certain the data and sort string are
>>>> correct. >
>>>>> The problem is the extra group trailer that appears out
>>>>> of no where on the sort / group calc, it doesn't even
>>>>> have a corresponding group header as shown in the
>>>>> original attachment.
>>>>>
>>>>> My sort code executes in the following order...
>>>>>
>>>>> dw_rpt.SetRedraw( FALSE )
>>>>> dw_rpt.SetSort( ls_sort ) // region a, curmtd d
>>>>> dw_rpt.Sort( )
>>>>> dw_rpt.GroupCalc( )
>>>>> dw_rpt.SetRedraw( TRUE )
>>>>>
>>>>> This sort code is in the double-clicked event of
>>>> dw_rpt... >
>>>>>> If you have GROUP BY in the SQL, that would suggest you
>>>>>> are already summing by region, and that's what you are
>>>>>> seeing. You want to do that in either the DW or the
>>>> SQL, >> not both. If you sort by region in the SQL that
>>>> will put >> the DW in the initial order you want.
>>>>>> The usual way to do this would be computed fields in
>>>> the >> group trailer, Sum (current_mtd for group 1) and
>>>> Sum >> (prior_mtd for group 1), which would give you the
>>>> results >> you are looking for after the DW has been
>>>> resorted by >> region. It might also need a GroupCalc
>>>> after the Sort(). >>
>>>>>> <M. Burdette> wrote in message
>>>>>>> news:493d50e8.2968.1681692777@sybase.com... Good
>>>> Morning >> > , I am using PB10.5 Build 4523. Connected to
>>>> SQL Server >> > 2000 via PB Microsoft OLE DB.
>>>>>>> I have built a simple region, territory sales report.
>>>>>>> The report is grouped by region. The group is in the
>>>>>>> datawindow as well as the sql.
>>>>>>>
>>>>>>> The issue I am having occurs when I change the sort
>>>>>>> order in script, if the sort causes the first row in
>>>> the >> > first group to move to another position it will
>>>> add a >> > group trailer row to the first row and then the
>>>> rest of >> > the rows in the group will get a trailer row
>>>> at the >> > bottom of the group as they should. The
>>>> totals in both >> > group trailers are the same. There is
>>>> only one group >> header row for the entire group. >
>>>>>>> Hopefully the attachment makes it clear what is
>>>>>> happening. >
>>>>>>> Once this occurs, no amount of sorts or groupcalcs
>>>> will >> > restore the split row. It is always the first
>>>> row of >> > the first group that splits off. The value
>>>> that is >> > being grouped on (region) is always the first
>>>> thing in >> > the sort string, and it is in the sql as
>>>> well. >> >
>>>>>>> Also, once it splits in a report will multiple
>>>> regions, >> > if I reverse the sort on the region,
>>>> whatever region >> > takes over as the first group has its
>>>> first territory >> split out. >
>>>>>>> I have stripped this report down to just its bare
>>>> bones, >> > no text, no computed fields, no line objects.
>>>> I have >> > tried on other machines as well, and it still
>>>> occurs. >> > Doesn't matter if it is in the development
>>>> environment >> > or as an executable app.
>>>>>>> I and the other programmers in my group have been
>>>> having >> > this issue more often now the we use 10.5 for
>>>> most new >> > development. However, this would also
>>>> happen on >> > occasion in PB6.5 as well.
>>>>>>> I have searched around the groups and not found
>>>> anything >> > that really help, any help provide will be
>>>> greatly >> > appreciated.
>>>>>>> Thanks,
>>>>>>> Mike
>>>>>>>
>>>>>>
>>>>
>>
>>


Scott Morris Posted on 2008-12-08 20:07:47.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <493d6c5f$1@forums-1-dub> <493d6f79.2d22.1681692777@sybase.com>
Subject: Re: Datawindow Sort / GroupCalc Issue
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <493d7e93@forums-1-dub>
Date: 8 Dec 2008 12:07:47 -0800
X-Trace: forums-1-dub 1228766867 10.22.241.152 (8 Dec 2008 12:07:47 -0800)
X-Original-Trace: 8 Dec 2008 12:07:47 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88341
Article PK: 417595


<M. Burdette> wrote in message news:493d6f79.2d22.1681692777@sybase.com...
>
> SELECT
> region_no, region_name, territory_no, territory_name,
> SUM( cur_ev_nov_dol + cur_wl_nov_dol ) as curmtd,
> SUM( prv_ev_nov_dol + prv_wl_nov_dol ) as prvmtd
> FROM gross_sales_query
> GROUP BY territory_no, territory_name, region_no,
> region_name ORDER BY region_no ASC
>
> There are a few more large year-to-date sum statements, but
> they would just clutter up the post. I have also stripped
> the dw down to the above sql during my testing and it still
> has the behavior stated below...

This might be caused by the fact that your new sort specification is
inconsistent with the definitions of the group levels in the datawindow.
Another alternative explanation is that your datawindow design is based on
an assumption about the order of rows - which is that returned by your query
at the present time - and that your new sort specification is inconsistent
with this assumption / design. Usually this is obvious if you display the
columns used in various group level definitions.

Based on your query, it is also evident that the sort order of the query is
incompletely specified. You sort by region_no - but below that level the
data can come in any order. Do not be mislead by your existing usage if the
data happens to appear to be sorted by territory_no - there is nothing in
your query that requires the dbms to return the data in that order and it
can (and eventually will) return it in some other order. If you post the
definitions of your groups (in the datawindow - the query is irrelevant for
this discussion), and both the pre- / post-issue sort specifications of the
datawindow, it will be easier to explain if this is, in fact, the problem.


KL Posted on 2008-12-08 20:30:38.0Z
Sender: 2f24.493d81e2.1804289383@sybase.com
From: KL
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <493d83ee.2f4d.1681692777@sybase.com>
References: <493d50e8.2968.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Dec 2008 12:30:38 -0800
X-Trace: forums-1-dub 1228768238 10.22.241.41 (8 Dec 2008 12:30:38 -0800)
X-Original-Trace: 8 Dec 2008 12:30:38 -0800, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88342
Article PK: 417596

Looks good to me.
You are getting exactly what you are asking for.
Territory total and region total.
Of course, usually, but not necessarily always, they don't
'overlap'.

Sounds like the SQL and the DW are 'fighting' each other.
Just do everything (group and sort) in the DW.
KL

> Good Morning,
> I am using PB10.5 Build 4523. Connected to SQL Server 2000
> via PB Microsoft OLE DB.
>
> I have built a simple region, territory sales report. The
> report is grouped by region. The group is in the
> datawindow as well as the sql.
>
> The issue I am having occurs when I change the sort order
> in script, if the sort causes the first row in the first
> group to move to another position it will add a group
> trailer row to the first row and then the rest of the rows
> in the group will get a trailer row at the bottom of the
> group as they should. The totals in both group trailers
> are the same. There is only one group header row for the
> entire group.
>
> Hopefully the attachment makes it clear what is happening.
>
> Once this occurs, no amount of sorts or groupcalcs will
> restore the split row. It is always the first row of the
> first group that splits off. The value that is being
> grouped on (region) is always the first thing in the sort
> string, and it is in the sql as well.
>
> Also, once it splits in a report will multiple regions, if
> I reverse the sort on the region, whatever region takes
> over as the first group has its first territory split out.
>
> I have stripped this report down to just its bare bones,
> no text, no computed fields, no line objects. I have
> tried on other machines as well, and it still occurs.
> Doesn't matter if it is in the development environment or
> as an executable app.
>
> I and the other programmers in my group have been having
> this issue more often now the we use 10.5 for most new
> development. However, this would also happen on occasion
> in PB6.5 as well.
>
> I have searched around the groups and not found anything
> that really help, any help provide will be greatly
> appreciated.
>
> Thanks,
> Mike
>
>
> [Attachment: rpt_grp_err.bmp]


Jeremy Lakeman Posted on 2008-12-09 09:22:02.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Sort / GroupCalc Issue
Date: Tue, 9 Dec 2008 01:22:02 -0800 (PST)
Organization: http://groups.google.com
Lines: 58
Message-ID: <e2704331-3811-49db-826e-66681c65cf26@r15g2000prh.googlegroups.com>
References: <493d50e8.2968.1681692777@sybase.com>
NNTP-Posting-Host: 122.49.134.3
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1228814522 6685 127.0.0.1 (9 Dec 2008 09:22:02 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 9 Dec 2008 09:22:02 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: r15g2000prh.googlegroups.com; posting-host=122.49.134.3; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US; rv:1.9.0.4) Gecko/2008102920 Firefox/3.0.4,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!r15g2000prh.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88350
Article PK: 417605


On Dec 9, 2:52 am, M. Burdette wrote:
> Good Morning,
> I am using PB10.5 Build 4523. Connected to SQL Server 2000
> via  PB Microsoft OLE DB.
>
> I have built a simple region, territory sales report.  The
> report is grouped by region.  The group is in the datawindow
> as well as the sql.
>
> The issue I am having occurs when I change the sort order in
> script, if the sort causes the first row in the first group
> to move to another position it will add a group trailer row
> to the first row and then the rest of the rows in the group
> will get a trailer row at the bottom of the group as they
> should.  The totals in both group trailers are the same.
> There is only one group header row for the entire group.
>
> Hopefully the attachment makes it clear what is happening.
>
> Once this occurs, no amount of sorts or groupcalcs will
> restore the split row.  It is always the first row of the
> first group that splits off.  The value that is being
> grouped on (region) is always the first thing in the sort
> string, and it is in the sql as well.
>
> Also, once it splits in a report will multiple regions, if I
> reverse the sort on the region, whatever region takes over
> as the first group has its first territory split out.
>
> I have stripped this report down to just its bare bones, no
> text, no computed fields, no line objects.  I have tried on
> other machines as well, and it still occurs.  Doesn't matter
> if it is in the development environment or as an executable
> app.
>
> I and the other programmers in my group have been having
> this issue more often now the we use 10.5 for most new
> development.  However, this would also happen on occasion in
> PB6.5 as well.
>
> I have searched around the groups and not found anything
> that really help, any help provide will be greatly
> appreciated.
>
> Thanks,
> Mike
>
>  rpt_grp_err.bmp
> 37KViewDownload

There are two place you can specify the sort expression when you have
a group by datawindow.
You can specify the sort of the whole report, or the sort of each
group.

I've found it simpler to ignore the group sort, and only specify the
whole report sort.
But anyway, after you call sort() make sure you groupcalc().