6 Replies Latest reply: Jul 23, 2013 10:25 AM by fac586 RSS

    How to create a report(timesheet) with columns as row values??

    SyedHussain

      Hi,

       

      i have created a table in APEX 4.2.2.00.11...

       

      CREATE TABLE "TRIAL_3_DETAILS"
        ( "CONSULTANT" VARCHAR2(20),
      "CLIENT" VARCHAR2(10),
      "PROJECT" VARCHAR2(20),
      "BILLABLE" VARCHAR2(10),
      "TASK" VARCHAR2(50),
      "DATE_" DATE,
      "EFFORT" NUMBER
        )
      /

       

      now i want create a Report “Time Sheets”  where the date values are columns in the weekly timesheet along with other columns as client name, project name,etc....

      also i want the estimated effort value under the date column..

       

      Task

      Client

      Project

      Bill (Y/N)

      <date>

      <date>

      <date>

      <date>

      <date>

      <date>

      <date>

      Hours

      <Task Description>

      <blank>

      <project>

      <blank>

      <Estimated Effort>

       

       

       

       

       

       

      <Estimated Effort>

      <Task Description>

      <blank>

      <project>

      <blank>

       

      <Estimated Effort>.

       

       

       

       

       

      <Estimated Effort>

      <Task Description>

      <blank>

      <project>

      <blank>

       

       

      <Estimated Effort>

       

       

       

       

      <Estimated Effort>

      <Task Description>

      <blank>

      <project>

      <blank>

       

       

       

      <Estimated Effort>

       

       

       

      <Estimated Effort>

      <Task Description>

      <blank>

      <project>

      <blank>

       

       

       

       

      <Estimated Effort>

       

       

      <Estimated Effort>

       

      Is it possible to create this kind of report??

       

      Syed

        • 1. Re: How to create a report(timesheet) with columns as row values??
          fac586

          8b7d2ac0-d473-4e4e-b028-6724cfc8f05c wrote:

          Please update your forum profile with a real handle instead of "8b7d2ac0-d473-4e4e-b028-6724cfc8f05c".

          Is it possible to create this kind of report??

          Yes. This can be done using the appropriate pivot method for your database version in the report source query, and the PL/SQL Headings Type to generate dynamic column headings. A custom report template may also be useful.

          • 2. Re: How to create a report(timesheet) with columns as row values??
            SyedHussain

            hi

            thanks for the reply..

            i am trying to implement pivot but i am not able to do so... can you give an example on how to use the pivot method

            • 3. Re: How to create a report(timesheet) with columns as row values??
              fac586

              SyedHussain wrote:

               

              hi

              thanks for the reply..

              i am trying to implement pivot but i am not able to do so... can you give an example on how to use the pivot method

              There are examples available in the links in the FAQ post. However it generally works better the other way. You show us what you've tried and we are more likely to be able to work out why it isn't working. Post what you've done so far (following steps 5-10 in these posting guidelines), or better still, reproduce the problem on apex.oracle.com and post guest developer credentials to the workspace.

              • 4. Re: How to create a report(timesheet) with columns as row values??
                SyedHussain

                hey thanks again

                i tried it it works fine..

                ----------------------------------------------------------------------

                WITH     e     AS

                (    

                     SELECT     consultant , client , project

                     ,     task , effort ,date_  , billable

                     FROM    trial_3_details

                )   

                SELECT     *

                FROM     e

                PIVOT     (     sum (effort)

                          FOR     date_     IN     ( '07/22/2013'     AS monday ,

                                                     '07/23/2013'    as tuesday,

                                                    '07/24/2013'    as wednesday,

                                                     '07/25/2013'     as thrusday,

                                                     '07/26/2013'     as friday,

                                                      '07/27/2013'     as saturday,

                                                      '07/28/2013'     as sunday                        

                                       

                                                    

                                         )

                     )

                ------------------------------------------------------------------------

                CONSULTANTCLIENTPROJECTTASKBILLABLEMONDAYTUESDAYWEDNESDAYTHRUSDAYFRIDAYSATURDAYSUNDAY
                SyedFacebookSecurityPrivacy SettingsN-------
                SyedGoogleSecuritygmail privacy featuresY8------
                PatelDellRecruitmentProfile DetailingY-------
                SanjayGoogleSoftwareWebpage developmentY-------
                JohnDellHardwareProcessorsY-------
                SteveLenovoAdministrationAttendanceN-------
                SyedYahooSecurityFirewall CheckY-------
                SanjayGoogleSoftwaredebuggingN-------
                SteveMicrosoftAdminstrationAttendanceN-------
                SteveYahooAdminstrationAttendanceN-------

                 

                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                 

                One more question how do i automatically set the days instead of explicitly declaring it every time?? and also i want to add one more column

                which gives overall week 'effort' of a consultant..

                • 5. Re: How to create a report(timesheet) with columns as row values??
                  Mike Kutz

                  use TO_CHAR() to "calculate" the day of the week.  Then, use that value.

                   

                  WITH     e     AS
                  (    
                       SELECT     consultant , client , project
                       ,     task , effort
                  , TO_CHAR(date_,'Day') day_of_week
                  , billable
                       FROM    trial_3_details
                      -- WHERE date_ between ____ and ____
                  )   
                  SELECT     *
                  FROM     e
                  PIVOT     (     sum (effort)
                            FOR     day_of_week   IN ('Monday', 'Tuesday', 'Wednesday','Thursday','Friday','Saturday','Sunday')
                       )
                  
                  • 6. Re: How to create a report(timesheet) with columns as row values??
                    fac586

                    MikeKutz wrote:

                     

                    use TO_CHAR() to "calculate" the day of the week.  Then, use that value.

                     

                    1. WITH    e    AS 
                    2. (     
                    3.     SELECT    consultant , client , project 
                    4.     ,    task , effort 
                    5. , TO_CHAR(date_,'Day') day_of_week 
                    6. , billable 
                    7.     FROM    trial_3_details 
                    8.     -- WHERE date_ between ____ and ____ 
                    9. )   
                    10. SELECT    * 
                    11. FROM    e 
                    12. PIVOT    (    sum (effort) 
                    13.           FOR    day_of_week  IN ('Monday', 'Tuesday', 'Wednesday','Thursday','Friday','Saturday','Sunday'
                    14.     )

                    Note that you actually need to use 'fmDay' as the date format mask with this approach. Using 'Day' will result in all of the day_of_week values being padded with blanks to the length of the longest value (Wednesday), so that will be the only value matched in the pivot:

                     

                    SQL> with t as (
                      2    select
                      3        to_char(trunc(sysdate) + (level - 1), 'Day') d
                      4      , round(dbms_random.value(0, 100), 2) z
                      5    from
                      6        dual
                      7          connect by level <= 10)
                      8  select
                      9      *
                    10  from
                    11      t
                    12  pivot (
                    13      sum(z)
                    14      for d in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'));
                    
                    
                      'Monday'  'Tuesday' 'Wednesday' 'Thursday'  'Friday' 'Saturday'  'Sunday'
                    ---------- ---------- ----------- ---------- ---------- ---------- ----------
                                                83.23
                    

                     

                    Using 'fmDay' avoids the blank padding and the pivot works as required:

                     

                    SQL> with t as (
                      2    select
                      3        to_char(trunc(sysdate) + (level - 1), 'fmDay') d
                      4      , round(dbms_random.value(0, 100), 2) z
                      5    from
                      6        dual
                      7          connect by level <= 10)
                      8  select
                      9      *
                    10  from
                    11      t
                    12  pivot (
                    13      sum(z)
                    14      for d in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'));
                    
                    
                      'Monday'  'Tuesday' 'Wednesday' 'Thursday'  'Friday' 'Saturday'  'Sunday'
                    ---------- ---------- ----------- ---------- ---------- ---------- ----------
                        60.16    125.09      44.17      77.5      3.63      82.89      26.22