What Chart Style will you use? Line Chart?, Bar chart? or ???
A line chart, is it possible?
It's possible. The details for setting up a line chart for this vs. setting up a bar chart differ. Bar charts are simpler, BTW.
A line chart needs to use a different field/data series for each line in order to chart multiple lines on the same chart and that complicates this process quite a bit.
Are you using FileMaker 11 or 12? ExecuteSQL might make it much easier to produce the needed data series' but it's a new feature in FileMaker 12 so I don't want to go there unless you are using that version.
We are using FM 12!! So what is the first step? :)
First step is to set up a table for your report and then use ExecuteSQL to generate the needed data series for your four specified years. With older versions of FileMaker, the only way to make this work that I can think of is to devise an extra table with some date and global field data to sit between your charting table and the data as a kind of "aggregator table". But with ExecuteSQL, we should be able to dispense with that added complication.
Create a new table and add a field for specifying a year. I'm assuming that you want a range of years in sequence so if you specify 2009, you could chart data for 2009, 2010, 2011 and 2012 in four separate data series for your line chart. Eventually, you'll want fields for the other chart options as well so you can add those fields while you are at it if you want.
I'm going to bookmark this thread and return back with a demo copy. ExecuteSQL still has that "new feature smell" for me so I want to play with it to make sure I get all the details correct before I tell you how to do it. Once I have a working demo, I'll report back here with a description of the details and a downloadable demo file.
And one question about charting the unit price: Since you'll have many transactions for a given product for a given month, do you want to chart the average unit price perhaps? I'm assuming that you want total sales for when you chart a sales figure.
Ok, this was fun....
I highly recommend the use of SQL Explorer by SeedCode. I ended up using it in conjunction with the documentation on select statements in the fm12_odbc_jdbc_guide_en.pdf that you can find in FileMaker's help file to build the SQL used in this demo file.
You can download a free copy of this tool from: http://www.seedcode.com/downloads
In this demo file, I've assumed that you would need the tables typical of most invoicing systems:
I added a 5th table, Charts and did not link it to any of the other tables in relationships as ExecuteSQL will define any needed relationships "on the fly" in order to produce a return delimitted list of values to chart in the Line Chart.
I defined 3 fields for this demo in Charts: year1 (number), cYear1Series and cYear2Series--both use the same SQL expression but cYear2Series uses Year1-1 as a parameter in the ExecuteSQL function where the first field uses just Year1. I could easily generate two more data series by duplicating one of these fields and then editing the paramter to by Year1-2 and Year1 - 3 repectively.
I didn't create those fields as I didn't have time to generate sufficient test data for them to show.
This isn't the complete solution, but it's a start for you to take a look at and try to understand how it works. To handle all the options you've requested, You can create more sophisticated SQL expressions that include more joins to other tables and more criteria in the WHERE clause to limit the data in each series. You could also put several ExecuteSQL function calls inside a case function so that the particular SQL used can be user selectable.
Here's the demo file for a look see: https://dl.dropbox.com/u/78737945/4YearCompChartDemo.fmp12
Note: one important limitation of this demo file is that there must be at least one sales invoice with at least one lineitem record for each month of the year. If not, the sales totals may not correctly align for the correct month. For most businesses, this should not be a problem as they would be out of business if they don't have any sales for a given month.
Amazing! You are a genius! It works perfectly :)
Thank you very much!
I am currently deploying what we have discussed and I am blocked getting the "product line" in the sql query. What would be the sql way of asking: give me the sales per years, grouped by months of the product family "bathroom" - which is a value from "product line" field from "product" table. Do we have to put that table in SELECT even though we don't want its value but just using it as a condition?
You started with:
ExecuteSQL ( "SELECT SUM ( b.sales ) FROM \"Invoices\" aINNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"WHERE a.cYear = ?GROUP By a.cMonthORDER BY a.cMonth" ; Char ( 9 ) ; ¶ ; Year1 - 1 )
And I am trying out:
ExecuteSQL ( "SELECT SUM ( b.Sales) FROM \"Invoices\" aINNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"_pkItemID\"WHERE a.cYear = ? AND c.productline = ?GROUP BY a.cMonthORDER BY a.cMonth"; Char (9) ; ¶ ; Year1 - 1 ; "Bathroom" )
What results do you get when you inspect the values in this field? It looks correct to me, but maybe you are getting the dreaded and frustrating ? that tells you something isn't quite right but with no clue as to exactly what isn't correct?
Correct! I have tried the "data viewer" to see if it could help out, but all I can get is a "?" as a result... even in the SQLExplorer I can't get it work! Is there something to do with " ? Is there a way to point out what is wrong?
That ? response is the one aspect of ExecuteSQL I hate the most. All you can tell from it is that "something is wrong" and there is not the slightest indication as to what, exactly is going wrong in the expression.
I opened the demo file, duplicated the calculatioin field and then successfully edited it as follows:
ExecuteSQL ( "
Sum ( b.cost )
FROM \"Invoices\" a
INNER JOIN \"LineItems\" b ON a.\"__pkInvoiceID\" = b.\"_fkInvoiceID\"
INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"__pkProductID\"
WHERE a.cYear = ? And c.ProductName = ?
GROUP By a.cMonth
ORDER BY a.cMonth
" ; Char ( 9 ) ; ¶ ; Year1 ; "Wrench" )
I then went back and carefully compared to yoru example and found a one letter typo in this line:
INNER JOIN \"Products\" c ON b.\"_fkItemID\" = c.\"_pkItemID\"
Unless you renamed a field, the field name is missing an underscore character as I routinely preface primary key fields with two underscores so that they sort to be ahead of foreign key fields with only one underscore.
Rigth on it! Thank you very much, it helped me big time setting my dashboard with charts!
Without over-asking, I am facing antoher challenge: the dashboard that uses exlusively "ExecuteSQL" - as you taugth me :) - takes 12 minutes (!!!) to load at every changes I make in the radio buttons section that I use to filter the results (Case functions integrated in the charting fields to personalized the search). I tried indexing the main fields that are being searched for but it did not change anything. Is it suppose to be that slow? Can I make it faster?
If not, can I add some function somewhere to launch the search on a button clic after that the radio button have been chosen?
Thank you again
ExecuteSQL is new to all of us so I don't have any definitive answer on whether or not that result is typical for what you have set up. Much depends on the number of records in your tables and whether or not all key fields are indexed or not.
If your SQL is using aggregate functions such as SUM or Average, it may be possible to use a new table that "flattens" the data into fewer tables and records--thus reducing the amount of calculations and iterations needed to evaluate your queries.