5 Replies Latest reply on Nov 5, 2015 7:03 AM by keywords

    Reporting Summary

    Annette

      Hi.  Was wondering if I could get a little help on a report I'm trying to create.  I'll give a brief description of what I'm working with. 

       

      I have created a database for booking rooms within different buildings.  On the table Room Bookings there is a record created for every room, for every day within the year in half hour increments.  So...for each day, each room has 21 records.  There are fields for time, date and room number already completed.  There are additional fields for Booked by, booked for and Increment (which autocompletes to .5 when someone books a room given its a half hour time slot).

       

      image2.png

       

      When a person wants to book a room they see a layout showing the rooms for the day, they enter the times they want the room (e.g. from 3-4 pm) and a find with a loop happens in the background, if the room is not already booked (booked by and booked for fields blank) then it books it for the person and sets the increment field to .5 .

       

      This all works like a charm as I need it to.

       

      My issue is when I try to create a report to give me the percentage of room usage.  I also have a field which is a summary (Usage) of the increment field.  So if I ran the report to find me the usage of all the rooms in a building for a date range and sort by room it tells me a total of how many hours the room was booked for.  Works fine.  (See image below.  That report is for a three day period)  Now I need to do the next step.  A room has the capacity to be booked for 7.5 hours in a day.  So I need to divide the total Usage by 7.5, multiply by 100 to get the percentage....then divide by the days within the date range (which I get using a variable in my script).  My only issue is while the usage field gives me the hours per room in the report, the other field i create to give me the average percentage gives me a total for ALL the rooms booked. 

       

      Using the example below what I need is the following:  Room 1 was booked for 4 hours over 3 days. 

      4 / 7.5 = 0.5333 X 100 = 53 .. but then the average over the three days within that report is 53 / 3 = 18% (rounded)

       

      What calculation / summary, whatever do I need to do so that I can get an answer per room in the below report as opposed to a total. of the 6.5 hours?

       

       

      image.png

       

      THank you SO much for any help offered / easier ways of doing this, I really appreciate it.  Apologies for the long winded explanation.

        • 1. Re: Reporting Summary
          jbrown

          Morning.

           

          I think I'd suggest doing a percentage calculation field (maybe PercentUsed) for each record:  4 / 7.5 * 100 in your example's case.

          Then do another summary field that takes the AVERAGE of the PercentUsed) field.

           

          I think that would get you what you want. Use this summary field along side the total hours summary field or replace the total hours summary field.

           

          Thinking more about it, You'd need to run a report that searches for just room 1 over the three days and then sort by the break field to get the correct average percent across that date range. If you put the summary fields in a break field that was based on the room number (or ID), you'd get the percent for all rooms over the same period of days. IS that what you're looking for?

          • 2. Re: Reporting Summary
            Annette

            I thought I tried that and it was using 6.5 (the total of the two as opposed to the one room) and putting the same figure in every record on the report.  I'll try again. 

            • 3. Re: Reporting Summary
              jbrown

              What's your break field / subsummary line based on? If you base it on the room number and then sort by room number, the summary fields will give you the total per room number (or average per room number)

              • 4. Re: Reporting Summary
                Annette

                Thank you!!  It was the break field that I didn't have as I was using the summary field type as opposed to the GetSummary calculation.  You are a star!! 

                • 5. Re: Reporting Summary
                  keywords

                  What Jeremy says about break fields should work. You can look at it either as:

                   

                  1.     Total room usage over 3 days / total room availability over 3 days * 100:  4 / 22.5 * 100 = 17.8%

                  2.     Average of room usage over each day: Day 1 53.33%, Day 2 0%, Day 3 0% = 53.33 + 0 + 0 / 3 = 17.8%

                   

                  Just make sure that whichever way you do it your calcs are room by room.