3 Replies Latest reply on Oct 3, 2014 9:12 AM by philmodjunk

    Formatting reports

    Holamon

      Title

      Formatting reports

      Post

      I am new to filemaker and am trying to build a report that looks like this: 


      School #1

      Year              2001            2002             2003           2004
      Data 1            ####            ####             ####           ####
      Data 2            ####            ####             ####           ####

      School #2
      Year              2001            2002             2003           2004
      Data 1            ####            ####             ####           ####
      Data 2            ####            ####             ####           ####

      etc...

      The data looks like this: 

      School   Year Data1  Data2  Data3 Data4
      School#1 2001 ####   ####   ####  ####
      School#1 2002 ####   ####   ####  ####
      School#1 2003 ####   ####   ####  ####
      School#2 2001 ####   ####   ####  ####
      School#2 2002 ####   ####   ####  ####

      etc, etc....

      I would like for there to be a new report for every school. 

      I cannot find a way to do this although I am sure it is possible.  Any help would be greatly appreciated!

        • 1. Re: Formatting reports
          philmodjunk

          What you describe is called a "cross tab" report where data from different records has to be arranged into columns as well as rows.

          There are a number of ways to do that, been doing it since FileMaker 3. The following method requires FileMaker 11 or newer:

          Create a table with one record for each school. You may have such a table already as such can be useful in a number of different ways.

          Link that table to your table of data like this:

          Schools::__pkSchoolID = SchoolData::_fkSchoolID

          Create a list view layout based on Schools. In the body of your layout add the fields needed to identify the school (School #1). Add a one row portal to SchoolData below it. Give it this portal filter expression: SchoolData::Year = 2001 (or use a global field in place of the literal 2001 value.) Resize the portal row to make the row tall enough to hold the fields for Data1, Data2, Data3 in a single column.

          Now make a copy of this portal, put it next to the first portal and change the portal filter expression to use 2002 instead of 2001.

          Repeat until you have the needed columns of data, each for a different year.

          One technique to consider is to define a global field, gYear, assign it the value 2001 and use SchoolData::Year = Globals::gYear as the first filter expression, then use SchoolData::Year = Globals::gYear + 1 for the second, SchoolData::Year = Globals::gYear + 2 for the third and so on. Then you can adjust for different ranges of years by editing the value of gYear and refreshing the window.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Formatting reports
            Holamon

            I am having trouble making the data elements into columns instead of rows.  If you look at my original question I am transposing the data from rows to columns.  I may be beyond help since I am so new to this software.  Are there any books out there for beginners to learn this software?

            Holly

            • 3. Re: Formatting reports
              philmodjunk

              There are many training resources, hard copy and electronic, even You-Tube videos that you can access or purchase. I even have a recently launched series of instructional database files that can be downloaded from a FaceBook page (see link in my first post), though the first files issued do not address this specific topic.

              FileMaker Inc. offers this free training resource: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

              If you take a new look at my last post, it's by setting up a series of One Row Portals that you get your data elements transposed from rows into columns. My post assumes that your example data shows a set of 5 records, one record to each row.

              But if you have never set up a portal before, learning how to set up one is probably the next new thing you need to learn. (If you are familiar with MS Access, portals are the FileMaker equivalent of a Sub Form or Sub Report.)