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.

Group Sort in IM6

3 posts in PB Infomaker Report Writer Last posting was on 1998-08-31 14:22:01.0Z
Mike Gilbert Posted on 1998-08-13 11:51:57.0Z
Newsgroups: sybase.public.infomaker.general
Date: Thu, 13 Aug 1998 12:51:57 +0100
From: Mike Gilbert <MikeG@Teamsoft.IE>
X-Mailer: Mozilla 4.04 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Group Sort in IM6
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 42
Message-ID: <348_35D2D35D.BCF958E2@Teamsoft.IE>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6945
Article PK: 242977

Hello IM gurus,

We think that we've got a slight problem with a group sort in IM 6.

Query along following lines

SELECT "sales_order"."cust_id",
"sales_order"."order_date",
"sales_order_items"."prod_id",
sum(quantity)
FROM "sales_order_items",
"sales_order"
WHERE ( "sales_order"."id" = "sales_order_items"."id" ) and
( ( "sales_order"."order_date" <= :reporting_month ) )
GROUP BY "sales_order"."cust_id",
"sales_order_items"."prod_id",
"sales_order"."order_date"
ORDER BY "sales_order"."cust_id" ASC,
"sales_order_items"."prod_id" ASC,
"sales_order"."order_date" ASC

The report has 2 groups - one by customer, second by product
(It's called Top Products by Customer)

Detail has a column 'qty_current_month' which is 0 if the order date's
month doesn't match
the reporting_month argument and contains the quantity ordered
otherwise. The detail
band is zero height cos we want a summary total not every date.

Trailer for product group contains product id, sum(qty_current_month for
group 2), sum(compute_0004 for group 2)

All OK so far

What we're trying to do (without much success) is to sort the report by
customer
then by descending qty_current_month and then by product. There's no
problem
if we want to sort group 2 by product_id.

We've tried the group sort, the row sort, adding sorts on the select,
all to no avail.

Does anyone have any idea if this is some kind of bug or if we're
trying to do something
impossible ?

Any suggestions are greatly appreciated.

TIA

Mike


Stephen Dupre[Powersoft Tools, Sybase, Inc.] Posted on 1998-08-31 14:22:01.0Z
Newsgroups: sybase.public.infomaker.general
Date: Mon, 31 Aug 1998 10:22:01 -0400
From: "Stephen Dupre[Powersoft Tools, Sybase, Inc.]" <sdupre@sybase.com>
Reply-To: sdupre@sybase.com
Organization: Sybase, Inc.
X-Mailer: Mozilla 3.0 (WinNT; I)
MIME-Version: 1.0
To: Mike Gilbert <MikeG@Teamsoft.IE>
Subject: Re: Group Sort in IM6
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 50
Message-ID: <348_35EAB189.69A5@sybase.com>
References: <348_35D2D35D.BCF958E2@Teamsoft.IE>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6925
Article PK: 242956

Nope. Known bug 171324. This probably won't be fixed until 7.0.
Terrys nested report suggestion is a good one. You basically drop the
second group by using a nest instead.

Stephen Dupre
Sybase

Mike Gilbert wrote:
>
> Hello IM gurus,
>
> We think that we've got a slight problem with a group sort in IM 6.
>
> Query along following lines
>
> SELECT "sales_order"."cust_id",
> "sales_order"."order_date",
> "sales_order_items"."prod_id",
> sum(quantity)
> FROM "sales_order_items",
> "sales_order"
> WHERE ( "sales_order"."id" = "sales_order_items"."id" ) and
> ( ( "sales_order"."order_date" <= :reporting_month ) )
> GROUP BY "sales_order"."cust_id",
> "sales_order_items"."prod_id",
> "sales_order"."order_date"
> ORDER BY "sales_order"."cust_id" ASC,
> "sales_order_items"."prod_id" ASC,
> "sales_order"."order_date" ASC
>
> The report has 2 groups - one by customer, second by product
> (It's called Top Products by Customer)
>
> Detail has a column 'qty_current_month' which is 0 if the order date's
> month doesn't match
> the reporting_month argument and contains the quantity ordered
> otherwise. The detail
> band is zero height cos we want a summary total not every date.
>
> Trailer for product group contains product id, sum(qty_current_month for
> group 2), sum(compute_0004 for group 2)
>
> All OK so far
>
> What we're trying to do (without much success) is to sort the report by
> customer
> then by descending qty_current_month and then by product. There's no
> problem
> if we want to sort group 2 by product_id.
>
> We've tried the group sort, the row sort, adding sorts on the select,
> all to no avail.
>
> Does anyone have any idea if this is some kind of bug or if we're
> trying to do something
> impossible ?
>
> Any suggestions are greatly appreciated.
>
> TIA
>
> Mike


Terry Dykstra [TeamPS] Posted on 1998-08-19 19:12:23.0Z
Newsgroups: sybase.public.infomaker.general
From: "Terry Dykstra [TeamPS]" <no_spam_tdykstra@cfol.ab.ca>
Subject: Re: Group Sort in IM6
Date: Wed, 19 Aug 1998 13:12:23 -0600
Lines: 77
Organization: Canadian Forest Oil Ltd.
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_I9kJiu5y9GA.298@forums.powersoft.com>
References: <348_35D2D35D.BCF958E2@Teamsoft.IE>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:6933
Article PK: 242964

I'm not sure I'm following your logic here. But if I understand you
correctly, you want it to look something like this:

customer sum_quantity product
1 100 A
1 90 C
1 80 B

2 280 B
2 150 A

You can do this by creating a nested report that sums(quantity) desc by
product . The retrieval argument passed would be the customer id.

--
Terry Dykstra [TeamPS]
Canadian Forest Oil Ltd.

Mike Gilbert wrote in message <35D2D35D.BCF958E2@Teamsoft.IE>...
>Hello IM gurus,
>
> We think that we've got a slight problem with a group sort in IM 6.
>
> Query along following lines
>
> SELECT "sales_order"."cust_id",
> "sales_order"."order_date",
> "sales_order_items"."prod_id",
> sum(quantity)
> FROM "sales_order_items",
> "sales_order"
> WHERE ( "sales_order"."id" = "sales_order_items"."id" ) and
> ( ( "sales_order"."order_date" <= :reporting_month ) )
>GROUP BY "sales_order"."cust_id",
> "sales_order_items"."prod_id",
> "sales_order"."order_date"
>ORDER BY "sales_order"."cust_id" ASC,
> "sales_order_items"."prod_id" ASC,
> "sales_order"."order_date" ASC
>
>The report has 2 groups - one by customer, second by product
>(It's called Top Products by Customer)
>
>Detail has a column 'qty_current_month' which is 0 if the order date's
>month doesn't match
>the reporting_month argument and contains the quantity ordered
>otherwise. The detail
>band is zero height cos we want a summary total not every date.
>
>Trailer for product group contains product id, sum(qty_current_month for
>group 2), sum(compute_0004 for group 2)
>
>All OK so far
>
>What we're trying to do (without much success) is to sort the report by
>customer
>then by descending qty_current_month and then by product. There's no
>problem
>if we want to sort group 2 by product_id.
>
>We've tried the group sort, the row sort, adding sorts on the select,
>all to no avail.
>
>Does anyone have any idea if this is some kind of bug or if we're
>trying to do something
>impossible ?
>
>Any suggestions are greatly appreciated.
>
>TIA
>
>Mike
>
>
>