2 Replies Latest reply on Jan 19, 2015 8:38 PM by disabled_rosalie

    Cross tab report

      Title

      Cross tab report

      Post

      Hi All,

      Sorry if this is a repetition of my previous post ( Summary II), already I just want to make my self clear and make the information of my files more detailed. Because I add more tables.

                                                                                                                                                                                                                                                                                                                                                                                                                     
      USER TABLE
      Field NameTypeOptions/Comments
      idTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameCalculationUnstored, = Get (AccountName)
      ReloginTextGlobal

       

                                                                                                                                                                                                                                                                                                                                                                                                                     
      Time_Log TABLE
      Field NameTypeOptions/Comments
      idTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameCalculationUnstored, = Get (AccountName)
      ID_timeTextIndexed, Auto-enter Serial

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      Projects_Activities TABLE
      Field NameTypeOptions/Comments
      idTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameCalculationUnstored, = Get (AccountName)
      id_projectTextIndexed
      Date Date
      ActivitiesTextIndexed
      Activities_time Number
      Activities_summarySummary=Total of Activities_time
      Lot_No.TextIndexed
      Project_CodeTextIndexed
      gFirstColDateDateGobal
      g2ndColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 1 ; 1 ; Year (gFirstColDate) )
      g3rdColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 2 ; 1 ; Year (gFirstColDate) )
      g4thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 3 ; 1 ; Year (gFirstColDate) )
      g5thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 4 ; 1 ; Year (gFirstColDate) )
      g6thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 5 ; 1 ; Year (gFirstColDate) )
      g7thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 6 ; 1 ; Year (gFirstColDate) )
      g8thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 7 ; 1 ; Year (gFirstColDate) )
      g9thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 8 ; 1 ; Year (gFirstColDate) )
      g10thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 9 ; 1 ; Year (gFirstColDate) )
      g11thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 10 ; 1 ; Year (gFirstColDate) )
      g11thColDateCalculationUnstored, Date ( Month (gFirstColDate ) + 12 ; 1 ; Year (gFirstColDate) )

       

                                                                                                                                                                                                                                                                                                                                               
      Projects_Codes TABLE
      Field NameTypeOptions/Comments
      idTextIndexed, Auto enter Calculation (Get (UUID)
      CreationAccountTextIndexed, Creation Account Name, Can't Modify Auto
      AccountNameCalculationUnstored, = Get (AccountName)

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      Privilege SET
      Table NameViewEditCreateDeleteField Access
      UsersLimited (CreationAccount = Users::AccountNameLimited (CreationAccount = Users::AccountName--All
      Time_LogLimited (CreationAccount = Users::AccountNameLimited (CreationAccount = Users::AccountNameYesYesAll
      Project_ActivitiesLimited (CreationAccount = Users::AccountNameLimited (CreationAccount = Users::AccountNameYesYesAll
      Project_CodesYesYesYesYesAll
      Any TAbleYesYesYesYesAll

      ::AccountName

       
      1. My USER TABLE is Connected to Time_Log Table (User::id=Time_Log::id)
      2. My Time_Log Table is Connected to Project_Activities Table (Time_log::ID_Time=Project_Activities::id_project
      3. My Project_Activities is Connected to Projects_Codes Table (Project_Activities::id_project = Projects_Codes)

       

       

        • 1. Re: Cross tab report

          This is the continuation...

          My Project_Activities Table is what Im using to record the time of activities in each project on my database.

          In my Time_Log Table I made a Portal from my Project_Activities Table to enter the number of Hours to each project project. I also put a button (Add Project) to create a record to the portal.

          This is the Portal looks like:

          Add Project

                                                                                                                                                                                                                                                                                                                                                                                       
          DateProject Code (Pop up) Used Values from field Project_CodesLot No.ActivitiesTimeSummary
                
                

          Below is the Script of the Add Project Button

          Allow User Abort [Off]

          Set Error Capture [On]

          Set Variable [$Time_ID; Value :Time_Log::ID_time]

          Go to Layout ["Project_Line"(Project_Activities)]

          New Record/Request

          Set field [Project_Activities::id_project; $Time_ID]

          Go to Layout [original layout]

          Refresh Window[]

          Now I want to create a report of each User 

          Under the Heading I want to see User Name and on the body will be the summary of the number of activities in each project

          User Name:_____________________

                                                                                                                                                                                                                                                                                                                                       
          Project CodeDec 2013January 2014February 2014March 2014
          Project 135610
          Project 226512

           

           

          • 2. Re: Cross tab report

            Hi Phil,

            I got it working now for how many days of playing.

            How can I include or get the summary of each individual user?