i want to develop a report on sales order acitivity.

top 50 customers during the given period , selecting all the field from tdsls400.

user to define in form top = 50 or whatever range.
date from & to options on form

what will query syntax. please help

thanks in advance

hi,

What do you mean by top 50 customers...
like first 50 customers according to customer code or customer name ???

regards
sumesh

on sales amount

e.g. a = 500000
b = 980000
c = 1050000

now i want this to be appear as descending 105000...

This Query Data (ttadv3580m00) code will get you a report sorted in descending Sales Order Amount for a range of dates:

select
tdsls400.ofbp, | Sold-to Business Partner
tccom100.nama, | Name
tdsls400.orno, | Sales Order
tdsls400.odat, | Order Date
tdsls400.hdst, | Sales Order Status
tdsls400.oamt | Order Amount
from
tccom100, | Business Partners
tdsls400 | Sales Orders
where
tdsls400.odat >= tdsls400.odat.f and
tdsls400.odat <= tdsls400.odat.t and
tdsls400.hdst <> tdsls.hdst.cancelled and
tccom100.bpid = tdsls400.ofbp
order by
tdsls400.oamt desc

Mike

murphy,

the query will give all the bp for a given range. but i am looking for only top 50 customers during the year

e.g. bp = a,b,c,d,e,f,g
amount = 1000000,800000,770000,550000 it should print in desc mode

in query or sessions how to define only top 50 customers to be printed

OK. This will list all BPs in descending order by total order amount for a range of order dates . Not sure how to list just the top 50.



select
tdsls400.ofbp, | Sold-to Business Partner
tccom100.nama, | Name
SUM(tdsls400.oamt):float0 | Order Amount
from
tccom100, | Business Partners
tdsls400 | Sales Orders
where
tdsls400.odat >= tdsls400.odat.f and
tdsls400.odat <= tdsls400.odat.t and
tdsls400.hdst <> tdsls.hdst.cancelled and
tccom100.bpid = tdsls400.ofbp
group by
tdsls400.ofbp,
tccom100.nama
order by
3 desc

And why not use as set with 50 rows - that would only get the first 50 rows of a table. I can't help on the query itself.
select
tdsls400.ofbp, | Sold-to Business Partner
tccom100.nama, | Name
SUM(tdsls400.oamt):float0 | Order Amount
from
tccom100, | Business Partners
tdsls400 | Sales Orders
where
tdsls400.odat >= tdsls400.odat.f and
tdsls400.odat <= tdsls400.odat.t and
tdsls400.hdst <> tdsls.hdst.cancelled and
tccom100.bpid = tdsls400.ofbp
group by
tdsls400.ofbp,
tccom100.nama
order by
3 desc
as set with 50 rows

Hi,
can this be provided to user prompt.

e.g. since it is for 50 rows

tomorrow user may require to check for top 5 or 25 rows as example

from to to options for top 5 - 25 customers

where and how to define in the query or session level.

can this be prepared as chart ..what is the options..

If it is a user prompt then I would just add a counter and quick if-then-else statement. If the counter was less than the input value of the user - print it, if greater skip it or break out of the select do statement. There are plenty of ways to do this, but this is the quickest that I can think of. I mean you can go so far as to use dynamic sql to do something like this - but from all the posts you seem to just be starting on baan so I will skip trying to explain how that could work. First get used to programming in LN - which I do not know.

how to define user prompt for >= 5 and <25 for top customers

query/session level

Assuming this is a formfield - then you would use a check.input. Only an example:

field.top.25:
check.input
if top.25< 6 or top.25>= 25 then
message(" Bad input")
set.input.error("")
endif


PS
And yes NPRao - I know you should create message that actually goes into the set.input.error statement. I know this is not the best technique. Just a quick example to try to get them going.

in query level can i define
e.g 1. cust <= 5 (USER PROMPT) I WANT TO PROVIDE THIS OPTION
THEN ONLY 5 CUSTOMERS TO BE SELECT AND PRINT AS PER THE ORDER AMOUNT DESCENDING

e.g 2. cust >= 5 and cust <= 25 USER PROMPT) I WANT TO PROVIDE THIS OPTION

THEN ONLY 5 to 25 CUSTOMERS TO BE SELECT AND PRINT AS PER THE ORDER AMOUNT DESCENDING

i am able to print by descending customer order amount,but it is appearing for all ( session written)
requirement how to filter as per user range of customer 5 or 10 or 15...

sample script /query will help

in query level can i define
e.g 1. cust <= 5 (USER PROMPT) I WANT TO PROVIDE THIS OPTION
THEN ONLY 5 CUSTOMERS TO BE SELECT AND PRINT AS PER THE ORDER AMOUNT DESCENDING

e.g 2. cust >= 5 and cust <= 25 USER PROMPT) I WANT TO PROVIDE THIS OPTION

THEN ONLY 5 to 25 CUSTOMERS TO BE SELECT AND PRINT AS PER THE ORDER AMOUNT DESCENDING

i am able to print by descending customer order amount,but it is appearing for all ( session written)
requirement how to filter as per user range of customer 5 or 10 or 15...

sample script /query will help

Hello,
the forum will assist you, if problems occur.
But no complete development will be done.
Post your current script an someone maybe will give you the hints you need.

At above posts Mark and Mike gave you a lot of hints.

Regards

hints already provided.


looking for range of 10 to 25 customers list from order table tdsls400 .order amount

Hi Mark,

i executed the query & out put is fine.

i try to add 1 field and deriving 1 value

e.g amount = tdsls400.oamt / tdpur400.ratp(1) and in report new column amount is attaching. it is not printing .. it is appearing as #

how to correct it

Hi Mark,

i executed the query & out put is fine.

i try to add 1 field and deriving 1 value

e.g amount = tdsls400.oamt / tdpur400.ratp(1) and in report new column amount is attaching. it is not printing .. it is appearing as #

how to correct it

Hello,

# is the overflow character.

Value does not fit the field.

Regards

Hi Mark,

i executed the query & out put is fine.

i try to add 1 field and deriving 1 value

e.g amount = tdsls400.oamt / tdsls400.ratp(1) and in report new column amount is attaching. it is not printing .. it is appearing as #

how to correct it

Make sure you are not dividing by 0 and as bernd metioned make sure amount has the correct domain to support the calculation.