Reporting Totals Using SBL
Summary: There are many situations where you may need to find totals for a database file. For instance, if you have a file for items sold, you might want to find the total quantity of each item sold so that you can compare it against the inventory. Either by using the report generator or by writing a short SBL program, you can easily find the total. This technote describes how to calculate totals for a database file using a short SBL program. You will find that once you have learned the basics, you can do more sophisticated tasks by modifying the SBL code. 
Reporting totals
Essentially, in order to find the total for a given field in a file, you want to perform a query and place the result of the calculation into a variable which you can then use either to display on a form or store in another database file. This technote uses the ITEMS file which is in the Superbase 2.0 SAMPLES\STOCK subdirectory. Upon examining the ITEMS file, note that there is a OrderQuantity field which indicates the quantity of the item ordered. The VIDEO file has fields for InStock and MinimumStock. The problem at hand is to find out if the Instock amount is enough to meet the requirements of the total quantity ordered for any video. This way, any time the difference between the amount of videos in stock and the total quantity ordered falls below the minimum stock amount, you can order more videos and increase the inventory. 
As the InStock amount is entered by the user you want to go through the ITEMS file and calculate the total quantity of each video ordered. The following code calculates the total number of any particular Video that was ordered.
	SUB main()
		GLOBAL total%' Visible to main and any other procedure
		total% = 0
		OPEN FILE "video"
		OPEN FILE "items"

		REM Ask the user for which Video he would like to calculate total
		REQUEST "Select a Video for which you","would like to calculate the Total 
		Quantity Ordered",20,choice%%,Video_code$,60,VideoCode.VIDEO,Title.VIDEO
			(Previous two lines should be placed on one line in Program Editor)
		REM If user clicks Okay, call procedure to find total
		IF choice%% = 1 THEN 
			CALL FindTotal(Video_code$)
		END IF 
	END SUB 

	SUB FindTotal(code$)
		FILE "items"
		REM Set up query to find total for OrderQuantity field

		REPORT ON OrderQuantity.ITEMS
		AFTER REPORT 
			total% = SUM OrderQuantity.ITEMS'	Store SUM in total%
		END REPORT 
		SELECT ;
		WHERE VideoCode.ITEMS = code$' 	Find SUM for a particular Video
		END SELECT 
		REQUEST "Video Code: " + code$,"Total Ordered Quantity = " + STR$ (total%,"99999"),0
		FILE "video"
		INDEX VideoCode.VIDEO
		SELECT KEY code$' 	Selects the correct Video in the VIDEO file
		VIEW 
 
		REM Check if current stock is sufficient
		IF (InStock.VIDEO - total%) < MinimumStock.VIDEO THEN 
			REQUEST "Video Code: " + code$ + " Currently in stock = " + STR$
			(InStock.VIDEO,"99999")," Need to order more copies!",0
			(Previous two lines should be placed on one line in Program Editor)
		ELSE 
			REQUEST "Video Code: " + code$,"Stock meets minimum requirements",0
		END IF 
	END SUB 

The first Request Box in main() is used to let the user select which video to find the total for. In most cases, you would want a WHILE loop which goes through the entire VIDEO file and uses the FindTotal() procedure to find the total quantity ordered for each video in the ITEMS file. You can make the necessary modifications to do this yourself.
One important point to remember is the above method is the most efficient method to use in trying to find totals for a database file. Another approach for coding the FindTotal procedure is to set a filter on the ITEMS file then use a WHILE Loop to go through the filtered records and keep a running sum in a variable. Functionally, the two methods work the same, but in terms of performance, using the REPORT and SELECT commands to query a file runs much faster. This becomes more apparent when working with a database with a large number of records. 















Program:	Superbase	
Versions:	2.0
Date:	April 23, 1993