[Music] iso filemaker magazine the professionals resource for filemaker know-how hey there this is matt petrowski for iso filemaker magazine and in this video we're taking a look at filemaker reporting fundamentals these are these are the things that you need to know if you want to build pretty much any type of report let's take a look at my desktop all right thanks for joining me here on my desktop we've got a web page and i'm simply at a website called data.gov and in the united states it's just a place where the federal government accumulates data and i'm doing this because it provides me with an opportunity to show you how to get both data in and then how to report based on that data so what we're using right here is we are using the monthly page views to cdc.gov and as all of us know we've recently had a global pandemic and we can see if those numbers went up or not but right now what we get was we get some basic data i've done the download i won't walk you through that we can see right here that we've got the file as a csv comma separated value file now if i hit the space bar here on the macintosh we get some data that comes up and we can sort of preview this data on windows i don't know how you would preview this data other than open it in a text editor but what i'm interested in is the fact that i don't always have to enter in the names of fields if i'm coming from a comma separated value file or an excel spreadsheet many times the names for of fields is already done for us so we don't necessarily have to create these names in a blank filemaker database we can actually just use the data itself if it already has column headers and have filemaker do the work so what i'm going to do here is i'm going to drag this on top of filemaker and this will work with a shortcut on windows or a shortcut on the macintosh or an alias as they call them and as i drag this what we have is filemaker automatically comes up with a window where filemaker wants to now convert this now we can filemaker adds in a special word you can see right there it adds in the word converted and we don't want that word we actually want to call this whatever we want in terms of the file now the interesting thing about filemaker is whatever the name of the file is typically when you create a new file then filemaker automatically creates one table for you and that initial table has the same name as the file the name that you give the file in this case um i don't know what it might be the case we'll see what happens here um i'm just going to call this cdc views just for fun now filemaker is going to come up with our import dialog i'll make this a little bit smaller so we can see on screen what we can do is over here on this side we get to choose whether we use things as field names right there or whether we use them as data now i can skip to any given row when i import this by simply clicking here and moving through the data i also get to preview the data quite clearly in this area so i'm going to move all the way back to the row number one and for this one i'm going to say use as field name so what we'll see is filemaker automatically switched over here and it's going to map all of these now we can see that there is this target within the file so we're coming from the csv file into this file and we can name this whatever we want in this case i'm going to name the same name cdc um actually i'll just call it cdc data and we will click convert right there it will bring everything in now this is actually a great technique if you need to let's say you have an excel spreadsheet that has 30 or 50 rows of data you do not have to take the time to ever repeat type something into filemaker if you're able to find get filemaker to do the work so many times i will even do this temporarily then i will go up to the manage and then go to the database and i will select that and so with this here if i had a separate filemaker file i can in fact just select all of these fields click on the copy option that we have in this dialog and then just go paste these fields into my actual filemaker database so use filemaker's willingness to convert a file for you even if it's just to get the names of fields so for the rest of this video what we're going to be doing is we're going to be taking a look at converting this into a report taking this raw data that we have here on screen and actually making sense of it and talking about all the aspects that lets us put things in a very clear format ready for us to generate a report okay so let's turn this data into a report that will visually make sense to us and be appealing and we'll discuss all the aspects of reporting as we go across this solution now one of the things that i'm going to do is i'm going to make a notes field so i'm going to we're going to make the assumption that let's say for this month of october in 2003 there were this many page views and maybe those page views were lower and we want to add a month field now there's a couple of ways you can do this from filemaker you can always click here in this table view you can click right here and add a field you can also always go up to the very familiar manage database and in that area we can always add a field right here but just for fun i'm going to click on the little plus right here because i already have the view and i will just call this notes i don't need to change the field type but if i did i would select on the little arrow here and then i would be able to choose something like going to the field and then going over to the field type and as we can see i could choose to change this to whatever field type i want right here in table view so with that notes we'll take a look at that a little bit later and see how it impacts our report in terms of what's going on but what we need to do right now is recognize what filemaker has done for us which if we go into the manage and then go to the layouts area what we're going to see is we're going to see that filemaker has actually created two different layouts for us layout 1 and layout 2. now layout 2 is the conversion of the data and i don't know why filemaker does this but they do filemaker typically assumes that in any solution you at least are going to start with one layout you need something that you're going to see and for the most part this layout is in what they call form view we have three different views we have in filemaker they are right here represented we've got form view we've got list view and then we've got table view so filemaker created a form view for us and we are looking at our data in a table view so we're going to take advantage of this layout otherwise what we would normally do is with this dialog closed we would go into layout mode by switching into layout mode under our view menu and in layout mode we would then have the opportunity under the layouts menu to make a new layout or report well in this case we don't need to do that we're just going to work with the one that filemaker has now going back into browse mode with command or control b what i'm going to do is i'm going to going to do something that usually is advantageous when you are developing anything and you need to see the raw data at the same time that you want to see a report and then as i'm going to head up to the new window and create a new window right here now you're going to see on my copy of filemaker that i do have some keys assigned that are not in your copy of filemaker at least if you're on a mac i control these keys within the system preferences on the macintosh under the keyboard setting and on windows i believe there's a mapping for a lot of different key commands so i have key commands assigned to a variety of things so on this particular view i'm going this one is already on layout too i'm going to switch this one to layout number one i'm also going to go into layout mode and then i'm going to right click there's always multiple ways to do things in filemaker i could go up to the layout and choose the layout setup from the menu right up here but you can also access the same thing directly on the layout by doing a context click and just right clicking so right clicking on the layout i get the exact same setup right there and i'm simply going to name this one report or name it whatever makes sense to your solution so with these two windows now i'm able to use a little snap i know on windows you can do this but on the macintosh i believe i have a um i forget what the um function is but there's a number of these you can search on the macintosh store and be able to put things side by side and over here i'll go into browse mode and into layout mode here well actually am i that one's in browse mode i should have done this one in browse mode that's no problem we'll just do it again all right so we have a lot of data and the way that a report works is it essentially works on sorting but your sorting can actually use as many different values as you want so let's take a look at the sorting as i use command or control s and we'll look at this relative to our data we'll zoom in a little bit here all right so relative to our data we have months notes page views page visits sort and year now quite clearly in this report we are going to want to sort based on the year but if we also want to break it down based on the month then we are going to be able to do that so the great thing about sorting is you can have as many different sorts as you want on a report now the sorts are going to control how the data is displayed and that's going to be through filemaker's parts filemaker has multiple different layout parts when it comes to reporting we're going to see those but first let's figure out how what is the order in which we want to see the data well what makes sense to me is first let's sort it by year so numerically 2003 is obviously going to come before 2013. then within that year i'm going to want to sort based on the month now these two will give me pretty clearly what i want but we need to know that sorting is always relative to the field type so i don't have it on screen right here i'm going to zoom out and then we're going to zoom in all the way over here so that we can see it when we look and consider the month we need to know that the field type of this month is going to be text we also need to be aware that when we imported this data not all data comes in as you expect for example the year that we have here we need to check that field type that filemaker actually made this in fact importing csv i believe filemaker most of the time imports all columns as text so we if we want this to sort correctly we're going to need to convert and make this an actual number field which we'll go ahead and do but when it comes to sorting if we sort base based on the month name that came in that month is going to sort alphabetical because it's text and because a comes before o and s and so forth so we'll have a completely out of order sort in terms of what's going on now in the case of this data what happened is it actually came in where in order to sort they did include a sort field and that sort field its only purpose is to sort things using a numerical value that simply maps to the actual month we can see that january represents number one february number two and march number three and so on so we can take advantage of that sort field if we want to but there's also another way that we can sort within filemaker by giving an explicit list of the the values that we want sorted in an explicit order and we'll take a look at that so for right now what we'll do is we'll go ahead and click sort we can see that we're getting the sort that we want so far but again we need to double check that year so i'm going to bring up the manage database go into that table by double clicking and as i suspected when we imported the data the year did not come in as a number so we are going to take the time and switch that to a number click the change button right there and make sure that it sorts the way that we want this will apply to any import imported data that you want to apply where it needs to be set to a date and all kinds of different things so we know that that one's going to be good if we sort by the sort we will get what we want in terms of the data so let's go ahead and go into our sort again with a command or control s let's take that month off and then let's put our sort on and just make sure that the data is happening in the order that we want so it looks like starting out here what we had is we only started in october but as we scroll down we want to make sure that we get our values and we are not getting the correct values right here why is that because the sort field came in as text this is so important and it's why i'm going through this is when you don't get the results that you want within filemaker it's usually not a problem of the data it's usually a problem up here it is us the operators so as text 1 10 11 12 then 2 is actually sorted in the right way we need to convert this sort into a number so we can right click or click right here go to our field set our field type to a number that's the easiest way to do that and now when we get our sort we can see right there on 2004 we're getting one two three and it's january february march april perfect what is our alternative to sorting well we can use a value list but in filemaker our value lists are somewhat limited but only to the extent that if we know the fixed values that we want then we're going to be able to work with those now here's another trick as i pull this window off to the side we go up to filemakers manage and we go to the value lists where we could take the time to create a new value list which i'll click on new and i will type in month names and here we have custom values so if we simply emulate this we could put in january and so forth and then type feb february and uh february that's right right there february always the difficult one to spell but why do i want to take the time to do that that data is already typed in right here is it possible for me to get that data out i believe it is so we're going to do that really quickly here i'm going to show you how we might be able to do that with this data already in my database i'm just going to go up to the file menu and i'm going to choose i'm actually hiding it the export records option now i have no reason to export the records right now but what we're going to do is we're going to quickly sort this by month so i'm going to sort this i'll clear all of my values and sort by the actual month name and just keep click those sorted so it's going to group them all my april's march may so on and so forth now i'm going to go to the export and export those records and i'm going to call them month names now why am i doing all of this this is just a step that is born out of laziness i could take the time to type things it's really only 12 values but i'm doing this to show you that if the data already exists take the time and you can you can save a lot of time especially when you're working with large sets of data now filemaker's two different text format types are separated and tab separated we could use filemaker's merge but for the most part we're just going to go with tabs separated because all we're exporting is going to be that one value the month name so we're going to save that here and we're going to put in the month and we have this option that says group by so when we click this what we should get as an output an output is hopefully a file that has just those names so we export that and let's go take a look at what we got we go over here bring my finder value on screen let's see if my tabs it will not show me but that's no big deal here i'm showing the extension of my file if you're a developer it always pays dividends to show your extension otherwise you can always right click on windows choose the properties or on the macintosh choose get info but because i'm showing the extensions i can just change this to csv just so i can take a look at the the file using the quick view and lo and behold there i have all of the values the singular values because i choose that group by and i can simply now just copy these so i can put these into a let me open this with a program called text edit here just so we can get those values out there they are i'm lazy i don't want to you know copy them filemaker of course does not have them in a sorted order i might as well show you how i would deal with this there is a program that i use actually many text editors will actually allow you to sort i'll bring this window on screen this is just a little program called vim where i can put these in i will actually type them in and paste them because this program allows me to sort this data this is a little bit of a tangent that i'm going on here but i'm going to you can see right here what i'm doing is in vim i can pass a command to this program to do what i want and many other programs will do this actually do i need to select all of that then pass it in i believe i do uh let's do oh it is sorting it it's sorting it based on the values of a b c d e f g which is actually not what i want because i want them in the right order so tangent to side will close this sometimes you do and again using an external program will save you the time of sorting here in filemaker we're going to go back to that value list and we're going to be able to sort based on that we'll go back into the month names i will select everything here and i'm just going to need to manually drag those around which i will do all right with all of those sorted we can see that this being such a small list again i could have just typed these in but i wanted to show you that because there are times where you're working with very large data sets that maybe have you know thousands of records and you just want to get those values out i'm showing you how to use filemaker to get the data out so that you don't actually have to type those values you can use the export just on one single field and as long as it's sorted you can then group by that and get all those singular values out so that's why i showed you again this case i would simply have just typed them but now when we go into the sort if we did not have this sort value provided by the data we would be able to sort by the month using that actual value list heading into the sort and going back to our original situation where we needed to sort first by the year and then we wanted to sort it by the month when we select the month what we do get is we get this additional option right here of custom order based on value list so you can see by checking this that value list which depending on the order i put the values in which i did put them in correct month order that will now control the data in the same way that the sort value did now sometimes you don't think about putting the sort value in but you can see right here we're getting the exact same result we have the year and then 2005 and so forth here we have january and then we have 1 through 10 or 1 through 12 for all the months in this particular case it is sorting based on the text values using the value list or alternatively we can use a literal sort value many times in your database systems you do need to add that sort value so now that we have the data in the proper format we can actually focus on the report itself now in this case this form view which started out as a form we are going to need to switch to a list view that's because we're going to be looking at the data as a list in order to print the form many times you do need a form view but in this situation we're going to be using sub summary parts in order to get summary values and adding some summary fields so that we get the type of report output that we want all right so let's generate our report we have our data in the right order and i'm going to break this off and bring take this window out to the side so that such that when we go into layout view what happens with filemaker is it now draws these uh sidebars on the sides over here and so having it anchored to the side of the window doesn't benefit us too much so if you're going to be doing a report and what i'm going to do is i'm going to get rid of all of these fields start with a blank slate i'm working in a copy of filemaker 19 here you of course are going to need to set the report to whatever dimensions you need to work in now there's a couple of ways that you can change the dimensions in filemaker we can see over here when we go to let's zoom in right here when we go to this area in what's called the inspector we have this position this position tab with the position sub area now the measurement system that's used right here is currently set to points and it defaulted to the point system on this new um file that i created but we want to use whatever is respective of our current locale so we click here and we can see that it switches to inches for the us or if you're in europe or a country that supports metric then we have centimeters so there's only those three we have inches points and centimeters and that's how you're going to change this so in my case i would be able to set my report to a width that is exactly what i want so 8.5 and it's going to resize that now there's one other way because i like to always show you all the different ways in filemaker to do things or you know as many as possible under the view menu we have an option to view rulers and when we turn those on it's really helpful when it comes to reporting especially in terms of lining things up now this is the second area right here where you can click to change the measurement system so i can click here and it goes to centimeters now it goes to points also pixels relatively and now we have inches so with this here we can see that we now have our measurement system and we can put things exactly where we want on the report the next thing we need to be aware of when it comes to reporting are the parts filemaker's parts it used to start out that filemaker by default would have a header at the top and then the very next part would be a body now the parts themselves are all relative to what you're going to be doing on screen and where that screen is going to be viewed in the case of a mobile application the top navigation is a part that in particular does not scale when you pinch and zoom on a mobile application and that's why filemaker probably changed it from the header part we can always double click any given part and it will bring up filemaker's part definition dialog box this is where we can see all of the individual parts so let's talk about what these parts do top navigation usually used for mobile a title header that is a part that will only show on the very first page of a printed report a header that will actually show at the top of every page of a printed report a leading grand summary when using summary fields will start at the beginning of any report that is broken down on individual sub-summary parts which we're going to see here the body a critical part that must be in any filemaker layout if you're going to be showing data the body is the one part that absolutely must be there sub summary when sorted by this is the magic filemaker part that allows you to print any type of report that breaks down data according to groups and when we select this when we add this part we'll see that you can select from any of the pieces of data that you have now the key to this one part is that you get a total of two for any given value so in other words if i want to show a sub summary or the month i can only do it twice once above and once below we will see for any given field our trailing grand summary will show after all summary parts our footer will be on the bottom of every given page and a title a footer will be at the very last page of a printed report and finally bottom navigation will be on every layout and is typically used for mobile so in this case we're going to switch our top part to a header here because that's going to be something that i want on every page if i wanted the title header then i could go ahead and create one of those but in this case we're just going to create a super simple report now down at the bottom as we can see we've got all of these different options now all of these different options are pretty self-explanatory based on the parts that you're working with for right now we don't want any of those options we're just going to say this is our header and i am going to go over here and make this a white part just because we're dealing with a report now the body is going to be our data of course and we are going to need that data into this so we're going to switch over to our fields and we're going to drag in the fields that we want sort is not something we're interested in month year and then our page views and page visits and i'll even put in my notes when we drag these out of course down at the bottom of the layout we have the ability to have the field placement because i'm going horizontal i do not want the option of them vertical i will place them horizontal and i do not want any labels on these currently so i will select this option so that i don't get any labels and all of these can stay just standard edit box in fact we don't even have to put the fields themselves on the layout that is the report because users typically are not interacting with a report and we'll see what the option is to do that but for the most part we'll just get the fields on the layout now as you can see the fields that are coming in they are not in a very report friendly format so we typically want to create a style in the styling area or in the design area that we have here and style these a little bit more simply i'm going to take off their corner radius turn off their bounding boxes and i'm just going to name them as a new value right here and i'll of course need to select any one of those i had five of them selected before i'm going to save my style and i'm going to save my style as a new style and that comes up off screen and i'm just going to call this report fields and we'll say ok and so now i'm going to be able to modify those and of course here it's red and i've got the little star so i'm going to go ahead and save the changes to this theme which actually doesn't change that save that to the original theme it created a copy and kept the same name we can always see that when we click on our styles by clicking right here and when it comes up we can see that we have an apex blue but then we also have an apex blue that's named as custom so filemaker did make a copy of the theme and it just kept the same name so a little bit confusing there but that's what we need to know so with that what i'm going to do is we'll select all of these fields we'll go ahead and make sure that we use our report fields option and get all of those converted so we're going to put these on our report we'll put the body and the year or the month and the year first we'll put the year then we'll put our month and then we'll put our page views and then we will put our page visits which one of the years does not have the data we'll go into browse mode and we'll see that we've got all of our data we can tighten that up always by shrinking the layout part we can also make these a little bit smaller so let's go ahead and do that we will select we will make these let's say 11 point which are going to be a lot smaller i can always choose to save my style when i do that i of course need to have one selected which i'll do save changes to current save changes to theme as we're updating it's always a good thing to do and i will shrink this up as small as i want to have this i'll use the arrow keys in order to move those up and then i will drag on the body part and get things so that it's really tight checking that this looks how i want it to look when i'm in browse mode so now we've got the data pretty tight there looking like we want to but we want to add or we want to break this down into all of its groups in order to do that we are going to add in sub summary parts now there's multiple ways to add a part we can go up to the area up here in the layouts menu i prefer to use either the toolbar where we have the part tool where we can drag out a part or right click and simply just do a part setup now this is the same menu option that we had in the menu command that we just saw the reason that i like this one is filemaker does not automatically shift things when you're creating a report and you use this tool you'll notice that you can drag in anywhere even between these fields and filemaker will allow that part to drop and it will literally chop your fields let's go ahead and make a sub summary right here let's sort it by month let's say okay and you can see what we got was we got a really funky thing going on there where it didn't exactly do what we wanted to do so let's go ahead and undo that let's choose the option where we choose the part setup and in this case we get a different dialog where we can see that the header is locked we have our body and we're going to create a new part so that part that we're going to create is going to be a sub-summary now the first sub-summary that we're going to use is the one based on the first sort that we're doing here's a key thing about reports if you create sub summaries that include data let's say like a date or a value that does not mean that you can't sort in a completely different order you can you can sort all of the data completely separate of how the report is going to render in this case it just so happens that i'm going to sort based on the year first and by double clicking we can see that we get that situation where i talked about you have two options you have a an above and a below for any given field so i can print above or print below in this case i want to choose above because i want the year to be above the actual body so i'll click above and we can see that that part gets added in here and it's adding in according to a preset height and you don't control it and it just adds it in automatically which shifts your fields for you so you don't have to play with guessing by dragging in that layout part we'll go ahead and click done for right now and we will simply drag this to make it a little bit bigger and we will put the year up here at top i did that with the option key by making a copy i do not want this option of create a label i'll turn that off and i'll say okay i will also go over to my styling and take the opportunity to make it bold so this is just a way to make things stand out but when i go into browse mode and when we sort this data which i just brought up my sort and we sort by year and then we sort by month by adding that but selecting my month and saying i want a custom based on the month names and sort that what we're going to have is we're going to have our first level of grouping and that's our grouping where we get everything in the year order so i've got 2003 2004 and so on the next thing that i might want if i had data that was date based would be based on the actual month but in this case the data is pretty much going to satisfy my needs in court in terms of having these broken up based on the year alone so the next thing that i need in this case is i need some totals i need some aggregate functions and these are the summary values that we're going to add into our database no matter what it is that we are going to summarize all right so now i need to gather or i need to show some data based on my the data that i have as i scroll down there's one of these that does not have i think for there we go 2014 did not have the values for page visits so we will use the page views so that we can actually have really consistent data so the page views is a field that we need an aggregate value on and all of filemaker summary functions as i go into the manage database are are they are our aggregate functions so we are going to put in a total and one of the things that i like to do is i actually like to precede the names of my fields with whatever the respective of for example if i have an unstored calculation i will many times prefix it with the value of unstored and it just turns out that i typically use an a lower camel case approach but for right now we will put in a upper case so preceding the field with the word summary very clearly spells out what type of field this is so i'm going to say summary total page views i think that's what i'm actually building the summary based on i will choose the summary field type and now when i create this i'm going to get all of filemaker's aggregate options they they are everything right here which makes sense a total of an average of a count a minimum a maximum standard deviation fraction total of and then list of now depending on the function that you select you can see that we are getting different options in this area right here so if we pick a total of and we see that we do not have data that will give us what we want page views that is because we cannot total based on a text field so clearly we need to go set these into the right format so we're gonna have to back out of this this is why we have our fee our raw data over as the developer here we can go to our page views simply just choose on the field change the field typed number let's go ahead and take the opportunity to do it for our page visits as well and set those and over here we go back to our field and we're going to see that when we go into our summary well i have to type that again summary total page views and we are going to select the summary type of a summary and we are going to create that right there and we can see that quite clearly with the total selected now we do have the option for our page views in this case we do not need running totals and we do not need a restart those options do make sense when you get to them and also summarizing individually these are just enhancements to what you need to do when the report's not coming out how you want that's when you know you're going to use those options so i won't turn them on for right now and with this summary field we can now add it to our particular layout so we can add the field into the sub summary anywhere that we want but remember that option of having two sub-summary parts is where it pays off for us because when we look at this we want to sub-summarize this report with the year up at the top but then we typically look at a summary of numerical values at the bottom so this means i need to go to my part tool again part setup and i'm going to create that part again and i'm going to it's going to be a sub summary again based on the year and this time we're going to say print below so we can see that we've got the two parts now we've got the leading and the trailing just for grins we're going to try to create a sub summary again and we're going to try to do it based on year and we will say print below and there is our result right there this shows us we can only have two of any given field and it's because we get one above and one below so i'll go ahead and click cancel that but we now have one above and one below so it is this one in below as i grab the part make it a little bit bigger where i want to put the total of the page views now i'm just going to cheat right here by taking an option drag of that field and rather than leaving the default field that filemaker has i'll choose this one filemaker does not have this the label turned on and i'll click ok and for this one i will also make it bold i'll just do that with a keyboard shortcut which is command shift b on the macintosh here so now when we go into browse mode our data is still sorted we can see right there always looking up at the top but what we have is we have a little bit of a disconnect here in terms of how our report is looking and that's because our sub summary parts actually are the same color so we can see that when they merge here together we don't get a clear distinction that's just going to be a visual thing that we need to change in order to fix things up we'll go to our sub summary part go over to our styling set that to white set the other sub summary to white and now we could just put in some things that sort of help us out we can drag in some lines if we want to i can drag a line right here all the way over and then i can do the same thing for the part right here if i want to have that be at the bottom so we'll get the line tool and we'll just draw a line right to the edge right there and we'll select both of those lines and we'll make sure and make them really dark here make them black lines now we'll go into browse mode and see if we're getting a result of what we want and there we can see that for the most part for 2003 we've got this number which is a summary of all of these values so you can see that the report is starting to end up exactly how we want now remember that notes field we'll take a look at that but i also want to point out that with a sub summary report there are times where you don't want to see the actual data in this case these numbers maybe they're important to me if i want to see things on a monthly basis and if i did want to create a monthly report where i saw month to month and i was going to maybe create an associated graph i would be able to create a separate report which would be a month view where this report is going to be a dedicated annual or by year view you must know that in filemaker when it comes to reporting the body where previously i said you almost always want a body report you don't have to have one so i can delete this body part so in this particular case what i'm going to do is rather than getting rid of the body part right here where i might want this i'm going to go up to the file manage go to my layouts i'm going to take my report layout and i'm just going to duplicate it i don't have the duplicate option with a right click so i'll click the button that we have down here and duplicate that i could open that but in this case i'm just going to switch to it using the menus right here we have the exact same report when i go into browse mode i should be getting the exact same thing there's my two values right there and i will go into layout mode and we will get rid of the body by selecting it and hitting the delete it'll ask us if we want to delete them we delete it all and now what we get is we're going to get a condensed sub summary report we can see right here for 2003 we've got that value for 2004 and in this case the lines wouldn't make sense so i go back into layout mode i will select my two lines there and there get rid of them i could just use the one sub summary report but i'm going to show you how we're going to put in those note values and look at the last remaining pieces to the puzzle to make a report work the way that we want when it comes to text that can be in a long field so we'll take a look at that but for right now let's take a look at what we get as a result i'll actually undo put one of those lines back because i want a given line to separate each year i'm going to do that after the actual total value right there so that when i go into browse mode each year and its value is actually represented and let's go ahead and take the time to clean up the number if you don't know about it in the fourth area in the inspector that is where we are going to select and i'll collapse everything here until we get to the data formatting so whatever data you want to have in a particular format that's where you're going to find this with this field selected on the number setting as opposed to the date the time and the graphic or container i'm going to choose to format my data as a decimal value and i'm going to say i'm going to have a fixed number of values i'm going to use the thousands separator so that the data comes out looking nice in this case decimals don't really value matter because we've got a whole number i could go red for negative and we have all kinds of options but in this case i should get a good output of what i'm looking for i've got 38 million actually the decimal is showing right there i need to go over select that field and choose this option do not display so if you do have decimal values you can still leave the decimals on but if there are no decimals you can simply turn that off go back into browse mode and why is it showing those decimal values still there i don't know why it is do not display if number is zero ah okay we'll just turn that off right there because that was uh that's it if the number is zero i was a little confused right there forget the decimals we won't use them we can always get the result of what we're looking for in terms of the data so let's take a look at the final part of reports and if you have this applies to if you have a form based view report something where you're including portals and other data especially if you have long text data you need to be aware of these few features all right so we're almost finished with our report and it comes down to our notes field so i'm going to go into browse mode and because i can actually enter into the fields here on my form i'm going to paste in my data which is just some lorem ipsum text now obviously this text is being cut off and let's put everything in the order or the place where i would want it to be i'll put this all the way at the edge of my report where it would probably be pull my line out to where that would be and put my notes out as far as i can so that we're in the full width of the port report even still my data is not actually being shown so i need this data to show let's say i had notes on each of the different years just talking about information of varying lengths right here let's say that that one does not and then this one let's go all the way to even the first sentence where it is still getting cut off this is where you need to take advantage of filemaker's report features that deal with sliding this comes into play more on your forms than it does on your list view reports but regardless we're on listview report here and we want to get all of the notes but we want all of the data to properly show well we go into layout mode and what we have to do in this particular case is our sub summary needs to be as large or as big as it needs to be for our accommodating text so let me show you a very good trick in filemaker where in layout mode i can make this actually as long as i want i could make this extend super super far the height of a layout in filemaker can be extremely long but if i know that i have an average of a maximum amount of data i'm going to show you a trick that in layout mode makes it a little bit easier we'll go into browse mode here and i would have to scroll through all of my records in order to find obviously the largest field i could use also use a filemaker calculation in order to find the length using the length function of this particular field and put that into another field and then find the longest one but knowing that this is the longest record as opposed to let's say this record if i was selected on this record and i'm in layout mode and i go up to the view menu we have an option under the show menu of sample data this is really helpful when you're creating reports or you're creating layouts where you need to size your fields accordingly so by turning on the size sample data we can actually see the data that's coming from the record that we were on when we left browse mode so what we're going to do is go into browse mode switch to my record that has the larger amount of data and by being on this record when we go into layout mode we can see all of that data with that view option turned on so now i can actually resize both this layout part and this sub summary to an approximate value now again if in the future this report might have even more text then what i'm going to do is i'm going to make this even longer or as long as i would anticipate for the report because now i'm going to use the option that allows me to make sure that the report is going to collapse down and it's going to consume all of this white space that we have between the end of the text and the next part that's all going to be found in the position tab in the inspector and as i collapse things down here to the auto sizing and the arrange you can see that we have this option right here sliding and visibility the sliding invisibility controls what happens to fields and portals when you want to shrink space or if you need to shift things to the left so we can see that we have the sliding left in this particular case i do not have any situation where i need to slide this notes value to the left so i'm going to choose the sliding up based on and then we have the options of all objects above only objects directly above and we have the also resize and closing part this is the option that i need to check right here that's going to say for this sub summary part i want to resize all of this and shrink everything up now it's not going to be something that we see with this selected we can see that we're applying these options with this item selected but when i go into browse mode it's not going to be something that i'm going to see automatically this is only going to appear when we are in preview mode looking at this report and you can see that by going into preview mode that's exactly what happened each of these parts were shrunk where the enclosing part pushed up and now i get every the exact report that i'm looking for let's go into layout mode and just add that one final touch we'll call this something to the ex to the effect of total page views by year and we will make this a little bit bigger i'll do that with the keyboard commands and make it bold and we will put this right over here and this is going to give us when we go into preview mode which i did with commander control u and it looks like i do need to get my uh text off here i'm not accounting for margins a little bit but there we go everything should be exactly as i want in 2003 this is the result and we can compare this year versus this year yeah one final thing i've got to do it i've got to make it the nicest it can be and that is right align my number right there and we'll align it with the text but there we go we get the type of report that we want and we've covered all of the things that we need to know about generating reports within filemaker sorting the order matters using a value list if we need to control an explicit order otherwise using a sort field that puts things in the proper order that we want then we've got the next part which is our layout parts which controls what's shown we get sub summary any two any given field can have a maximum of two sub summary parts and then finally any text that we have we have sliding and visibility controls where we get to slide left or slide up based on either just directly above or all items above and we also get to enclose the part so that our report comes out exactly how we want so as always i'd like to wish you much luck with your own filemaker development and until next time much luck and happy filemaking we hope you've enjoyed this video tutorial and we'd like to say thank you for your subscription and your support if you're not already a subscriber head on over to www.filemakermagazine.com subscribe for more information about the benefits of joining