How do I show my company's total sales, with subtotals by region and salesrep?

Displaying Sub-Summary Headers in Reports on the Web with FileMaker and Lasso.

8-12-2009 by Tami Williams

Why

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.

     

How

1. Take a look at the Demo database, "Sales Report" layout
2. Build a lasso page using the [Map] tag

Demo Files

• Demo.fp7 - the FileMaker database
• SubSummary_SalesRep.lasso - subsummary by salesrep
• SubSummary_Region_SalesRep.lasso - subsummary by region and salesrep

How it all works

<?LassoScript
// here are key sections of the code

// the inline (database call) is done only once, right here
inline ...;

var: 'salesrep' = '' ,
'salesrep_sales' = (map),
'sales' = 0 ;

records;
	   // reset the salesrep and sales amount when the salesrep changes
	   if: $salesrep != (field: 'salesrep');
	   $salesrep = (field:'salesrep') ;
	   var: 'sales' = 0 ;
	   /if;
	   
	   $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;
/records;
/inline;
?>

<table>
<tr><td>SalesRep</td><td>TotalSales</td></tr>
	 [records: -inlineName='subsum']
	 [if: $salesrep != (field: 'salesrep')]
	 <tr><td>[field: 'salesrep']</td> <td>[$salesrep_sales->Find: (field: 'salesrep')]</td></tr>
	 [/if]
	 
	 [$salesrep = (field:'salesrep')]
	 [/records]
</table>

Alternatives

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.

Privacy |  Copyright  AskTami Inc. dba Creative Computing.   All rights reserved.