1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Order by In Multiple Layout report

Discussion in 'Oracle Forms and Reports' started by ash, Jun 13, 2014.

  1. ash

    ash Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,



    I have a an Oracle Report (10g) as follows:

    Data model: 1 query based on view

    Layout: 10 different layouts.

    Format trigger: Based on field ('type') in query, it will select which layout to use
    return:TYPE = 'B' ;

    etc.



    This produces over 1000 pages. I want to order by by surname (from query) so i have set the Break Order to Ascending for surname in the data model.

    However, this seems to group by layout type and orders within each layer.



    So I end up with..



    Layout A - Adam

    Layout A - Aston

    Layout B - Archer



    Instead i want:



    Layout A - Adam

    Layout B - Archer

    Layout A - Aston

    .... X - Borris



    Does anyone know how i can get the report to Order by surname for all layouts and not group by layout.



    Thanks
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Provide Sample Data, That will help people to give solutions....
     
  3. ash

    ash Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    There is one group. The query is:
    Code (SQL):
    SELECT spriden_first_name,
                   spriden_mi,
                   spriden_last_name,
                   syrlong_degree_desc,
                   syvhonr_desc,
                   syrlong_long_desc,
                   syrlong_extra_grad_details,
                   shrdgmr_grad_date,
                   certificate_type
        FROM v_sal_bulk_certificates, t_sal_selected_id
     WHERE t_sal_selected_id.audsid = :P_SESSION
           AND v_sal_bulk_certificates.spriden_id = t_sal_selected_id.id
           AND shrdgmr_term_code_completed = :P_TERM
           AND shrdgmr_program = nvl(:P_PROGRAM,shrdgmr_program)
           AND shrdgmr_levl_code IN ('FE','UG')
    ORDER BY spriden_last_name, spriden_first_name, spriden_mi
     

    The format triggers on each layout is:

    return (substr:)CERTIFICATE_TYPE,1,1) = 'A') ;
    return (substr:)CERTIFICATE_TYPE,1,1) = 'B') ;
    .....etc.

    If the view returns 10 records of the same certificate type the order by seems to be fine. However, if the certificate types are different then the order by seems to stop working.
    The report is being called from a form.
    Regards,

    Ash
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Ash,

    Did you try to add certificate_type in your order by clause:

    Code (SQL):
    ORDER BY certificate_type, spriden_last_name, spriden_first_name, spriden_mi
     
  5. ash

    ash Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Rajen,

    I have not added that because they should not be ordered by certificate_type. They need to be ordered by last_name, which is already in the order by but not working.

    Thanks,

    Ash