9 Replies Latest reply: Jan 24, 2013 2:12 AM by fac586 RSS

    multiple tables in a single standard report using single query

    bp21
      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
          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
            I am using Apex version 4.1.

            Thanks!
            • 3. Re: multiple tables in a single standard report using single query
              VC
              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
                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
                  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
                    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
                      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
                        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
                          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.)