Lesson 13 - Writing Sales statistics reports
It is a sure bet that if you create a commercial system, somebody at some time is going to want to see statistics. Actually, it is probably the reason you created the system in the first place.
Of course, sales reports will be printed so, we are still talking about mastering the report format.
In most cases sales reports cover 2 broad categories: Customer sales and Product sales (Who is buying and What they are buying).
Within those categories you will have a whole bunch of sub-categories, usually based on dates: daily sales, weekly sales, monthly sales, quarterly sales, yearly sales, by customer, by product, by salesman, by category, by region, etc, etc, etc.
Where is the data?
All the sales reports have 2 things in common: Transaction Master table and Transaction Details table.
All the information for the reports starts with those 2 tables: who bought what, when, how much was paid, who sold it.
Of course, in Master, "Who" is a customer number and you will want to get the customer's name and address from the Customers' table and "What" is a product number which you will look up in the Products' table.
So, the first thing we will do is to create a general query to handle most of the sales reports which we may be asked to produce.
Don't forget that you don't have to use all the fields in the query for every report - you can pick and choose whatever suits the particular need.
Not that I have added a few columns, in case I need them later.
I have concatenated the customer's first and last names and I've calculated the customer's age (in case they ask me whether our customers are teens or seniors). I could also concatenate the employee's name, and so on. (In our examples the employee is the sales person. We might have to calculate sales by employee to pay commissions.)
The first report we will produce is the Customer Sales report. This will show how much each customer purchased. For the moment we have not specified a time period so, it will list all sales.
We start with the Report wizard as in the previous lesson.
We select the query or table for the report. In this case, the MainSalesqry query.
Then we select the fields for the report. It doesn't really matter if we select too many fields - we can easily remove them from the report.
This kind of report is based on grouping.
To obtain a total for each customer, all sales must be grouped for each customer.
Grouping is usually done on one field at a time, at least at the beginning.
If I wanted to see sales by product, I would group on Movies.
Grouping on Employee would give me the total sales by employee, and so on.
I then skip over the next few questions from the wizard: no other groups, no sort fields.
I select the Corporate format and I assign the name: CustomerSales to my report.
The first result is not very exciting, to say the least. Each customer's data is spread over 4 pages!
Not to worry! We'll fix all that with editing.
After moving titles about, deleting titles and fields that are not needed, re-aligning the detail section, the result is more promising.
Note that you have to get everything within the 6½ inch margin on the right to fit on one page.
Change font sizes, colors, type to improve appearance.
Although we have all the details, we have no totals for each customer.
When we look at the grouping and sorting criteria, we see that the main group is on c_lname.
Customer_last_name is not an appropriate grouping level: several customers may have the same last name.
We will have to add a grouping level on Customer number instead.
Then we clean-up our grouping somewhat. Grouping on transaction number is useless, so we remove it.
We leave customer_ last_name for the sort order because the report will be more meaningful that way.
And finally, we add totals. We'll need a group total (total amount of sales to each customer) and a report total for all customers.
Not that whenever you create a group you have the option of specifying whether a header and footer sections are required (yes or no for each).
In our case we will specify yes for each so that we have a header where the customer's name and address will be printed (only once for each different customer) and a footer where the total of all the details will print.
To get the total we simply create a textbox and use the sum( ) function as control source, as shown.
We do the same in the report footer to get the report total.
What we've done so far is to produce a report of sales by customer.
But at no time did we specify a timeframe. So, if the customer has been renting movies from us for the past 5 years, we will get a complete list of all his transactions for the past 5 years evry time we print the report. That is very, very rarely useful.
What we really want to do is specify that we want the sales for last week, last month or current year-to-date.
That's not hard to do.
All we need is the period. So, in the query, we put in criteria for the transaction date, using parameters.
When the report is printed the query is automatically invoked. The user will input a From date: and a To date: and only transactions done between those two dates will be selected for the report.
That is the easiest way of doing it.
If you go back to the lesson on manipulating dates you will see that you could construct the query any way you like: the last 30 days, the current month, the previous month, current year to date, etc.
No problem even if you want to have different queries for different reports. You can Copy and Paste MainSalesQry as MainSalesQry2, MainSalesQry3 and change the criteria in each of them.
And finally! you don't even have to print al the details all the time.
Actually, most of the time what is required is a summary of activity.
To print a summary report you go through all the steps indicated above and then you delete the Detail section.
All the calculations will still be made, except that the details, the individual transactions, won't appear.
Once you've mastered the technique to create a Customer report, you can produce an Item Sales report, an Employee Sales report, a Region Sales report, etc.
Remember: it's all based on Grouping and Sorting.