I am trying to create a report that shows customers sorted in descending sales amount.

My program script is reading table tccom010 and then reading through table tdsls045 for each customer and accumulating a total for that customer. I then do a rprt_send() in the program script for that customer.

My problem is that I am sorting the report by the accumulated amount in descending order. One of the requirements is that I create a column that ranks the customer according to his position in the report. I don't think that I can calculate this in the program script because the sort is happening in the report.

Example of needed report output:

Rank Customer Sales Amount
1 111111 90,000
2 333333 85,000
3 222222 80,000

It seems that I can do something in the report script but I haven't been able to make it work.

All replies appreciated.....

Not sure if this would work, programming is not my field ( no pun ) but how about a variable in the report started at value 1, and incremented after layout for the rank??


Steve

I have tried creating a field in the report with the following code in the report script.

detail.1:
before.layout:
line.count = line.count + 1


The report lists all of the detail lines with a line.count of 1.

Is line.count decleared as an external variable?

I have the variable line.count set up as an external variable with the tcamnt domain.

Try moving layout detail.1 to after.tccom010.cuno.1 and alter your report script to...

after.tccom010.cuno.1:
after.layout:
line.count = line.count + 1

don't forget to put line.count = 1 in the before.program section

Ev

Thanks for your responses. I have tried all of the suggestions (as well as all the variations that I could think of) and still the rank number appears as 1 for every line.

It appears that the coding that is suppose to increment this number is only being executed the first time. I think that the way the program script and the report are interacting is different than the way I thought it worked when using different sort fields.

Anyway, I will keep trying and let you know if I figure it out.

Thanks

If sorting by the calculated amount I would suggest using the line count in the after section of the amount field. (You would need to account for situations where exists more than one customer per
descending amount).

Also note, if you are suppressing lines in the report (print expression other than 1 on report layout) then the calculations
will not come into play.

What are the sort fields on the report and what is the defined sort sequence ?

I currently have the report set up to use the after calculated field to update the ranking.

I am not suppressing the printing of any lines.

The sort in the report is as follows:

calculated amount field Sort Seq. 1 (Descending)
customer number Sort Seq. 2 (Ascending)

The program script is reading tccom010 index 1 by customer number.

If you have the a line the states that line.count = 1 try changing this to line.count = 3 (or anything but 1). This will help us determine if the line.count + 1 calculation is ever being touched.

If this line is not being touched then all counts should show as
the new value.

It may also be possible that instead of defining line.count as line.count + 1 simply use the aggregate function of count for
incrementing the line.count value based on the desired layout
section you which to base the increment on.

Could you post your code?

Yes, do post :)

Also, try declaring the line.count as a Report Input Field, NOT as an external in the report script.

Try debug and see if line.count = line.count + 1 ever gets hit, etc. If so, place a trace and see if it gets reset somewhere.

I defaulted the initial value to 3 instead of 1. All of the lines on the report then came out with a value of 3. This means that the increment code is never being executed.

I have not tried the count function but will give that a try also.

Being fairly new to this board I am not sure that I know how to post my code but I will see whats involved.

Thanks

I have tried many different variations today so I have tried to put the scripts back to the original version.

Report script:

before.program:
rank = 1

detail.1:
after.layout:
rank = rank + 1




Program script:


declaration:

table ttccom010 | Customers
table ttdsls045 | Maintain Sales Deliveries

extern domain tcdate date.f
extern domain tcdate date.t
extern domain tcamnt slsamt

form.1:
init.form:
get.screen.defaults()

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif

field.date.f:
when.field.changes:
date.t = date.f

functions:

function read.main.table()
{
select tccom010.*
from tccom010
selectdo
read.sales.table()
endselect
}

function read.sales.table()
{

slsamt = 0

select tdsls045.*
from tdsls045
where tdsls045._index2 = {7, :tccom010.cuno}
and tdsls045.dqua <> 0
order by tdsls045._index2
selectdo
slsamt = slsamt + tdsls045.amnt
endselect

if slsamt <> 0 then
rprt_send()
endif

}

Well, hopefully you are using Wordpad or SciTE etc. to do your editing. If you are, simply copy and paste. You can set Wordpad as your default editor in ttadv0110m000:

Editor Read/Write Command C:\wordpad.exe (insert correct path)

Starting Editor on Local system

what version are you using ? I had "other" report problems concerning "totaling" and found out some of my comps for creating/compiling reports are dozens of solutions behind.

We are using version 4c3.

should be fine. I thought you might be on a IVb version.

If you want to sort on the calculated field - make sure the field is defined in the input fields section as sort 1.

But I do not think that a field that is calculated in the report script can be used as a sort field, as the sorting has already occured at this point. This is what I think but am not 100 % sure.

Can you not get the total delivered qty for each customer from a different table ? look at some of the report/display sessions in the sales module.

The field that I want to use to sort is calculated in the program script (slsamt).

The field that I am having a problem with is calculated in the report script. I just want to print this field, not use it for sorting.

Ok, I had some time on my hands so I made my own session following your guidelines and it worked for me. I dropped the line

order by tdsls045._index2

in the program script (since the report is doing the sort),

I moved layout detail.1 to after field tccom010.cuno.1 and
here's my report script...

declaration:
domain tcamnt rank

before.program:
rank = 1

after.tccom010.cuno.1:
after.layout:
rank = rank + 1

try it out and let me know

Ev

The order by in the program would be used to optimize the
selecting of records from tdsls045. This should not affect how
the data is sorted in the report unless the report fields were
setup as presorted. Removing the order by tdsls045._index2
may adversely affect the performance of the session.

What is it that you are incrementing ? (detail lines ?) or customers ?

If you are printing detail lines then the line.count incrementer
will be active (verify via debug).

Are you actually printing any details on the after field sections
of customer or total ? The problem probably does not lie in
the program script or the report script, but in the way the report layouts are being used. The line.count field should not need to be defined as an external variable (in the report script), just defined as a variable, and it should not need to be defined as
an input field.

Try using:
declaration:
domain tcmcs.long line.count

At first I made the changes suggested by evertsen except that I did not declare field rank in the report script because I had it declared in the Input Field section.

This produced the same results as before.

I then decided to take the field out of the Input Field section and declare it in the report script. This produced the results that I wanted with the rank incrementing by one for each line of the report.

I do not understand why this change made the difference. At this point I'm not sure that I care how it works just that it works.

Thanks to all of you who took the time to take a look at this. I truly appreciate it.

Mike

Actually, I've found that using order by tends to slow down performance but I think that depends on the flavor of database.

It won't matter what domain he uses for the counter as long as it is numeric in nature (although I would have used a "long" myself).