9 replies to this topic

[Tyleulen]

I have a report I would like to be able to sort two different ways. The starter screen would allow the user to choose which way the final report would be output. I know I could basically set up two function paths that do the same thing up until the sort for the final report, but that does not seem ideal. Is there a way I could have both paths use the same update and report function and just have the variable that is sorted in the report change based on what was choosen in the start screen? My initial thought was to define a $SORT variable that would have the name of the file variable I wanted to sort by and have the cycle sort by that, but that does not seem to work. So, is there a way to dynamically change the sort by variable for the cycle, or do I need to just copy all the functions twice?

[andykay]

Ty,

Not knowing how you are declearing your $SORT variable, or how you set up your PS, it is difficult to speculate why the soft coding of the $SORT variable is not working, as we do exactly what it sounds like you are doing here without any problems.

Barring that, and assuming you haven't already tried so already, you could write 2 different SQL code statements separated in an IF $SORT = clause that contains "ORDER BY variable_name" declarations as the last line of the SQL code.

I have not tried it, but you might even be able to soft code the variable_name, too, and use 1 single SQL statement for both types of sorts...it's worth a try.

Good Luck,

AK

[jcduym]

Hi Ty

Yes that should be possible. You can pass in the sort variable by any known means (com-variable, file-variable).

Please bear in mind that the length of a sort key in ProIV is restricted to 128 characters. If the key of the sorted file is longer it won't work. And you should flag all files included in the sort, and if necessary set the any keys in the before-read sort etc.

Hth


Jan

[Chris Mackenzie]

as sort/select variable have $SORTVAR

in sort/select logic do something like this with
your start screen selection

CASE $SELECT
WHEN '1' : $SORTVAR = CUST_NAME
WHEN '2' : $SORTVAR = INV_NO + INV_SEQ
WHEN '3' : $SORTVAR = CONV(INV_DATE) + INV_TIME
etc
ENDCASE

should work ok

[Mike Schoen]

If you are already using an update which feeds data to a report, you can also set up a memory file which gets populated by the update, with the first key as the sort key.
You can then also do control-breaks on the sort key for sub-totalling, etc.

[Chris Pepper]

Further to that, in ANY reporting instance where you need to run a ProIV sort and have an intermediate work file you should set the keys so that the file ends up sorted - and remove the ProIV sort.

Always remember what ProIV means by a SORT...

it means it will create an indexed workfile with a key made up of the variables you define, plus the key of the driving file...
then it will load every record not deselected by the Sort/Select logic into it (after reading every file down to the Sort Select endfile)
then it will reprocess every record in the temporary file re-reading each record in the LU

[Tyleulen]

as sort/select variable have $SORTVAR

in sort/select logic do something like this with
your start screen selection

CASE $SELECT
WHEN '1' :  $SORTVAR = CUST_NAME
WHEN '2' : $SORTVAR = INV_NO + INV_SEQ
WHEN '3' : $SORTVAR = CONV(INV_DATE) + INV_TIME
etc
ENDCASE

should work ok

So then, how would you set up multiple sorting levels?

[Bob Filipiak]

Tyleulen,

The method shown assumes a single level of sorting, with the top (ONLY SORT) level being customer name, OR invoice number OR invoice date/time.

I get the impression, you wish to sort more levels, and to do so, you would have to spoecify additional variables. Native IIRC allows for 5 sort variables.

Let us assume for this example, that one of the top level sorts is by customer name, and with two or more second level sorts. Sort variable 1 (top level) would be the customer name, sort level two could be invoice date/time OR invoice number. These values would be assigned to sort variable two. This assignment could be acomplished in a single SELECT CASE statement (like the example shown) ONLY with you having to add the additional sort levels.

CASE $SELECT
WHEN '11' : $SORTVAR1 = CUST_NAME : $SORTVAR2 = INV_NO + INV_SEQ 'customer name/invoice #
WHEN '12' : $SORTVAR1 = CUST_NAME: $SORTVAR2 = CONV(INV_DATE) + INV_TIME ' customer name/invoice date-time
WHEN '13' : $SORTVAR1 = CUST_NAME: $SORTVAR2 = CUST_PO_NUMBER ' customer name/purch order #

WHEN '21' : $SORTVAR1 = CONV(INV_DATE) + INV_TIME : $SORTVAR2 = CUST_NAME
etc
ENDCASE


Alternatively, you could use multiple SELECT CASE statements, with the additional ones processing lower levels of sorting. Whether or not that would be a maintenance headache (and it could be) is up to you.

HTH


Bob Filipiak

Edited by Bob Filipiak, 08 June 2006 - 04:26 PM.

[Richard Bassett]

Tyleulen,

FWIW, if you're on a(n) SQL database, it's almost invariably faster and more scalable to try and delegate the sort into SQL (if you can't avoid it altogether!)

[Tyleulen]

I am using VIP on Reality. I finally figured out what the problem I had was. I was trying to pass the name of the numeric variable into the sort order rather than the value of the variable.

I set up multiple sort levels using multiple case statements and once I got my variables matched up it worked fine.