Re: Order By in 4GL Report

You need to decalre a char variable(s) of the greater size of your possible order by choices. Inside your foreach using a conditional statement put the value of the order by choice in the variable, which is used in the report's order by section. You also have to track what to do in after/before groups depending what the order by choice is. I cuted out sections of code out of a report that lets the user assign the order of a report from 4 choices , not all have to be included , if the user enters 2314 then the report is ordered by field 2, f 3 , f 1 and f 4 . The code below should give you an idea how this is done. Note : on the order external by do not mix simple varable type like char with record fields (struct) for some reason this gives weird errors (RDS/Sequent/HP) . main define f_part_1, f_part_2, f_part_3, f_part_4 char(18) # my largest order by element is 18 long. foreach q_row_set into f_ord.* let f_order_by_array[1] = f_ord.div_code let f_order_by_array[2] = f_ord.order_date let f_order_by_array[3] = f_ord.user_created let f_order_by_array[4] = f_ord.cust_num for f_i = 1 to g_length let f_x = g_pri_sort[f_i,f_i] # put the right value in the right part num , trick the report. case f_i when 1 let f_part_1 = f_order_by_array[f_x] when 2 let f_part_2 = f_order_by_array[f_x] when 3 let f_part_3 = f_order_by_array[f_x] when 4 let f_part_4 = f_order_by_array[f_x] end case end for output to report main_print(f_ord.*,f_part_1,f_part_2,f_part_3,f_part_4) end foreach end main report main_print(f_ord,f_part_1,f_part_2,f_part_3,f_part_4) order external by f_part_1,f_part_2,f_part_3,f_part_4 before group of f_part_1 # depending what the order by choice was different actions are taken on each control break. if g_pri_sort[1,1] != " " and not f_head and not g_first_row then case g_pri_sort[1,1] when "1" do something when "2" print column 1,"Order Date :",column 13,f_ord.order_date using "mmm dd, yyyy" when "3" print column 1,"Operator :",column 13,f_ord.user_created end if before group of f_part_2 end report Bryan Tonnet <batonnet@zeta.org.au> wrote in article <5astqp$d03@godzilla.zeta.org.au>... > Anyone know of a decent (decent = not recopying the entire report block) way > to include a dynamic "order by" in a 4GL report? > > That is, if my report driver is; > > LET sql_stmt_l = "SELECT a,b,c FROM z" > > IF something THEN > LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY a" > ELSE > LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY b" > END IF > > ; can the report be told of a variable "order by", or am I stuck with > a different report block for each possibility? > > Thanks in advance > > Bryan Tonnet > batonnet@zeta.org.au >