4 Replies Latest reply: Dec 14, 2010 11:57 PM by 824010 RSS

    Dynamic Order By on Report Builder 10g

    616943
      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
          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
            Hi, I did this and the reports was ok.

            Thanks.

            Bruno
            • 3. Re: Dynamic Order By on Report Builder 10g
              671654
              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
                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