When writing a report, the first thing to consider is what you're trying to report on. That tells you what tables you will need to include in your report. The Second thing is to figure out what to filter. Sometimes the answer is nothing, when you want to work with all records. Usually, though, you want to filter out some records. Maybe you only want to look at records pertaining to this year - this would typically require only a single item.
How Selection Criteria work
In order to select data for your report, you write a statement that evaluates either true or false. This statement is checked for every record (row) loaded from the database. If it evaluates true, then the data is included in the report. If it is false, then that row is skipped, and we move on to the next one.
You can use this information to write compound selection criteria. A simple criteria might look something like this:
{CDS_ACCOUNT.MailFlag} = "Y"
MailFlag indicates whether or not a user wants mail. If it is set to a capital 'Y' then they have indicated that they will accept mail from us. The = operator (equals sign) returns true if the items on each side of it are equal, and false if not. Thus, if we pull the user's address information from the CDS_ACCOUNT table, and we reject every record where the above statement is false, we will have a list of addresses only for those users who want mail.
What if we wanted to offer the functionality to display information on players whose name contains a certain "string" of characters, but if we don't enter a string, we want to show all players? This is easily done with a short compound selection criteria. First we create a parameter, which I usually call "Search String". The criteria will look like the following:
( if {?Search String} = "" then true else {CDS_PLAYER.PrintedName} like "*{?Search String}*" )
This breaks down fairly simply. We start with an if statement. If the statement is true (in this case, we check to see if the search string is equal to nothing, which means nothing was typed) then we execute the function coming immediately after the comparison. If that function is followed by the word "else" then if the comparison was not true, we execute the function following the "else". Thus, if it's empty, then we return true, so that the records are selected and appear in the report. If not, then we check to see if the search string appears anywhere in the string.
Writing Select Criteria Manually
When writing reports for Aristocrat OASIS, the Select Wizard is typically somewhat unhelpful. For instance, basically all date values are stored as datetime values to assist comparison of internal dates. However, you frequently want to prompt a user only for a date, not a date and time. You cannot directly compare a date to a datetime, hence the fact that you can use the Date() and Time() functions on datetime values.
This particular issue is trivial to solve. Assuming you have a parameter value called {?Date} (which is a date type) and want to find out if it matches your GamingDate, you can just do the following:
{?Date} = Date({CDS_STATDAY.GamingDate}
Another useful technique is the ability to do case insensitive searches. This is easily done by using the LowerCase() or UpperCase() function; LowerCase() is used by convention.
Just prompt your user for a string value, and let them know it isn't case sensitive. Then use the following selection criteria (with variations for the names of your parameter and the field or what have you that you're looking for it in:)
InStr(LowerCase({?Search String}, {CDS_PLAYER.FirstName}) > 0
InStr() is a function that checks to see if your first string (the one before the comma) is found in your second string, and returns the number of matches. If the number is 0, then there are no matches; your search string was not found.
These are quite simple examples of "advanced" record selection formulas - advanced meaning anything you can't get done just by clicking buttons in the Select Expert. You can actually enter formulas in the Select Expert, but it makes much more sense just to use it for whatever it will work for through simple clicking and do the rest in the editor.
Mailing Lists
In a more complex example, I will demonstrate and explain how to develop a mailing list for OASIS.
When mailing your customers there are a number of things you may want to take into account. The first, and most obvious, is whether or not the user has a deliverable address. The first indication of this is the {CDS_ACCOUNT.BadAddress1} flag, which unfortunately is a string (in the database, actually, a char value) which is either 'Y' or 'N'. We want BadAddress1 to be equal to 'N'. There is also a flag which specifies that the user does or does not want mail: {CDS_ACCOUNT.MailFlag} is what we're looking for here. We want it to be equal to 'Y'. Both of these can be accomplished easily through the Select Expert.
We also want to make sure the account's status indicates that the player is still playing, in most cases. If their account has been closed, merged, etc., we probably do not want to send them mail. The field {CDS_ACCOUNT.AccountStatus_ID} should be equal to 'A' for active accounts.
We also need to check that the address is feasibly a valid one, because OASIS does not do any address checking. In fact, it doesn't even check to see that your address has all the necessary elements to be delivered. We will need to do that.
The code follows:
{CDS_ACCOUNT.AccountStatus_ID} = "A" and
{CDS_STATSUMMARY.IDType} = "P" and
{CDS_ACCOUNT.Address1A} <> "" and
( ( {CDS_ACCOUNT.City1} <> "" and {CDS_ACCOUNT.State1} <> "" ) or {CDS_ACCOUNT.Zip1} <> "" ) and
{CDS_ACCOUNT.MailFlag} = "Y" and
{CDS_ACCOUNT.BadAddress1} = "N"
First, we check account status. We also make sure that the CDS_STATSUMMARY records we're looking at are for players and not groups, hence the IDType = "P" statement.
After that you can see a slightly more complicated set of criteria for checking for valid addresses. All addresses need a street address, so that stands by itself. They also need to have a zip code, or a city and state; either one is generally enough to get your letter where it's going, so it's worth a try.
Finally, we check that the MailFlag (is 'Y') and the BadAddress1 value (is 'N'.)