Tutorial 09: Creating a quick and dirty report from a database

June 16, 2008

© NSB Corporation. All rights reserved.

Contributed by Harold Wood

This tutorial is a bit more advanced. It assumes you already know about databases, file objects and how to program in NS Basic.

You've written that really sweet App, everyone loves the screens, it does what it should, but then you get the "Well, that report is nice but it would be really cool if ........."

So what do you do? Write another report sub, and then for the next "Suggested Change" write another and so on? Being the extremely lazy programmer that I am I like to make my code as reusable as possible. That way I can add the extra reports without a minimum amount of code effort, it keeps my userbase happy, my pocket book happy and me sitting on my couch watching bad scifi movies while pecking away on my laptop.

So let's make things easy now:

Add a comboBox to the report form, call it box_Reports. Populate the list with the names (shortened of course) of your reports.

Define the SQL for your reports as constants, use the replaceable parameter method when you define the SQL, also, and this is important, give good thought to the names of the columns you will report, use Aliasing to make the column names sensible and meaning specific, so 

SELECT COUNT(*) FROM ITEMS;
becomes 
SELECT COUNT(*) NumItems FROM ITEMS;
This will become important later. If you need a multipart name try the Underscore character, for example Count_Orders_On_Backlog.

You will find that with a little thought you can easily make the db Engine do the majority of the work for you, for example 

SELECT (SELECT AVG(PurchasedPrice) FROM ITEMS WHERE ItemId = oi.OrderItemId) Average_Price,
(SELECT MIN(PurchasedPrice) FROM ITEMS WHERE ItemId = oi.OrderItemId) Lowest_Price,
(SELECT MAX(PurchasedPrice) FROM ITEMS WHERE ItemId = oi.OrderItemId) Highest_Price,
(SELECT SUM(NumUnitsOrdered) FROM OrderItems WHERE OrderItemId = oi.OrderItemId) Count_Ordered
FROM OrderItems oi

The above query is just an example, it will provide a nice little report that can be useful and it doesn't take that much code to make it work.

Now that your SQL is ready you need to put together an output engine for it. Here's where we do the coding. For the sake of simplicity ive left out the dialog box routines so we can just focus on the file IO.

This same code can be used without changes for a variety of quick reports. You could easily change it to output HTML for a cleaner format or csv to import into regular excel(not the pocket version).

Have fun!

Some more: Text, csv, and html output

"SaveWhere" is my file dialog.