Exception Report: Curtains Too the SQL offers its user the functionality of an extensively customizable report writing tool known as the exception report. This report lets you define criteria by which the system will search through your databases to return to you a final document containing the appropriate members.

• Reports
• Member Reports
• Exception Report
• Advanced Version

Step 1

Here you have the option to load a pre existing report of search criteria. If you have a report saved already you would click load and navigate to the folder it resides in, then open. Files are saved in .cfg format. The pre-defined search criteria are then loaded into the program allowing you to search your databases for the information again. For the purposes of this guide we will assume you are starting a new report afresh.

Step 2

This is where you define what criteria the system will use to search your databases. The available search fields are shown on the left of the screen and are divided into database tables for ease of navigation. (fig 1.1)

As an example for this report, lets assume your aim is to create a report of all members with a Share 1 account, who have not made a transaction for the past month. You would also like to see the member name, member number, balance of the share account and address of the member so that you can send a letter to them. (see mail merge).

As shown in (fig1.1), you would firstly drop down the database table and select MemberBalances. You are then presented with the available fields from the MemberBalances table.

As you are wanting to search by a specific product, the first thing you want to do is define what product you are going to refer to when searching. Double click on Productcode. (fig 1.2)

You will note Productcode now appears at the bottom of the grid. As you want to search for Share 1 accounts in this instance, you would say Productcode = S1. (fig 1.3)

(fig 1.2)

(fig 1.3)

The other options you have for searching are as follows

• = - Indicates that field must equal the criteria (i.e. S1)

• < - Indicates that field must be less than this amount (relevant for balances, dates etc)

• > - Indicates that field must be greater than this amount

• >= - Indicates that field must be greater than or equal to this amount

• <= - Indicates that field must be less than or equal to that amount

• <> - Indicates that field must NOT equal that amount

• LIKE - Indicates that field must be similar to that mount (relevant for postcodes, etc.)

• NOT LIKE - Indicates that field must NOT be similar to that amount

• IN - Useful for selecting multiples ( e.g. IN S1, S2, S3, S4)

• BETWEEN -Indicates that field must be between 2 amounts (useful for dates, balances etc.)

Once you have the parameters filled in, click accept as shown above (fig 1.3). The search criteria will then appear in the grid as shown below. (fig 1.4)

(fig 1.4)

You can repeat this process with other fields as many times as is necessary to refine your search criteria the way you want it.

(fig 1.5)

As you will note in figure 1.5 you have added the main search criteria that you will need to search your members with. Although we are not searching for a specific member address it is necessary to include the type of address we would like the exception report to search by (home, work1, statement1 etc.)

(fig 1.6)

MemberAddressType will appear twice in the list (fig 1.6). It is necessary to right click on one of the entries and delete the row (which you can also do at any time should you enter a criteria in error).

This will leave you with your final search criteria (fig 1.7), indicating that you wish to search the databases for members with a Share 1 account which has a last transaction date less than your specified date, looking only at their home address (this will avoid duplicate entries as some members will have different work/home/statement addresses)

(fig 1.7)

NB: the And/Or column allows you to define whether each criteria must be met by the member or, if only one criteria needs met in order for them to appear on the final report.
Step 3

This step is used to indicate which fields you will see output on the finished report. (Step 2 defines the search criteria where as Step 3 defines the information presented to you).

Much the same as the previous step, drop down the table menu at the left and select the relevant fields (by double clicking). In this case, you wish to see the member name, member number, member address, share 1 balance and last transaction date on that account. Thus, Step 3 on your report would look like this. (fig 1.8)

(fig 1.8)

Note that MemberAddress is broken down into Line1, Line2, Line3, Line4 and PostCode. This allows you to select each individual part of the members address should you not require the entire thing. This is also true for the members name.

As you have specified on Step 2 that the product code the exception report is searching for is share 1, the balance will automatically refer to this account, as will the last transaction date. Once you have all the criteria you wish to appear on the report selected, you may move on to step 4.

Step 4

This step is designed to allow you even more control over the layout of the report. You can pick and choose in what order the fields you selected in step 3 are presented to you on the final report. The selection box on the left allows you to designate in what order your fields will appear. (fig 1.9)

(fig 1.9)

To determine in what order the field will appear, click on it so that it is highlighted in blue (as shown in fig 1.9). Any information you type into the item box on the left will then become relevant to that field. For example, if you wanted Product Code to be in the 1st column of your finished report, you would highlight it as shown in fig 2.0 and type 1 in the order box then click update.

(fig 2.0)

Please note you also have the option to sum column which will count the contents of the field you have selected and present you with a total figure.

Step 5

This step is used to preview your finished report. (fig 2.1)

Click preview in order to see your finished report. You will be presented with an on screen depiction of your search results. You also have the option to print a hard copy of your report here.

( fig 2.1 )

• Preview - Allows you to see your search results on screen in the grey box

• Output - Allows you to save to an external format such as an excel document or text document

• Letters - Takes you to the letters option of Step 6

• Print - Will print out a hard copy of your search results to your printer

• Back - Lets you return to any of the previous screens to amend your search criteria

• Next - Takes you on to step 6

• Exit - On exiting the report you are given the option to save you search criteria so that you may run the same search again without having to re-enter all the necessary information

Step 6

This step allows you to automatically send a pre-defined letter to the members who appear on the exception report (see letter data setup). Simply click letters and select the relevant letter (fig 2.2.) number. This will automatically print a letter for each of the members who appear on the report. It will fill in the fields you have defined as the variables on your letter setup.

(fig 2.2)

Reporting Via SQL Server

When running Curtains from an SQL Server, you are given yet more definable options for creating your search criteria on the exception report. When selecting a database table, you will notice you are presented with a shortcut bar from which you can select your criteria. (fig 2.3)

(fig 2.3)

• Table - Selects which database table you are accessing. In this instance you have selected MemberAddress

• Address Type - Lets you define which address type you will search for (bank1, HOME, statement1 etc.)

• Field - Lets you select which field from the table you wish to search on

• Arith - Apply an arithmetical value to the field

• Literal - Define a value for the field

• Compare - Defines the parameter of the search (see fig 1.3)

• Table - Allows you to compare against another table

• Product Code - Lets you define if you are searching for a particular product code within this table

• Field - Lets you compare this field against the field before the Compare

• Arith - Apply an arithmetical value to the field

• Literal - Lets you specify the particular contents of the field you are searching (fig 2.4)

(fig 2.4)

As you can see from (fig 2.4) this search would look through the ADDRESS table, whilst looking at the HOME address of the members, specifically at their Postcode. Assuming this was looking for members within an area, the end of the postcode would vary. This means you are not looking for an exact match to the literal field but something similar to it. For this reason the Compare is set to LIKE. You will also note the beginning of the postcode is followed by a %. What this indicates is that the exception report should look through your databases for a postcode G51 with any permutation afterwards and present it to you on a final report.

The SQL shortcut bar allows the user far more malleability in the exception report as you are able to compare one field against another.

An example of a use of the Arith field would be as follows (fig 2.5)

(fig 2.5)

(fig 2.5) indicates that you wish to search the value of share 1 from the MemberBalances field multiplied by 3, less than the loan 1 balance divided by 2. So any member who has a share 1 balance (*3) which is a lesser amount than their loan 1 balance (/2). The exception report would then only search for members who fill this criteria.

(fig 2.5) is an example of just how complex and in depth you can make your exception report searches. More often than not it is not necessary to be so in depth in your searches, but the option is available should you wish to use it.