This discussion is archived
9 Replies Latest reply: Jan 24, 2013 12:12 AM by fac586 RSS

multiple tables in a single standard report using single query

bp21 Newbie
Currently Being Moderated
Hi all,

I am using standard report. I have a situation wherein I need to display more than one table in the report from a single query. Tables are seperated based on the value in one of the column's data.

For example, I have employees table. In the report, I want three tables like: 1) employees having personal vehicle 2) employees not having personal vehicle 3) employees who belong to facilities division

Is this doable? If so, how can I achieve this?

Thanks!
  • 1. Re: multiple tables in a single standard report using single query
    VC Guru
    Currently Being Moderated
    bp21 wrote:
    Hi all,

    I am using standard report. I have a situation wherein I need to display more than one table in the report from a single query. Tables are seperated based on the value in one of the column's data.

    For example, I have employees table. In the report, I want three tables like: 1) employees having personal vehicle 2) employees not having personal vehicle 3) employees who belong to facilities division

    Is this doable? If so, how can I achieve this?

    Thanks!
    WHAT's your apex VERSION?

    You can use control break on that particular column. See http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/bldapp_rpt_att.htm#BCEDBBBJ
  • 2. Re: multiple tables in a single standard report using single query
    bp21 Newbie
    Currently Being Moderated
    I am using Apex version 4.1.

    Thanks!
  • 3. Re: multiple tables in a single standard report using single query
    VC Guru
    Currently Being Moderated
    Ok, Here is 4.1 doc for creating break column http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldapp_rpt_att.htm#BCEDBBBJ

    Is that what you are looking for?
  • 4. Re: multiple tables in a single standard report using single query
    bp21 Newbie
    Currently Being Moderated
    Yes, but I dont see the columns listed in Break Columns for which I want to break on. I see only First, First and Second and First,Second and Third only whereas there are more than 3 columns in my report.

    Sorry if this is something basic but I just started working on Apex. I am newbie.

    Thanks!
  • 5. Re: multiple tables in a single standard report using single query
    fac586 Guru
    Currently Being Moderated
    bp21 wrote:
    Yes, but I dont see the columns listed in Break Columns for which I want to break on. I see only First, First and Second and First,Second and Third only whereas there are more than 3 columns in my report.
    But how do you determine which categories employees belong to?
    1) employees having personal vehicle 2) employees not having personal vehicle 3) employees who belong to facilities division
    Are these categories based on data in the report? Which column(s)?

    APEX provides the capability for standard reports to break on up to 3 columns. The break columns should be at the leading edge of the report, i.e. the first 1, 2, or 3 columns. Then pick the number of columns used to break on from the Break Columns options.
  • 6. Re: multiple tables in a single standard report using single query
    bp21 Newbie
    Currently Being Moderated
    For the first two tables that I want in report, I have a column 'vehicle' in employee table. If that has some value then the employee has vehicle else if the column has null value then the employee does not have vehicle.

    For third table, I have a column 'division' with different values in it and I want to select one division employees.

    Thanks.
  • 7. Re: multiple tables in a single standard report using single query
    fac586 Guru
    Currently Being Moderated
    bp21 wrote:
    For the first two tables that I want in report, I have a column 'vehicle' in employee table. If that has some value then the employee has vehicle else if the column has null value then the employee does not have vehicle.

    For third table, I have a column 'division' with different values in it and I want to select one division employees.
    Then it sounds like you can create a report based on a query like:
    select
        case division
          when 'facilities'
          then
            '3) employees who belong to facilities division'
          else
            nvl2(vehicle, '1) employees having personal vehicle', '2) employees not having personal vehicle')
        end category
      , ...
    from
        employees
    ...
    order by
        category
    ...
    Then set the Break Formatting options:

    Break Columns: First Column
    Break Column Style: Repeat Headings on Break

    To get more control over a break report layout, use a Re: Custom Report Template Issue.
  • 8. Re: multiple tables in a single standard report using single query
    bp21 Newbie
    Currently Being Moderated
    Thanks all for your prompt replies.

    Fac586,

    I am unable to use
    order by
        category
    as I have sortable columns. Is there any other way to do this?

    Thanks!
  • 9. Re: multiple tables in a single standard report using single query
    fac586 Guru
    Currently Being Moderated
    bp21 wrote:

    I am unable to use
    order by
    category
    as I have sortable columns. Is there any other way to do this?
    Doing so in a single standard report—if it's even possible—would be decidedly non-trivial. I don't have time to get into it just now (might be a interesting puzzle for the weekend...)

    Breaking or partitioning the rows in this way requires that they are grouped by the partition value, which can only be done in the SQL source of a report by sorting. If additional sorting is required, then it has to be combined with the primary sort on the partition value. Achieving this is the non-trivial part.

    For the present the simple way to do this is to create 3 separate reports, one for each grouping. (It's worthwhile noting that in the time you've spent waiting on answers on this thread you could have created these 3 reports, done the laundry, watched a movie, grabbed a pizza and got a decent night's sleep. Sometimes nails are just nails, and you have to get the hammer out.)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points