Viewing and Exporting Data with SQL Selects
|Top Previous Next|
SQL stands for Structured Query Language. It is the basic programming language used by the Firebird database that stores the data in DONATION, and by almost all major database systems today, such as Microsoft or Sybase SQL Server or Oracle. Some very technically experienced users of the program may be familiar with SQL, and may wish to explore the database using it. That is what the Database à SQL Select feature allows you to do. I may also give you some instructions to use this feature either to help debug problems you are having, or to create simple ad-hoc reports that are not included in the Reports menu and that cannot be created with Reports à Custom Report.
N.B. This option is not available with the free Lite version of DONATION.
While this SQL Select utility can be used to create custom reports, most users will prefer to use the much easier (though less powerful) Reports à Custom Reports option, which does not require any knowledge of SQL. With that option, you just select columns from lists, specify a range of years, and click OK. It generates the SQL for the report automatically, and then puts you into this SQL Select window.
When you select Database à SQL Select (or come to here after clicking OK in the Custom Reports window), the following window comes up on which you can enter a SQL Select statement:
If you have come into here from Reports à Custom Report, the SQL statement will already be filled in to the large entry box in the window.
You can save a SQL statement that you enter here (or that was created by a Custom Report) to a file with the Save to SQL File button, or retrieve an existing SQL statement from a file with the Open SQL File button, to re-use it. However, more commonly you would instead use the Memorize Report button - more details on that are below.
On this window you can also select a Report Style – either Normal or Grid.
The Normal style looks a lot like the built-in reports. The advantages to using the default Normal Report Style are that it has a standard header section, like the built-in reports, and it starts out in Print Preview mode, so that you can have a better idea of exactly what the printed report will look like.
The Grid style looks more like a spreadsheet, with boxes around each row and column, but it has no header section (except for the column headings). It has some interesting features. You can change the displayed width of columns by clicking on the border between two columns in the row of headings, and dragging it left or right. You can change the order of columns by clicking a heading, and dragging it left or right. When you print the report, the grid is not printed.
You can also select between the Portrait (normal) and Landscape (sideways) Orientations for the report.
In addition, you can specify the Report Title on this window, though it will only be shown on the report if you use the Normal Report Style, not the Grid style.
Once you have your desired statement entered, click the Run button. If there are errors in the SQL, a message box will come up explaining the error. If the SQL is valid, the results will be displayed as a very simply formatted report on the Reports window, as if it was a normal report. From there, you can do anything you could normally do on the Reports window, including Sorting or Filtering the data, Printing the result, and using Save As to save the results to a text file or other file format.
Note: any columns that hold dollar figures (or other currency figures, if you are not in a country that uses dollars) will automatically have a total created for them at the bottom of the report.
Memorizing your Report
You can memorize a report in the SQL Select window with the Memorize Report button. That will memorize the SQL, Report Style, Orientation and Report Title, and allow you to replay it later with the Reports à Memorized Reports menu option. Or, you can wait until you are on the report-viewing window after clicking Run in this window, and use the Memorize button there, possibly after using the Sort or Filter buttons, since any sort or filter will also be memorized with the report. See Memorized Reports for full details.
There are six arguments that can be used in different combinations for selecting only a single donation date, a range of donation dates, a range of donation dates and optionally a donation category, the current fiscal year, or the current fiscal year and optionally a donation category, to include in the data selected by the report. These would be included in a WHERE clause in the report (which restricts the data to data satisfying a given condition). There are radio buttons in the Custom Reports window that make these be included automatically, but you can also use them directly if you understand what you are doing. The following are the options:
Details and Examples
The DONATION database is composed of tables, one table for each type of data. The tables in the database are named constants (for your Organization Info, and to hold the current year number you are working on), donor, donation, category (for donation categories), donor_category1 and donor_category2 (for the donor categories) and receipt. There are a couple of other tables, but they are used only for very technical purposes and you are unlikely to want to select from them.
There is also a view (which behaves like a table) called donors_last_2years, which includes all donors in the current working year, plus all donors from the previous working year that are are not present in the current working year, either because you deleted them, or because they were added after the initial copy of all donors to the new year, when you first switched to the new year.
Here is one example of how you might use this SQL Select routine directly, without going through Reports à Custom Reports. Suppose you wanted a list of your donation categories, for the donation year 2011. There is no report built into DONATION to create such a list. (Well, OK, there is, under Reports à Category. But this is still a good example!) Enter the following SQL statement:
Select * from category
Where year_number = 2011
Note that "*" means "all columns in that table". When you Run this SQL, you will see that it generates a report listing three columns – Categorynum, Year Number and Description. You may decide that all you really want to see is the Description column. Close the Reports window, and change the SQL to say:
Select Description from category
Where year_number = 2011
Print off the result, and there is your list of donation categories!
To see what columns are available in each table, use "select * from tablename" for the different tables. That will give you a report listing every column. You can then change the "*" to one or more column names, separated by commas, to get the columns you are really interested in. For instance, "select Year_Number, Description from category". All SQL entry is case-insensitive, i.e. it doesn't matter whether you enter upper case or lower case for things like column names and keywords like "Select". For those who already understand SQL, the table structures are all defined in the file TABLES.SQL in the main program directory, usually C:\Program Files\Donation.
One problem you can have if you use this utility to create reports, is that the report may be too wide to print. There are a couple of things you can do to resolve this problem. If it's only a bit too wide for the standard print format (which is in Portrait mode, where the page is vertical), select the Landscape radio button in the SQL Select window to switch to Landscape mode, where the page is horizontal. Another obvious option is to reduce the number of columns you are displaying. If you use the Grid Report Type, you can click and drag to make the columns narrower.
As with any other report that is displayed, you can export the results of a SQL Select to various output formats, including Excel, using the Save As button once the report is displayed. This is one good way to "pretty up" a report created with SQL Select or Custom Reports, if you are not quite satisfied with the results.
It is also possible to use this routine to make changes to the data in your database, using the SQL INSERT, DELETE and UPDATE statements. In normal usage there would be no reason to do this, because all normally required tasks can be done using the other features of DONATION. However, in rare cases, particularly if I am helping you with a technical support issue, this may be appropriate. You will need me to give you a special password in order to use this feature.
If you need further help with this option, feel free to contact support for assistance.
For more information on how to write SQL, there is a web page with a list of several SQL tutorials, at http://www.thefreecountry.com/developercity/sqltutorials.shtml.
Note to Users of Version 2.70 or Earlier of DONATION
If you previously used version 2.70 or earlier, which used the Sybase Adaptive Server Anywhere database, and have upgraded to version 3.00 or higher, which uses the Firebird database, some of the database field names have been changed, and also there are some fairly minor "dialect" differences in the SQL used by the two databases. As a result, if you had any saved SQL files that you used with SQL Select with the older version, they will probably need some adjustment in order to work with the new version. You can use the techniques in this help page (e.g. using "select * from tablename", or consulting the file TABLES.SQL) to determine the new field names. If you get stuck, contact technical support.