Displaying Sub-Summary Headers in Reports on the Web with FileMaker and Lasso.
Here are some examples of why you might want to use a sub-summary report:
- You have a database with sales by salesperson; each salesperson works in a different region and you want one report that let’s you see the total sales by salesperson, as well as all the sales by region.
- You have a database of contacts and you want a report showing the number of contacts by city and state.
- You have a registration database and you’ve been asked to produce a report that showed how many males and how many females participated in each event.
- You want to re-create on the web the FileMaker ability to create sub-summaries that allow several records to be displayed under one header.
- Take a look at the Demo database, “Sales Report” layout
- Build a lasso page using the [Map] tag
- Demo.fp7 – the FileMaker database
- SubSummary_SalesRep.lasso – subsummary by salesrep
- SubSummary_Region_SalesRep.lasso – subsummary by region and salesrep
How it all works
// here are key sections of the code
// the inline (database call) is done only once, right here
var: 'salesrep' = '' ,
'salesrep_sales' = (map),
'sales' = 0 ;
// reset the salesrep and sales amount when the salesrep changes
if: $salesrep != (field: 'salesrep');
$salesrep = (field:'salesrep') ;
var: 'sales' = 0 ;
$sales = (math_add: $sales, (field:'sale'));
// sum the sales by salesrep without displaying in the browser
// save the salesrep and their matching sales amout in a name/value pair in the map
if: $salesrep == (field: 'salesrep');
$salesrep_sales->(Insert: $salesrep = $sales);
[if: $salesrep != (field: 'salesrep')]
<tr><td>[field: 'salesrep']</td> <td>[$salesrep_sales->Find: (field: 'salesrep')]</td></tr>
[$salesrep = (field:'salesrep')]
You can also create summary headers and footers by using the [Summary_Header] and [Summary_Footer: ‘…’] custom tags. For more info on those take a look at the LassoSoft Summary Headers and Footers Tip of the Week.
And , take a look at this LassoSoft Tip of the Week showing a quick and painless way, using variables, to display sub-summary headers in a list using just the [Records] tag.