9 Replies Latest reply on Aug 7, 2015 5:54 AM by philmodjunk

    Reports_Crosstab

      Title

      Reports_Crosstab

      Post

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      FieldsTypeComments
      UserIDNumberIndexed, Auto-enter serial
      AccountNameText 
      CreationAccountTextCreation Account Name, Can't Modify
      First NameText 
      Last NameText 
      FullNameCalculationFirstName & " " & TextStyleAdd(LastName; Bold)

       

      DailyReports Table

       

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      FieldsTypeComments
      DailyReportsIDNumberIndexed, Auto-enter serial
      DailyReports_UsersIDText 
      DailyReports_UserName Indexed, Creation Account Name
      ProjectCodeText 
      DateDate 
      ActivitiesText 
      ActivitiesTimeNumber 
      Activities Time SummarySummary=Total of Activities Time
      Month_1DateGlobal
      Month_2CalculationUnstored, from DailyReports,= Date ( Month ( Month_1 ) + 1 ; 1 ; Year ( Month_1 ) )
      Month_3CalculationUnstored, from DailyReports,= Date ( Month ( Month_1 ) + 2 ; 1 ; Year ( Month_1 ) )
      Month_4CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 3 ; 1 ; Year ( Month_1 ) )
      Month_5CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 4 ; 1 ; Year ( Month_1 ) )
      Month_6CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 5 ; 1 ; Year ( Month_1 ) )
      Month_7CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 6 ; 1 ; Year ( Month_1 ) )
      Month_8CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 7 ; 1 ; Year ( Month_1 ) )
      Month_9CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 8 ; 1 ; Year ( Month_1 ) )
      Month_10CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 9 ; 1 ; Year ( Month_1 ) )
      Month_11CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 10 ; 1 ; Year ( Month_1 ) )
      Month_12CalculationUnstored, from DailyReports,=Date ( Month ( Month_1 ) + 11 ; 1 ; Year ( Month_1 ) )

       My Two tables are connected by UserID  = DailyReports_UserName

      I made a Table of Occurrence of my DailyReports table and I named it DailyReports_ProjectCodes_Users and is connected to my DailyReports table by ProjectCode (=)

      I also created a layout and I named it ProjectCodes_Users which I have a Layout Parts Subsummary when sorted by DailyReports_UserName and  Subsummary when sorted by ProjectCode. Also under this layout I have a also setup a single portal showing related records from DailyReports_ProjectCodes_Users (DailyReports_ProjectCodes_Users::ProjectCode = "DMF") and I put a field Activities Time Summary

      Is there a way that That I can Catch the UserNames to be on the top of my report like the attached?

       

      Thank you so much

       

       

      Untitled.png

        • 1. Re: Reports_Crosstab
          philmodjunk

          Not when rotated at that slight angle, no. But if you are OK with displaying them rotated exactly 90 degrees, then that should be possible.

          • 2. Re: Reports_Crosstab

            Hi Phil,

            Thank you for the response. Yes Im very Okey with 90 degrees. What are the things I need to do to put my users in my Header?

            Do I need to make Global Fields to capture the users name? Please guide me.

            Thank you so much.

            • 3. Re: Reports_Crosstab

              Hello Gurus,

              Here I am again begging for help to my problem above.

              Can you please help and direct me on how can I resolve my problem.

              Thanks a Lot.

              Rose

              • 4. Re: Reports_Crosstab
                philmodjunk

                The number of columns will not be flexible, but you can put a series of one row portals to the users table in order to display the user names. The relationship can use the X operator to match to all users and the first portal would specify an initial row of 1 the next and initial row of 2 and so forth.. You'd resize the portal row to be quite tall and then use Arrange to rotate the field sideways within the portal. Move the portal a few pixels and make sure that the field moves when the portal moves. You can then resize the portal row to fit tightly around the rotated field and make the borders of the portal invisible if you want.

                • 5. Re: Reports_Crosstab

                  Hi Phil,

                  Thank you. I'm done with your first instruction.

                  How can I match each total project Hours done by each Users?

                  Thank you.

                  • 6. Re: Reports_Crosstab

                    Here I am gain,

                    I've been struggling for the whole day in order to accomplish what I want to match the Users Hours to their specific activities  but I didn't get any luck.

                    I made a table of occurence to my DailyLabReports Table and I named it DailyReports_ProjectCodes_User which I connect to my Users Table (Users::AccountName X DailyReports_ProjectCodes_User::DailyReports_UserName)

                    I made also a single portal setup from TOC DailyReports_ProjectCodes_User then I filter it Using DailyReports_ProjectCodes_Users_New::ProjectCode = "Rosalie" ( As my User) then I put the field Activities Time Summary inside the portal)

                    Can you please help me.

                    Thank you.

                    • 7. Re: Reports_Crosstab
                      philmodjunk

                      I think the intersection of a Project Row and a User column should report the total time worked on that project by that user? Is that correct?

                      There are two commonly used approaches:

                      One row portals in a list view of Projects can show the totals with a portal filter limiting the value of the summary field to a particular user.

                      A series of unstored calculation fields  are used to create the columns, each an ExecuteSQL query that pulls together and summarizes the same data as the filtered portal.

                      Note that GetNthRecord can access data from your Users table used for the column headers and these can be used as parameters in the SQL queries or as values in portal filter expressions.

                      Which method would you like to attempt here?

                      • 8. Re: Reports_Crosstab

                        I would appreciate if you can give me an example ExecuteSQL

                         

                        Thank you so much much

                        • 9. Re: Reports_Crosstab
                          philmodjunk

                          ExecuteSQL ( "
                          SELECT Sum ( ActivitiesTime ) FROM DailyReports
                          WHERE
                              \"DailyReports_UsersID\" = ? AND ProjectCode = ?
                          "; "" ; "" ; GetNthRecord ( UserTablePortalTO::UserID ; 1 ) ; ProjectCode )

                          The only part that changes for each field is the number in red. This example is for the first column of data. Change this value to 2 for the second column of data and so forth.

                          This query is based on some important assumptions that may not be the case for your solution.

                          First, that you have a table with one record per project that has a field named ProjectCode to uniquely identify each project. These eSQL calculation fields would be defined in this table. The following relationship must then exist in order for the GetNthRecord function calls to work correctly:

                          Projects::anyField X UsersTablePortalTO::anyField

                          The X is used in place of = as the operator, but you can select any field in either table as the match field to use in this relationship.

                          If you add a pair of global date fields, you can also modify these SQL queries to also limit the totals calculated to a range of dates specified in those global date fields.