11 Replies Latest reply on Oct 25, 2011 8:48 AM by EmilyShep

    Complex Report with Groups and multiple summaries

    EmilyShep

      Title

      Complex Report with Groups and multiple summaries

      Post

      Hi,

      I want to make a report that can export to excel and will group information based on the cost code, I want a column to show total hours on that cost code used to date, and then I want another summary of what was input in the current/most recent date.

      Here is a screenshot of what I would like to do:

      Screen_Shot.JPG

        • 1. Re: Complex Report with Groups and multiple summaries
          philmodjunk

          Check out the "group by" option you can use when exporting. If you use Cost Code as your "group by" field and have your records already sorted by this field, you should be able to produce an export of just one line for each cost code.

          • 2. Re: Complex Report with Groups and multiple summaries
            EmilyShep

            When I have Group By "Cost Codes" it then only shows the entrie for 1 employee on 1 date

            Project   Number Cost Codes Description Hours Employee ID Work Date
            10009                   
            15-1 SAFETY   5
            SHEPE 07/08/2011
            10009                   
            50-H1 SUPERVISION  21 SHEPE 07/08/2011
            10009                   
            520-1-H SLEEVING  10 SHEPE 07/08/2011
            • 3. Re: Complex Report with Groups and multiple summaries
              philmodjunk

              Leave the Hours, EmployeeID and Work date fields out of your export.

              Add whatever field you are using for Estimated Hours as it appears to be working for you before.

              You'll then need to include either summary or calculation fields that correctly return the other three columns of data (hours to date, hours this week and % complete).

              I don't know how you've structured your data, so I can't be more specific than that.

              • 4. Re: Complex Report with Groups and multiple summaries
                EmilyShep

                I'm having a hard time figuring out how to get the summary field for hours input for the most current week only, any ideas?

                • 5. Re: Complex Report with Groups and multiple summaries
                  philmodjunk

                  An unstored calculation field defined like this:

                  If ( ( Get ( CurrentDate ) - DayofWeek ( Get ( CurrentDate ) ) = ( Work Date - DayofWeek ( Work Date ) ) ; Hours )

                  Will only return the hours for the current week and then your summary field can summarize this field for your hours for current week total.

                  • 6. Re: Complex Report with Groups and multiple summaries
                    EmilyShep

                    Ok I have both summary fields working, but the report is being really finicky... I'm not sure why sometimes it only shows the code once with the info I need but other times it shows the codes multiple times.

                    Project   Number Cost Codes Description Est Hours Hours TD by Cost Codes Hours TW by Cost Codes
                    10009 15-1 SAFETY  90 20
                    10009 50-1 SUPERVISION  3456 23
                    10009 510-1-H MATERIAL HANDLING  756 29
                    10009 50-1 SUPERVISION  3456 6 6
                    10009 510-1-H MATERIAL HANDLING  756 10 16
                    10009 15-1 SAFETY  90 12 28
                    10009 50-1 SUPERVISION  3456 12 40
                    10009 510-1-H MATERIAL HANDLING  756 12 52
                    10009 15-1 SAFETY  90 20 72
                    • 7. Re: Complex Report with Groups and multiple summaries
                      philmodjunk

                      Make sure that you've sorted your records by Cost Codes so that they are grouped by this field's value before you export the records.

                      • 8. Re: Complex Report with Groups and multiple summaries
                        EmilyShep

                        That did the trick!

                        I have one more thing I would like clarification and one last question and it should be working perfectly!

                        For this equation "If ( ( Get ( CurrentDate ) - DayofWeek ( Get ( CurrentDate ) ) = ( Work Date - DayofWeek ( Work Date ) ) ; Hours )" I'm having a hard time figuring out what it is calculating... will it always use the most current date as the "weekly" values? What it looks like to me is that it will only get the values entered the same day? Although it is working today for values I entered yesterday...

                        My other question is, I'm trying to calculate the % complete... (hours TD/est. hours)*100, but this isn't working... in my export the column i am exporting for the Hours TD shows as "Hours TD by Cost Code". Is there a way I can get the % complete field to calculate the percentage using that field?

                        • 9. Re: Complex Report with Groups and multiple summaries
                          philmodjunk

                          As long as the calculation field is unstored, Get ( CurrentDate ) returns the date from your system clock so this calculation always updates with the current date. The calculation subtracts the day of the week from this date (a number where 1 = Sunday, 2 = Monday, etc.) this converts all dates to the Saturday date of the week before. Dates that fall in the same week will thus show the same "Saturday Date" on both sides of the equal sign and your Hours value is then returned. The Field will be empty for all cases where this is not true.

                          Hours TD is a summary field, correct?

                          If so, referring to it in a calculation returns the grand total, not the current cost code based sub total. Use get Summary to get the sub totals:

                          get Summary (hours TD ; Cost codes)/est. hours * 100

                          • 10. Re: Complex Report with Groups and multiple summaries
                            EmilyShep

                            You are extremely helpful! Thanks for everything Phil!

                            • 11. Re: Complex Report with Groups and multiple summaries
                              EmilyShep

                              Ok so I know it has been a while since this last post but there is something wrong with my program.

                               

                              The "Hours for Current Week" are adding up each week. Its not distinguishing that only the hours entered in the current week are to be kept in the field.

                               

                              I have been trying to play with it and figure it out for hours so any help is appreciated.

                               

                              Thanks