This discussion is archived
4 Replies Latest reply: Dec 14, 2010 9:57 PM by 824010 RSS

Dynamic Order By on Report Builder 10g

616943 Newbie
Currently Being Moderated
Hi,

How can I do a dynamic order by clause using user parameters on Report Builder 10g? I need to set dynamically the column and the direction on the clause.

Thanks.

Bruno Galletti
Brazil.
  • 1. Re: Dynamic Order By on Report Builder 10g
    rwillems Pro
    Currently Being Moderated
    Hi Bruno,

    use a lexical parameter for this. Assuming you have 2 bind parameters p_col and p_direction. Let the user choose the values for them out of LOVs. For p_col you could use something like
    select column_name from user_tab_columns where table_name = 'MYTABLE'
    and for p_direction a static LOV with ACS and DESC

    In the after param form trigger do something like
    :p_mysort := 'ORDER BY '||:p_col||' '||:p_direction

    The parameter p_mysort create manual in the Navigator and add him in the query like:
    SELECT ....
    ....
    &p_mysort


    Regards
    Rainer
  • 2. Re: Dynamic Order By on Report Builder 10g
    616943 Newbie
    Currently Being Moderated
    Hi, I did this and the reports was ok.

    Thanks.

    Bruno
  • 3. Re: Dynamic Order By on Report Builder 10g
    671654 Newbie
    Currently Being Moderated
    Hi Bruno / Rainer,

    I am implementing the same solution, and after adding the lexical parameter reference in the SQL statement and calling the report from Oracle Forms the report failed with the following error on the report server:

    Terminated with error: <br>REP-300: missing expression select wcsr_no , wcsr_date , wcsr_rec_week_zone , wcsr_loc_house_num || ' ' || wcsr_loc_pre_dir || ' ' || wcsr_loc_street_name || ' ' || wcsr_loc_street_type || ' ' || wcsr_loc_post_dir || ' ' || wcsr_loc_unit || ' ' || wcsr_loc_city || ' ' || wcsr ==>

    No change has been done to the SQL statement except adding the &p_orderby parameter at the end of the query.

    Any suggestions? Are all parameters defined as "bind" parameters on the Repotr Object Navigator.?

    Thanks

    Gabriel Aguirre
    gabriel.aguirre@alfagl.us
  • 4. Re: Dynamic Order By on Report Builder 10g
    824010 Newbie
    Currently Being Moderated
    Hi,
    Instead of Lexical parameters you can also use decode statement at order by in your select statement

    like Ex:
    select * from gl_je_headers
    where..... conditions
    order by decode(je_source,'Manual',je_header_id,je_category);

    Thanks
    Pras