10 Replies Latest reply on Mar 23, 2015 4:20 PM by PatriciaAngelini

    reports that go vertical

    PatriciaAngelini

      Title

      reports that go vertical

      Post

      Hello.

      I have a report that lists daily activities in a vertical format. I need to also produce this in a horizontal manner. For example, now it looks like

      Monday

      Tuesday

      Wednesday

       

      I need for it to be like:

      Monday        Tuesday          Wednesday

       

      How do I do that?

      Thank you.

       

       

       

       

      Screen_Shot_2015-03-03_at_9.23.36_AM.png

        • 1. Re: reports that go vertical
          philmodjunk

          That would appear to specify what we call  a "cross tab" report. But besides 5 or 7 columns for days of the week, what other data does this report need to display and where? What other columns? What will one row of data be?

          • 2. Re: reports that go vertical
            PatriciaAngelini

            The first column needs to be the event listing

            A row of data will be the event name, start time, end time, and location.

            • 3. Re: reports that go vertical
              PatriciaAngelini

              Is there a link to where I can learn about cross-tab reports?

               

              • 4. Re: reports that go vertical
                PatriciaAngelini

                I viewed the Lynda.com video on cross tabs and I am confused as where to put the summary tabs. I created a calculation that has the day of the week but I don't know how to have the presentations "presentationName" go across.

                • 5. Re: reports that go vertical
                  philmodjunk

                  There are multiple discussions of "cross tab" reports that you can search for in this forum. There is more than one approach to how you set this up as you can use one row filtered portals, calculation fields (sometimes using ExecuteSQL) or just straight fields from a related table to produce your columns of data.

                  I'm still having trouble understanding how you want your report to look like. You say that you want each row to list an event, but in your example, each event only appears a single time so I'm not sure how that works out in columns based on days of the week.

                  Is it possible that you consider Adrenaline Film Project, "screening", "after party" and "review" to be different parts of a single event? Are they a single record or three different records in your table of events?

                  • 6. Re: reports that go vertical
                    PatriciaAngelini

                    Ok.. I watched the cross-tabs video on Lynda a few times so I could better explain the problem. I am looking to do a cross-tabs report that has the days of the week as columns. I am looking to put presentations on the proper day/time slot so we can have a week at a glance report.

                    A presentation has a date and a time. I have added a field so I have the day of the week that presentation is showing. The calculation I used was:If ( PresentationDay ; PresentationMonday ; "")  I did this for every day of the week. I have a snapshot of how the report is set up.

                    It is not working and I don't know what I did wrong. 

                    I hope this helps...

                    • 7. Re: reports that go vertical
                      philmodjunk

                      A question remains unanswered: What do you want to see in each row of your report? Will each row represent an entire week? Will each column list both the name of the event, the start and end time, plus the location?

                      That doesn't seem consistent, however with your single, separate column of start and end times.

                      Your sample report does not show this, but do you have different events with the same date, start and end times so that each row represents a "time slot"? And each "day of the week" column lists an event and location or is empty if there is nothing scheduled for that date and time slot?

                      And will you need to see this in browse mode on your screen or only as something that you print? (There's an option for printed output and preview mode that doesn't work for browse mode viewing.)

                      • 8. Re: reports that go vertical
                        PatriciaAngelini

                        Each row will represent a segment of time....

                        8 am

                        10 am

                        Noon

                        On any given day, there may be multiple events at the same time or 30 minutes apart. One film my start at 6:15 pm and another may start at 6:45 pm.

                        We will need to see this in Browse mode and I also will need to be able to print it.

                        • 9. Re: reports that go vertical
                          philmodjunk

                          Is your "segment of time" two hours?

                          You'll need a table with one record for each row of your report, one record for each segment of time. Your relationship might be this:

                          It seems that you have an overlapping time interval problem here. If the Time interval for your segment overlaps a portion of the time interval for your scheduled film, the record should show in one of the portals for that time interval. One way to handle this is to set up a return separated list of Time segments in a match field of both the Time Segment and Event tables. You'll have to figure out how small of a time interval to use. Looks like 15 minute intervals. So a Two Hour Time Segment Record might have a text field with this list:

                          8:00
                          8:15
                          8:30
                          8:45
                          9:00
                          9:15
                          9:30
                          9:45

                          Then the relationship might be:

                          TimeSegment::TimeList = Event::TimeList

                          You'd then set up a portal filter that specifies the day of the week or a date:

                          DayOfWeek ( Event::EventDate ) = 2 // Monday is 2nd day of week

                          would filter for Monday.

                          Either a looping script or recursive custom function could generate such a list of Time values for a given TImeSegment or Event record.

                          • 10. Re: reports that go vertical
                            PatriciaAngelini

                            Wow... I will try