5 Replies Latest reply on Mar 4, 2015 4:17 PM by philmodjunk

    Creating a Report

    LadyofUC

      Title

      Creating a Report

      Post

      I've used Filemaker as a simple database for years, but now I want to create a simple report. My data is currently in Excel. Attached is a JPG depicting an example of what I hope to accomplish. Can anyone please tell me if Filemaker can accomplish my goals, and if so, how should I proceed?

      Thanks, Annacrying

       

      Concatenations.jpg

        • 1. Re: Creating a Report
          philmodjunk

          Is this the actual format that you want? Doesn't seem very flexible or easy to read when you get more than one item of data in the same cell like that.

          And your "desired outcomes" show data not present in the first image so that creates questions about the actual structure to your data. Is this all in one table, in a system of related tables? or?

          • 2. Re: Creating a Report
            LadyofUC

            Thanks so much for your questions. And I agree with your critique. Unfortunately, each chart will be inserted in a report whose format is set. The data may have mistakes, since it is for illustrative purposes only. I'm trying to show the format of the charts that I need to produce. I need to know if Filemaker can cope with the concatenated columns, and if so, how?sad

            • 3. Re: Creating a Report
              philmodjunk

              It can using a calculation field, possibly using the ExecuteSQL function, though that's not the only option.

              But note that you haven't really spelled out enough detail here for me to comment in detail. A I previously noted, you have data shown in your "output" that is not present in your "input" so there appears to be data from another table that is "matched" to the data shown in your first screenshot. How that data is organized into tables and what "rules" control how you match up this data are all details not yet shared in this thread.

              • 4. Re: Creating a Report
                LadyofUC

                Dear PhilModjunk,

                            Thanks so much for your input. Attached is a corrected image.

                            Though I am a software developer, I have no coding experience with Filemaker. I was hoping that I could create the tables using Layout in Filemaker. Please let me know how I would get started using ExecuteSQL. It sounds like you may know of other options. If so, please let me know what is, in your opinion, the fastest route to accomplish my goal as I have a limited amount of time to devote to this project.

                Thanks so much.

                • 5. Re: Creating a Report
                  philmodjunk

                  What your are describing are "cross tab" reports. They aren't the simplest things to create and FileMaker and there are much simpler to set up formats for reporting this info. What still I have a problem with is what you intend to do when, say you have 5 or 6 instructors or courses to list in a single "cell". Saying that "the design is already fixed" doesn't change the fact that this report design is very inflexible and likely to fail in many situations.

                  Report 1 has one row for each course, one column for each quarter and the intersection of quarter and course is where you list the instructors that (apparently) teach that course for that quarter.

                  Report 2 has one row for each instructor, one column for each quarter and the intersection of quarter and instructor is where you list the courses taught by that instructor during that quarter. (apparently, a given instructor doesn't teach a lot of different courses for the same quarter....)

                  Using Report 1 for an example, (You should be able to generalize from it to produce Report 2),

                  Set up a list view layout based on YourTable. Remove the body layout part. Add a sub summary layout part "When sorted by Course". Inside this sub summary layout part (it can print above or print below, makes no difference). put The course number field and a calculation field:

                  ExecuteSQL ( "SELECT Instructor FROM YourTable WHERE Course = ? and Quarter = ?" ; "" ; " | " ; YourTable::Course ; "14F" )

                  Be sure to sort your records by Course or you will have a blank layout. (Using a sub summary layout part and no body layout part allows you to "condense" your list down to one row for each unique value in the specified "when sorted by" field.

                  WHen you get the correct list of names in a column to the right of the course number and not the dreaded ? result. You can make copies of this calculation and change the quoted quarter ID to different values and use these additional calculation fields to set up your additional columns of data. Note that you can replace the quoted "14F" with a reference to a global field and then you can dyanamically control what quarter is specified for a given column.

                  Note that you may need to enclose field and/or table names inside double quotes in this query if your name contains characters not allowed in SQL or if the name is also a reserved word in SQL. That looks like this: WHERE \"course\" = ?

                  For more on using ExecuteSQL:

                  SeedCode's SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree

                  FileMaker Inc's reference Doc on SQL: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

                  Caulkins Consulting, Home of Adventures In FileMaking