FileMaker Customer Support

Page 1 of 1

INTRODUCTION  TO  CHARTING     TABLE  OF  CONTENTS     ABOUT  THIS  DOCUMENT  

3  

FILEMAKER  CHARTING:    IMPORTANT  THINGS  TO  KNOW:  

3  

I.    INTRODUCTION  TO  CHARTING  TYPES  

4  

BAR CHART OR BAR GRAPH ...................................................................................................................4   LINE CHART................................................................................................................................................5   AREA CHART ..............................................................................................................................................5   PIE CHART ..................................................................................................................................................5   II.    INTRODUCTION  TO  CHARTING  TERMINOLOGY  

7  

CHART TITLE .............................................................................................................................................7   DATA TYPES ..............................................................................................................................................7   LEGEND .......................................................................................................................................................7   X-AXIS AND Y-AXIS ................................................................................................................................7   X-AXIS AND Y-AXIS TITLES ..................................................................................................................8   III.    FILEMAKER  CHARTING:  THE  BASICS  

9  

STEP 1: ADDING THE CHART TO THE LAYOUT.................................................................................9   STEP 2: SPECIFYING CHART SETUP OPTIONS ...................................................................................9   STEP 3: BROWSE MODE ...................................................................................................................... 11   IV.    FILEMAKER  CHARTING:    IMPROVE  CHARTING  

13  

LAYOUT PARTS ...................................................................................................................................... 13   CALCULATIONS ...................................................................................................................................... 16   FUNCTIONS ............................................................................................................................................. 17   SUMMARY FIELDS ................................................................................................................................... 18   V.    THE  ROLE  OF  LAYOUT  PARTS  IN  CHARTING  

18  

WHEN DATA IS BASED ON THE CURRENT FOUND SET: .................................................................. 19   WHEN DATA IS BASED ON THE CURRENT RECORD (DELIMITED VALUES): ................................ 19   WHEN DATA IS BASED ON RELATED RECORDS:............................................................................... 19   X-AXIS AND Y-AXIS TITLES IN CHARTS: ......................................................................................... 19   VI. CHARTING EXAMPLES  

21  

VII. FILEMAKER CHARTING FAQ'S  

24  

FileMaker Customer Support

Page 2 of 2

ABOUT THIS DOCUMENT This introductory document will help you better understand the new charting feature of FileMaker Pro, and will assist the creation and planning of FileMaker charts. This document focuses on the creation of basic charts rather than the details of chart configuration. For further details on configuration, please review the Charting topics listed in FileMaker Pro Help menu > New features in FileMaker Pro 11. Note: In this document, FileMaker Pro will refer to both FileMaker Pro and FileMaker Pro Advanced unless further specified. This document is specifically relevant to FileMaker Pro 11.

FILEMAKER CHARTING: IMPORTANT THINGS TO KNOW: • • • •

Chart must be placed in the correct part on the layout Charts can only be viewed in Form View or List View Data must be sorted if the layout part resides in a Sub-Summary part In some cases you might need a summary field

NOTE: Improper charting setup will cause unexpected and inaccurate results.

FileMaker Customer Support

Page 3 of 3

I. INTRODUCTION TO CHARTING TYPES FileMaker Pro now includes the ability to graphically compare data using its new charting feature. You have the ability to choose between four charting types: Bar (horizontal and vertical), Line, Area, and Pie. Bar chart or Bar graph A bar chart is typically used to compare two or more numerical values taken over time or under different conditions. For example, a bar chart could be used to compare annual sales totals by region. Or, it could be used to display and compare employee sales numbers for a specific month. As the scenarios imply, bar charts work best when comparing static values such as Employee A's sales numbers vs. Employee B's sales numbers. In FileMaker Pro, you have the ability to specify the bars of the chart to be displayed vertically or horizontally.

FileMaker Customer Support

Page 4 of 4

Line chart Line charts display a trend in data over time by connecting a contiguous series of data points with a line. Each data point within the contiguous series represents an individual measurement. In a sales example, a line chart can be used to take a company office, and chart the upward and downward trends in sales over a specific set of years. By contrast, a bar chart could be used to compare the differing sales totals from multiple offices within a region.

Area chart Area charts offer another way to compare aggregated data over time. The Area chart shows quantitative data and is used to compare summary data. In the example below, an Area chart is used to compare a company's sales numbers between 2009 and 2010 in a way that clearly shows the differences.

Pie chart Pie charts show the relationship between individual data series values to the total of all charted data. In a sales example, the total charted data could be Employee A's sales total for the month of July. The

FileMaker Customer Support

Page 5 of 5

individual data series values could be the type of sales packages sold represented by the percentage of overall sales for Employee A. So, for example, a Pie chart could show that 61.90% of Employee A's sales were sales package 1, 33.33% of Employee A's sales were sales package 2, and 4.76% of Employee A's sales were sales package 3.

FileMaker Customer Support

Page 6 of 6

II. INTRODUCTION TO CHARTING TERMINOLOGY After a chart is added in Layout Mode, the Chart Setup dialog window appears. Here, you can specify multiple chart characteristics including the x-axis and y-axis values, chart and axis titles, etc.

Chart Title This refers to the title of the chart as a whole. For a chart tracking employee sales numbers in the month of June, a chart title could resemble "Employee Sales Totals for June 2009." Data Types When configuring a chart in FileMaker, you specify whether the data being charted will be pulled from the current found set, if it is delimited data, or if it is related data. • Current Found Set - If this option is selected, data will be pulled from the current set of records being browsed. • Current Record (delimited data) - If this option is selected, data will be pulled from a delimited list of values from the current record. • Related Data - If this option is selected, the data that is charted will be pulled from data in a related table. Legend A chart legend refers to a key (represented by colors or symbols) that provides information on the charting graphic. For example, consider a line chart tracking data for three different offices. While each office is represented by a different line color, it would be difficult to distinguish the association between line colors and offices. To make this association clearer, a legend can be added to the chart to verify office name and its matching color. X-axis and Y-axis Most charts will be comprised of an x-axis, typically reserved for labels, and a y-axis, typically reserved for values. As an example, a line chart tracking the trends in retail sales on a monthly basis might have month names on the x-axis and the values (for example, monetary measurement) on the y-axis. When defining the x-axis or y-axis for a chart, FileMaker allows you to specify a particular field or calculation.

FileMaker Customer Support

Page 7 of 7

*Note: Pie charts use the moniker Label and Value as opposed to an x and y-axis. When defining the x-axis and y-axis, take into account the idea of data series and length. For example, a field with the numerical series [13, 4, 28, 17, 65, 2] would equal a length of six. If a simple chart were to be configured with this field, there would be six points on the chart. Because of this, it may be beneficial at times to specify summary fields and calculations for the x and y axis'.

X-axis and Y-axis Titles This refers to the specific titles for the x-axis and/or y-axis. A bar chart listing employee names on the x-axis might have the x-axis titled "Sales Team" or "Worldwide Sales Team."

FileMaker Customer Support

Page 8 of 8

III. FILEMAKER CHARTING: THE BASICS In this section, we will cover the basic steps of creating a chart in FileMaker. For this exercise, let us continue with the example of tracking regional sales while using the following solution: • A new file titled "Sales" • A text field labeled Company • A number field labeled Sales_Total • Four records with the following values:

Step 1: Adding the chart to the layout Enter Layout Mode and click on the charting tool in the tool bar, or select Insert > Chart Now, when the cursor scrolls over the Layout parts, the arrow icon will be replaced by a plus '+' symbol. Draw a generous sized rectangle in the Layout's Body part. Once this is completed, the Chart Setup dialog opens.

Step 2: Specifying Chart Setup options In the Chart Setup dialog, we can specify the chart title, the data series for the x and y axis, where from the data is pulled, etc. Also, you'll notice a chart icon displayed on the right hand side of the Chart Setup window. This is a generic representation of what your chart will look like and does not reflect actual data content.

FileMaker Customer Support

Page 9 of 9

Once the Chart Setup dialog opens, enter the following information: (See images below) • Chart Title - Regional Sales for June 2009 • X-Axis - Click on the arrow next to the x-axis field. Upon clicking this arrow, another window appears with the options to specify a field name or specify a calculation. Choose Specify Field Name, which will then open the Specify Field dialog. At the top of the dialog is a drop down menu to specify the Table where the field you are looking for is found. Since we only have one Table in this solution, we can leave the current default setting of Current Table. Next, select the Company field and click OK. • Y-Axis - Click on the arrow next to the x-axis field. Upon clicking this arrow, another window appears with the options to specify a field name or specify a calculation. Choose Specify Field Name, which will then open the Specify Field dialog. At the top of the dialog is a drop down menu to specify the Table where the field you are looking for is found. Since we only have one Table in this solution, we can leave the current default setting of Current Table. Next, select the Sales_Total field and click OK. • Use data from: In this drop down menu we have the option to choose Current Found Set, Current Record (delimited data), and Related Records. Keep this option at the default setting, Current Found Set.

FileMaker Customer Support

Page 10 of 10

• •

Step 3: Browse Mode After entering the appropriate information in the Chart Setup dialog, click OK to save changes and exit the window. Finally, go back to Browse mode and go to Form or List View. The chart should look similar to this: FileMaker Customer Support

Page 11 of 11

FileMaker Customer Support

Page 12 of 12

IV. FILEMAKER CHARTING: IMPROVE CHARTING As outlined in the previous section, the creation process for FileMaker charting can be straightforward. Not to be overlooked, however, are the roles that calculations, summary fields, and layout parts play in accurately displaying charted data. Will a chart that is placed in the header part display the same information as a chart placed in the body? Will that same chart display similar information as one placed within a sub-summary part? How can summary fields impact large sets of charted data? Before expanding on these ideas, let us first take a step back and examine these FileMaker features. If you are already comfortable with calculations, summary fields, and layout parts and would like to continue, please jump ahead to the next section, How Layout Parts Affect Charting Functionality.

Layout Parts By default, a FileMaker layout is comprised of three parts: Header, Body, and Footer. You can view this by creating a new database and entering Layout mode as shown below. Typically, a Header part is used for column headings, titles, and other information that would appear at the top of every page on a layout. Once the Header section has been customized, you can go to Browse Mode to view it. When printing, Headers will appear on every page unless a Title Header has been specified. If this is the case, the Title Header will appear solely on the first page followed by the Header on subsequent pages. A Footer part is located at the bottom of the layout and typically contains page numbers or dates. If a field is placed in the Footer, it will display data from the last record on that page. The Body part FileMaker Customer Support

Page 13 of 13

displays the individual records of the table. This layout part will contain the majority of fields in most solutions and involve the highest interaction. Aside from those mentioned above, FileMaker includes additional Layout parts: Leading Grand Summary, Sub-summary and Trailing Grand Summary. These parts are usually added to a layout for reporting purposes or for grouping and categorizing data. For a better understanding of how summary parts work, let's take a look at the example listed below. In this exercise, we will track a particular month's sales figures for ABC Company's sales team. A report will be created to display each employee's average sales total for a particular month. In this solution, let's include the following: • Create a new file titled "Employee_Report" • A text field labeled Employee • A number field labeled Sales • Four records for each employee similar to this:

As depicted above, employee records are spread throughout the solution. To group the data together for reporting purposes, we can add a Sub-summary part to the layout. In Layout mode, drag and drop the Sub-summary icon from the toolbar onto the layout and above the Body part. Once this part is added to the layout, the Part Definition dialog will appear. Choose the 'Sub-summary when sorted by' option and highlight the Employee field. This specifies the Employee field as the break field. Because of this, when records are sorted by the break field (in this case, the Employee field), FileMaker will display each employee and their associated records as a group.

FileMaker Customer Support

Page 14 of 14

After clicking OK to save changes, and exiting the Part Definition dialog window, the layout should look like this:

Next, duplicate the Employee field and add it to the Sub-summary part.

Finally, go to Table View in Browse Mode and sort the records by employee. The results should look similar to the following:

FileMaker Customer Support

Page 15 of 15

In FileMaker Pro 11, you can add a Trailing Grand Summary from within Table View in Browse Mode. At the top of the employee field column, place the cursor to the side of the employee field and click on the down arrow. From this menu, choose the option Add Trailing Group by Employee. Next, place the cursor to the side of the Sales field and click on the down arrow. Highlight the Trailing Subtotals option and then choose Average (sales). The average sales total for each employee and the average overall sales total are now depicted. This would be the same as going to Layout Mode, adding a Trailing Sub-summary part with a Summary field to the layout. As previously noted, understanding layout parts is not only essential for structural data purposes, but because charts will render differently in different layout parts.

Calculations In a basic chart configuration, you can easily type in a name for the chart and choose a specific field for the x-axis and y-axis values. However, when specifying chart options, you have the ability to apply calculations to titles, the x-axis values, and the y-axis values. Because of this, calculations can take a simple chart and turn it into a very powerful one. To take advantage of this, you will need to first make sure you understand the basics of calculations. In FileMaker, a calculation is a user-defined formula that can return the calculated value of text, number, date, time, timestamp or a container. Defining a calculation can include: • Constants - Constants are static values within a formula such as a field, literal text or numbers.

FileMaker Customer Support

Page 16 of 16

•

•

• •

Expressions - Expressions are a value (such as the constant number 2) or a computation that produces a value (such as 2 + 3) Operators - Operators are symbols that determine how to combine two or more expressions such as constants, functions and field values. For example, arithmetic operators include +, , /, * Information from other fields in the same record. Functions – Such as “Left(Name,1)” to get the first or leftmost letter from the Name field.

In a line chart solution tracking sales trends for different offices within a region, each office will have their own line chart identifying company name (the x-axis) and the trend in sales for each office (the y-axis) based on a specified year. When entering a title for this chart, entering the literal text "ABC Company's sales averages for 2010" puts us in a bind when rendering the chart for different offices or for the years 2009, 2008, and so on. To create a more flexible title, let’s assume that in the solution we have a text field titled Office_Name and another text field titled Year. We could combine these fields with literal text and use a calculation similar to the following: Office_Name & "'s sales averages for" & " " & Year Now, the chart title will be a little bit more flexible when rendered for find results including different offices or different years.

Functions FileMaker features a list of over a hundred pre-defined calculation functions that produce specific, useful values. Functions are divided into several categories including but not limited to: • Logical functions - (If, Case, IsEmpty, etc.) test for a condition to evaluate it as true or false. • Aggregate functions - (Average, List, Count, etc.) summarize data across multiple records • Date functions - (Day, DayName, Month, etc.) calculate and manipulate dates • Number functions - (Abs, Ceiling, Floor, Int, etc.) manipulate numbers

FileMaker Customer Support

Page 17 of 17

•

Text functions - (Char, Code, GetasText, etc.) analyze, rearrange, extract, and build text strings

For a full list of functions and categories, please review the FileMaker Pro Help topic "About Functions" or the following documentation: http://www.filemaker.com/downloads/pdf/fmp10_functions_ref.pdf

Summary fields Summary fields calculate subtotals, grand totals, and averages of fields. Summary field types include: • Total of • Average of • Count of • Minimum • Maximum • Standard Deviation of • Fraction of Total of Take advantage of Summary fields when data series are too lengthy to individually chart. In the Sales example listed below, a Summary field is used to summarize a possible data series from sixteen down to four.

V. THE ROLE OF LAYOUT PARTS IN CHARTING Understanding how Layout parts affect Charting is integral for making sure charted data displays properly. Based off of their placement within a layout, FileMaker charts will render information differently. Please review the following list to familiarize yourself with how FileMaker charts will implement data: FileMaker Customer Support

Page 18 of 18

When data is based on the current found set: Header: Current found set Footer: Current found set Body: Current found set Leading Sub-summary: All records in a sorted group Trailing Sub-summary: All records in a sorted group Leading Grand Summary: Every first record in a sorted group Trailing Grand Summary: Every last record in a sorted group When data is based on the current record (delimited values): Header: Current found set Footer: Current found set Body: Current found set Leading Sub-summary: First record in a sorted group Trailing Sub-summary: Last record in a sorted group Leading Grand Summary: First record in the found set Trailing Grand Summary: Last record in the found set

When data is based on related records: Header: Current record after the relationship join is applied Footer: Current record after the relationship join is applied Body: Body's layout after the relationship is applied Leading Sub-summary: Record set created after the join is applied and executed against the first record in a sorted group and the related table Trailing Sub-summary: Record set created after the join is applied and executed against the first record in a sorted group and the related table Leading Grand Summary: Record set created after the join is applied and executed against the first record in a sorted group and the related table Trailing Grand Summary: Record set created after the join is applied and executed against the first record in a sorted group and the related table X-axis and Y-axis titles in charts: Header: Current record Footer: Current record Body: Body's layout on the layout FileMaker Customer Support

Page 19 of 19

Leading Sub-summary: First record in a sorted group Trailing Sub-summary: Last record in a sorted group Leading Grand Summary: First record in a found set Trailing Grand Summary: Last record in a found set

Note: If there is currently no active sort, the chart will revert to charting data from the entire found set; it will behave identically to a chart in the Header/Footer

FileMaker Customer Support

Page 20 of 20

VI. CHARTING EXAMPLES For this exercise, the goal is to chart yearly student enrollment trends for a FileMaker K1 (Should this be K-12?) school. Let's assume the solution has the following: • A new file titled Enrollment • A text field labeled Enrollment_Year • A text field labeled Student_Name • A text field labeled Gender configured as a Male, Female checkbox • A collection of mixed records that will ultimately break down to: o 50 student records for 2005 enrollment o 70 student records for 2006 enrollment o 80 student records for 2007 enrollment o 40 student records for 2008 enrollment o 60 student records for 2009 enrollment For this scenario, a Line chart would display data most clearly. However, before adding the chart to the layout, we will first group the records by individual enrollment years to gain an accurate count of student enrollments per year. To do so, add a Sub-summary part to the layout and set the Enrollment_Year field as the break field. When sorted by the break field in Browse Mode/Preview Mode, FileMaker will group records by their enrollment year. Once the Sub-summary part and break field are added to group the data, we will need to determine how many records actually exist for each enrollment year. If a Summary field is created and the option to get a count of the Enrollment_Year field is selected, we can determine how many records exist for each year. (Don’t like the wording of the last sentence. Not sure how to rephrase). In File > Manage > Database, create a Summary field labeled Enrollment_Year_Count. Then, click on the options button to open the Summary field options window. Choose the option "Count of" and select the Enrollment_Year field from the available field list. Once these changes are made, add the Enrollment_Year_Count field to the Subsummary part. It is important that the Enrollment_Year_Count field is placed here so that its values reflect each section of the break field or in this case, a count per year. If the Enrollment_Year_Count field is placed in the layout's Body part, it will add an overall count of enrollment based on the entire found set. Next, click on the charting tool in Layout Mode and draw a generous sized rectangle in the header. In the Chart Setup window, enter the following data: FileMaker Customer Support

Page 21 of 21

• • •

•

• •

Chart Type: Line Chart Title: FileMaker K1 Enrollment 2005-2009 X-axis: Click on the arrow next to the x-axis field and select Specify Field Name. From the current table, choose the Enrollment_Year field. Y-axis: Click on the arrow next to the y-axis field and select Specify Field name. From the current table, choose the Enrollment_Year_Count field. Use data from: Current Found Set Show data points for groups of records when sorted checkbox is checked

Finally, go to Browse Mode and sort the records on the break field. The Line chart should now display the trends in student enrollment for 20052009. Now, let's say that we wanted to compare total enrollment with enrollment by gender. To ensure that we are charting accurate data, the first step would involve the creation of a "flag field" to determine male or female records for numerical purposes. Creating a Summary field count on the Gender field will not work because the Summary field will not be able to distinguish between male or female. It would simply verify if the Gender field had a value in it and then add the record to its overall count. To flag the appropriate gender, we will use a calculation field along with the function GetAsBoolean. GetAsBoolean is a function that returns the numeral 1 if the user specified calculation is true or 0 if false. A Summary field will then add the results to produce a total number of males who enrolled per year. To start, go to File > Manage > Database and add a calculation field called Boolean_Male. The calculation will look like the following: GetAsBoolean ( Gender = "Male") Next, add a Summary field called Boolean_Male_Count. In the Summary field options, choose "Total of" and select the Boolean_Male field. After saving changes, place the Boolean_Male_Count field in the Sub-summary part so that the totals reflect the break field's per year organization. Placing this field in the layout's body part will result in a total of all male records throughout the entire found set. Repeat these steps for the Female records so that there is a Boolean_Female field to flag female students and a Boolean_Female_Count field to add the total of female records. In the Chart Setup dialog window, add the following values to the field names: • Chart Type: Area FileMaker Customer Support

Page 22 of 22

• •

•

•

•

• •

Chart Title: FileMaker K1 Enrollment 2005-2009 X-axis: Click on the arrow next to the x-axis field and select Specify Field Name. From the current table, choose the Enrollment_Year field. Y-axis: Click on the arrow next to the y-axis field and select Specify Field name. From the current table, choose the Boolean_Female_Count field. Click on the '+' sign to add an additional data series to the Y-axis: Click on the arrow next to the y-axis field and select Specify Field name. From the current table, choose the Boolean_Male_Count field. Click on the '+' sign to add an additional data series to the Y-axis: Click on the arrow next to the y-axis field and select Specify Field name. From the current table, choose the Enrollment_Year_Count field. Use data from: Current Found Set Show data points for groups of records when sorted checkbox is checked

Go to Browse Mode and sort by the break field to view the chart. While a Line chart could be used to compare enrollment trends from 2005-2009, the Area chart is useful in this scenario to compare the quantitative data between male and female enrollment vs. total enrollment. If you wanted to just show female or male enrollment, since the data is being pulled from the current found set, it is not necessary to create additional charts. You can perform a Find for "male" or "female" in the Gender field, sort records by the break field and view a chart specifically for the male or female enrollment.

FileMaker Customer Support

Page 23 of 23

VII. FILEMAKER CHARTING FAQ's Q: Will my chart appear in a web browser if I host my database via Instant Web Publishing? How do I edit a chart in a web browser? A: If a client connects to an Instant Web Publishing hosted database, the chart will appear in the web browser. However, the client is actually seeing a bitmap image of the chart. Because of this, the chart itself is un-editable over the web. Q: Can I place a chart in a portal? A: Charts will not appear in a portal. Q: Can I drag/drop or copy/paste my chart? A: Copy and Pasting or Dragging and Dropping a chart while in Browse or Find mode will result in a bitmap image of the file. Q: Will my chart display in my runtime solution? A: Charts will not render in a runtime solution. Q: Will charts display if I open my database file with a copy of FileMaker Pro prior to FileMaker Pro 11? A: Charts will not display if the database is opened in a version preceding FileMaker Pro 11. Instead, the Chart will appear as a box outline with a large "X." Q: How will FileMaker chart my repeating fields? A: When charting repeating fields, only the first value of the repeating field is used.

FileMaker Customer Support

Page 24 of 24