I have a report which shows the values prid, desc,custprid.

I want to sort the output based on user input. for this i created a enumarted field with prid,desc,custprid. so when user selects any of these three, the output should be sorted by that value.

thanks
nansat99

Couple options -

1. Instead of sorting on the report, you can choose the 'Presorted' sorting option when you set up the input fields on the report. Then you can sort in your program script and they should come out in the same order on the report.

2. Create a second/third/fourth report that sorts things in the right order, and have the code open the correct report. Probably only want this if you also want to move around the fields in the layout as well.

if I opt for first option suggested by you how can i sort them in program script ? is there any predefined variable ?

thanks
nansat99

Well, you can do it dynamically with 1 sql statement, but that's probably more bother than yours sounds like. I would suggest 3 different SQL's with 3 different 'order by' clauses, depending on what sort option they chose. Something like:


if sort.choice = 1 then
select tdsls401.*
from tdsls401
where (your where conditions)
order by tdsls401._index1
selectdo
process.record() |* processes & prints
endselect
endif
if sort.choice = 2 then
select tdsls401.*
from tdsls401
where (your where conditions)
order by tdsls401._index2
selectdo
process.record() |* processes & prints
endselect
endif
if sort.choice = 3 then
select tdsls401.*
from tdsls401
where (your where conditions)
order by tdsls401._index3
selectdo
process.record() |* processes & prints
endselect
endif

I would pick option number 2. You already have the domain and can just open different reports. All you have to do is clone the report and change the sort options.

Typically when I do it in the program I do not set any sort options on the report. Then I just send the records in the order that I want them.

A third way to do this is to create a key field - not printed, but passed to the report. Each time you get ready to send the report create the key field in the sort order you want. So one time it may be keyfield = prid & desc &custprid, the next time it can be custprid & prid & desc. On the report use this keyfield as the sorted field.

I made the sort mode to presorted in report inputfields(prid, custprid, desc) and did the following coding.

functions:
function read.sort.and.process()
{
on case etol(sort.by)
case 1:
read.main.table.1()
break
case 2:
read.main.table.2()
break
case 3:
read.main.table.3()
break
default: read.main.table()
break
endcase
}
function read.main.table()
{
select timjr914.prid,timjr914.tbid
from timjr914
|where timjr914.auth = tcyesno.yes
selectdo
select timjr101.*, timjr910.upcc,timjr910.prid
from timjr101, timjr910
where timjr101._index1 = {:timjr914.prid}
and timjr101.prid refers to timjr910
selectdo
on case strip$(timjr914.tbid)
case "00024101":
cust.id = "95000"
break
default:break
endcase
rprt_send()
endselect
endselect
}
function read.main.table.1()
{
select timjr914.prid,timjr914.tbid
from timjr914
|where timjr914.auth = tcyesno.yes
selectdo
select timjr101.*, timjr910.upcc,timjr910.prid
from timjr101, timjr910
where timjr101._index1 = {:timjr914.prid}
and timjr101.prid refers to timjr910
order by timjr101._index1 selectdo
on case strip$(timjr914.tbid)
case "00024101":
cust.id = "95000"
break
default:break
endcase
rprt_send()
endselect
endselect
}
function read.main.table.2()
{
select timjr914.prid,timjr914.tbid
from timjr914
|where timjr914.auth = tcyesno.yes
selectdo
select timjr101.*, timjr910.upcc,timjr910.prid
from timjr101, timjr910
where timjr101._index1 = {:timjr914.prid}
and timjr101.prid refers to timjr910
order by timjr910.prid selectdo
on case strip$(timjr914.tbid)
case "00024101":
cust.id = "95000"
break
default:break
endcase
rprt_send()
endselect
endselect
}
function read.main.table.3()
{
select timjr914.prid,timjr914.tbid
from timjr914
|where timjr914.auth = tcyesno.yes
selectdo
select timjr101.*, timjr910.upcc,timjr910.prid
from timjr101, timjr910
where timjr101._index1 = {:timjr914.prid}
and timjr101.prid refers to timjr910
order by timjr101.desc
selectdo
on case strip$(timjr914.tbid)
case "00024101":
cust.id = "95000"
break
default:break
endcase
rprt_send()
endselect
endselect
}

Now even when i select sort by as "description" on form, still the report shown sort by prid,custprid.

Can you tell me where am I missing ?

regards
nansar99

Not sure on your data or what you are trying to accomplish, but it looks like you are first selecting from the timjr914 table, which probably comes out in a prid order, then selecting the specific timjr910/101 record that matches this prid. The sort is then based on the order that timjr914 is selected from, the order by on the inside select to 910/101 probably not doing much, since you are getting only for that prid (if the data is set up how I am imaging).

With multiple levels of selects, you may want to do as Mark originally recommended, choose option 2 where you create 3 different reports, the only difference being the sort order, and then all you need to do is open the right report instead of figuring all the sorting out in the script.

Hi,

This is what I would do:
- Define an additional variable in your program script.
- Fill this variable with the value of the variable on which you want to sort, so either prid, desc or custprid depending on the sort option the user selected.
- Add this variable to the report input fields and set sort sequence to 1 for this field (and ascending / descending).

The report will be sorted on this newly added field containing the value for the field that you want the report to be sorted on.

So:
- Only one query needed
- Only one report needed

Hope this helps.

Youp

My personal recommendation is to work as Youp suggested. I have had many similar situations, also with very complicated queries, and the most simple, neat, and efficient way to do it (also of course performance-wise) is by assigninging the - by user selected - sort-field names to variables, which are in turn defined as the report sort fields.

If you choose not to opt for this option, I would choose the different reports. It is about the same performance wise, yet creates of course a whole lot of extra reports, to be maintained, etc...

thank you all for your valuable suggestions, I followed the idea suggested by Youp and it worked.

Thanks
nansat

What I have done in the past, is to create a sort key as a text string.
I then fill the sort key with the fields in the order I require them.

The sort key is sent to the report writer, and is identified as the input field, sorted ascending. It is not necessarily used on any report line. I repeat, it is used to control the output sequence.
A low order byte on this sortkey can be the report option that is desired.
so for a key of three fields, chars you could have something similar to...

xxxxyyyzzz1 report type 1
zzzxxxxyyy2 report type 2
yyyxxxxzzz3 report type 3

When sorted, you will know how to process the data,
In the report, you could use ... after.receive.data, or other intercept.