I’m supporting a large solution that combines CRM with Orders, Stock and Invoice for a small company. Now they want to utilize the data to get a better view of how business is going. That means statistics and graphs. Reports I’ve been doing for long (though some technics seem to elude me), but graphs are still rather new to me.
I can do reports that show how much each manufacturer has sold (or each product), but I can’t seem to sort them in order “Who sold the most”, that is, on the statistics field itself. I have to export to Excel to do that.
I'm also trying to do a line graph that shows how much each manufacturer has sold each month, by view one squiggly line per manufacturer. (The X-axis is Months and the Y-axis is sold per month.) Right now I can only get one squiggly line representing total sold per month.
All this is based on data that exist in the “Invoice rows table”. The attached picture illustrates a simplified ERD of how the invoice, customers, products and manufactures connect. I have also attached a FileMaker file that contains a “fake” “Invoice rows table”. The first layout contains a table view of the table. The data has been deliberately anonymized (and fields removed), but the data is real. When the data is sorted correctly, layout two shows an unformatted draft for a report, illustrating sales per manufacturer per month. The third layout holds the graph I made so far.
• How do I, in the report, sort the manufacturers in order of which sold the most?
• Can I make a report that only shows the five most selling manufacturers?
• How can I make the graph, dynamically, show separate lines of how much each manufacturer has sold for, per month?
More questions will come later.
With best regards Magnus Fransson.