Know how to produce a summary report with a monthly total for each month?
Your bar chart will have similarities to such a report. You'll need a summary field, I'll call it sSalesTotal, that computes the total of your Price field. You'll need to be able to sort your records so that they are grouped by month. Here's a method I recommend for that:
Define cMonth as Delivery Date - Day ( DeliveryDate ) + 1 and specify "date" as the return type. This computes the date of the first day of the month for every record.
Now set up your chart to graph the sTotalSales Field and use either cMonth or Delivery Date in a calculation to label your X-axis. Make sure that you specify these options:
Use Data From Current Found Set
Show Data Points for Groups of Records when sorted.
Then you do a find for the records you want to chart and sort them by cMonth in order to group them by month, sorted in chronological order.
Thanks for your response above. As I'm very novice to customization, are you able to simplify the steps outlined above? I understand in theory what you've explained, but I don't know how to recreate what you've explained.
Is this possible please?
Which part do you need help with? Defining cMonth or in setting up the chart? Let me know exactly where you first get lost and I'll break it down into more specific steps from that point.
I don't suppose I can send you my file to work on can I?
If so, please advise how to do this.
If not, Assume I don't know anything other than how to set up the table which contains the data.
Your note above assumes I know how to set up a summary report, which I don't.
Are you able to outline it step by step? Also, to ensure we're clear on my desired outcome report, please see the graph on this link http://www.realtown.com/LiveInLosGatos/blog/los-gatos-homes/blossom-hill-manor-real-estate-market where it says 2 - Average sales price per month for this east Los Gatos neighborhood:
The chart on that web page shows what I understood you to need.
Open Manage | Database | Fields and select the table in which you set up these fields in the drop down if it is not already selected.
Create the field cMonth, select Calculation as its field type and enter the calculation that I posted earlier:
Delivery Date - Day ( DeliveryDate ) + 1
In the bottom left corner of the Specify Calculation dialog where you enter this expression is a drop down. Select "Date" from it. Now click OK.
This calculation should return the date of the first day of the same month as the date in DeliveryDate.
Get this working and report back when you do. (Or what problems you encountered if you cannot get it to work.)
This is great. So far so good. I appreciate the step by step and simple nature of the instructions. Ready for next step.
Add sTotalSales to the same table.
Select "summary" as the field type. Select the "total of" option and click the listed field in this table where you record individual sales.
Now add a new Chart Object to your layout. (The layout must specify the same table as where we have defined these two new fields.)
In the Chart Setup Dialog box that appears, select Bar Chart as the chart type.
Enter what you want for the chart title.
For the X-Axis, click the button with the black triangle on it to the right and select "specify calculation".
Enter this calculation:
Left ( MonthName ( YourTable::DeliveryDate ) ; 3 ) & " " & Year ( YourTable::DeliveryDate )
Since I don't know the name of the table, I've used YourTable. Put your name in place of this text. (Better, just add the fields from the list of fields shown in the list at top by double clicking them.) This produces the three letter month name abbreviation followed by a space and then the year to use to label each bar in your chart.
For the Y-Axis, click the button to the right and select "specify field". Select sTotalSales.
At the bottom of the dialog box, select these two options:
- Use Data From Current Found Set
- Show Data Points for Groups of Records when sorted.
Click OK to dismiss this dialog.
After making any needed adjustments to your chart object's size and location, return to browse mode.
Perform a find for the records you want to include in this chart. (This could be a specific range of sales dates such as those for the year 2011.)
Then Sort your records by selecting sort from the Records menu. Specify cMonth as the sort field. This step is need to group your records by month and in the expected "calendar" order of January through February.
how do i add a new chart object and to what layout?
is there a messenger service so we can chat in real time, otherwise, this could take all day?
You have nearly everything you need to do this.
Select the layotu where you want this chart. It should specify the same table as that where the fields we have added were defined. You can use new layout from the layouts menu to create a brand new layout and specify this same table if you want. (Layout Setup... specifies the table for a given layout in the Show Records From drop down.
To add a chart object, enter layout mode, click the chart tool at the top of the screen in the status area and then draw a rectangle on your layout (in the body layout part). This will pop up the chart setup dialog I described in my last post.
Thank you. I think I have it working now, however is it possible to create a static report such that you just press a button and that report shows up? As opposed to having to manually find then sort each time?
Also, for the bar graph, is there a way to show the dollar value, in this case, sales total at the top of the bar? For example, if in September of 2011 sales were $14,596, could you have the value of $14,596 appear at the top of the bar for the month of September?
Finds and sorts can be scripted so that you click a button and the script does the find and sort for you as well as changing to the report layout.
Charts in FileMaker are pretty limited. We didn't have any charting capability built in until the latest version was released. You cannot add the dollar total to the top of the bars, but a tool tip with the total (not formatted as currency) should appear when you hover the mouse over the bar. (Not what you wanted, but that's all FileMaker can offer you here.)
The alternative is to use a third party tool for for generating the chart. There are plug ins. I think there's s google widgit that will allow you to use a webviewer to chart your data and you can export the data to an excel spreadsheet so that you can use its charting tools (don't know if it offers this feature or not).
Thanks for all your help.
Hey Thanks for the breakdown, PhilModJunk,
This really helped me finally get something useful in a chart.
Now I've got to figure out how to do this as a dashboard item with preselected or selectable year or year range...