Handling Zero Results in a VBScript-Powered Report

If experience has taught me one thing about programming, it is this: expect the unexpected. If you write a report that asks for a number, expect the user to type in a word. If you are looking for a text string, expect someone to leave it empty. Today I'm going to tell you how to handle the unexpected situation of having a query return no results.

In a regular ActiveReports design, this is no big deal. The report just returns empty space where the data would be. However, if you have written VBScript to filter or manipulate the data in any way, then the result of a null set of query results is usually an ugly error message to the user about BOF or EOF, something the average user will blink at and then click OK with no real understanding as to what went wrong.

Luckily, adding a routine to handle a null-resultset situation is pretty easy. The trick is to use the RecordCount property of the ADO object to determine if there are results or not. If there are no results, you can tell the routine to skip all the data filtering and manipulation code and just run the "plain" report, which will still be blank because of a lack of data. As added information, you can pop up a message box to explain why the report will be blank.

Because most data manipulation is done before the report starts rendering, you will probably need to add this code only to the ActiveReports Document object and the OnReportStart event. However, if you are doing some further data manipulation in a later section of the report layout too, then add it as needed to each script in the report design.

Here is the sample VBScript code for a Shelby v5 Report design. The ADO object is called DataSource_Shelby_ (Yes the final underscore is part of the name. Why? I don't know. It's a mystery).

Sub onReportStart

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

{insert your data filtering or manipulation code here}

Else

MsgBox("There are no results to use for this report. Please check the query and try again after the query returns results.")

End If

End Sub

Once this code is in place, even when changes in the data or changes in the query mean that no results are available, the user will not be presented with a geek-speak message, only a warning and a blank report.

Querying the Schema

Since I didn't get to post last week, I'll post twice this week to make up for it.

Today I was presented with a problem in which a customer wanted to know which tables had been updated by a particular person in a particular time frame.

Immediately I knew that he would need to find out the values of the WhenUpdated and WhoUpdated columns, which are found in many of the tables of the Shelby Systems v5 database schema. The problem was that there was no way of predicting exactly which tables had been affected in the given time frame. In addition, there was no way of know exactly which tables existed in the customer's database, at least not without doing some digging or getting a copy of a full backup of the customer's data. Both of these options would take more time than was really called for.

What I needed was a query that would let me get a list of the tables that contained one or both of the columns I was interested in finding. This is called a "meta-query" because it queries the database schema instead of the regular data storage tables. Of course, this being SQL Server, even the schema is stored in tables. Specifically, the information about the columns in the tables is stored in a table called information_schema.columns.

I whipped out the SQL Cookbook by Anthony Molinaro, which is a superb resource for pushing the ordinary limits of SQL code, and I quickly found the entry on querying the schema to list column names in a given table. I simply reversed the conditions of the query to list the tables that contain a given column. The result was this:

select distinct
table_name
from
information_schema.columns
where
table_schema = 'Shelby' and column_name in ('WhoUpdated', 'WhenUpdated') and table_name not like 'vw_%'
order by
table_name


Running this code gives a list of all the tables that have either WhoUpdated or WhenUpdated. Because all of the tables that have one also have the other, the column_name condition is a bit redundant, but it shows how you can search for matches on one or more column names.

When the customer runs this query, it will list for him only the tables that are in his database installation, so it saves me having to figure out which tables he has and which he doesn't. Once he has the list of tables, it is a matter of running a query on each table to find a match on the date or the name of the last update information.

What is also great about this query is that it can be run straight from the built-in ShelbyQUERY module. There is no need to use the SQL Server Management Studio or any other outside querying mechanism.

P.S. If you're wondering, yes, there is an information_schema.tables table that stores information about the tables per se.

Listing Husband and Wife on One Row - Part 3

I didn't get a post in last week. I have been busy reviewing and revising the Fall 2009 training season workbooks and trying to get SSTips ready to send out as well. So today I am finally getting around to posting part 3 of the series on putting names together on one row. Today we'll look at how to put the names together into one column as a combined name. This is tricky because of the possibility of different last names, as well as the fact that sometimes spouse information is not available even when we know that the person in the database is married. Another factor is that the gender of the head of household may be male or female. And I'm not even going to get into handling special titles - such as Dr. - because that would add another layer of complexity to the basic process of combining names.

The following code puts two names together. It limits the results to those who have a TESTGRUP profile code, the same as in the previous parts of this series.

/* query begins */
select
SelectedName = SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName,
Spouse = Spouses.FirstMiddle + ' ' + Spouses.LastName,
CombineIfMarried =
case /* This begins a conditional tree. The top branch of the tree is to check that the selected person is married. */
when SelectedNames.MaritalStatus in ('M','R') then
case /* The next branch is to check the gender of the selected person. */
when SelectedNames.Gender = 'M' then
case /* The bottom banch determines if the person and the spouse have the same last name. If there is no spouse info in the database, both comparisons will fail and the "else" condition applies. */
when SelectedNames.DifName + Spouses.DifName = 0 then Spouses.FirstMiddle + ' & ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
when SelectedNames.DifName + Spouses.DifName = -1 then SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '') + ' & ' + Spouses.FirstMiddle + ' ' + Spouses.LastName
else 'Mr. & Mrs. ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
end
when SelectedNames.Gender = 'F' then
case
when SelectedNames.DifName + Spouses.DifName = 0 then SelectedNames.FirstMiddle + ' & ' + Spouses.FirstMiddle + ' ' + Spouses.LastName + isnull(' ' + SpousesSuffixes.Descr, '')
when SelectedNames.DifName + Spouses.DifName = -1 then Spouses.FirstMiddle + ' ' + Spouses.LastName + isnull(' ' + SpousesSuffixes.Descr, '') + ' & ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName
else 'Mr. & Mrs. ' + SelectedNames.LastName
end
else 'Mr. & Mrs. ' + SelectedNames.LastName
end
else SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
end
from
Shelby.NANames as SelectedNames inner join
Shelby.NAProfiles as Profiles on SelectedNames.NameCounter = Profiles.NameCounter and Profiles.Profile = 'TESTGRUP' left join
Shelby.NANames as Spouses on SelectedNames.FamNu = Spouses.FamNu and Spouses.UnitNu = case when SelectedNames.UnitNu < 2 then abs(SelectedNames.UnitNu -1) end left join
Shelby.NASuffixes as SelectedNamesSuffixes on SelectedNames.SuffixCounter = SelectedNamesSuffixes.Counter left join
Shelby.NASuffixes as SpousesSuffixes on Spouses.SuffixCounter = SpousesSuffixes.Counter

/* query ends */

The CASE statement to put the names together has three levels, as noted with the comments in the statement. The first level divides those who are married from those who are not. The second level divides the people selected into male and female, and the third level divides those spouses with different last names.

Listing Husband and Wife on One Line - Part 2

Last week I posted on how to put both husband and wife on one line by joining to the names table (NANames for Shelby v5) multiple times. That is a useful technique when you want to show the spouse information for every name that is part of the desired result set. However, a different technique is needed when you want to show spouse information only when both spouses are part of the desired result set. A typical example might be a label report for the choir or another group in which one or both of the spouses might participate, and when both spouses do participate, they should both be combined. When only one spouse participates, the other should not be part of the results.

This new method selects the names as you would normally, joining only once to the names table. But then add a GROUP BY clause to the query, grouping by the common factor for the family (NANames.FamNu for Shelby v5). After you group the results, you can use a CASE statement to select either the Head of House or the Spouse name to appear in the results.

In the example query below I have selected all the adult choir members by using an inner join to the NAProfiles table and at the same time limiting the included Profile values to those that begin with MUAC, which is a common prefix on all the adult choir profile codes. The query uses GROUP BY and CASE statements to pull the spouses together and list them in the appropriate columns of the results.


select
HHFirstName = max(case when Names.UnitNu = 0 then Names.FirstMiddle end),
HHLastName = max(case when Names.UnitNu = 0 then Names.LastName end),
SPFirstName = max(case when Names.UnitNu = 1 then Names.FirstMiddle end),
SPLastName = max(case when Names.UnitNu = 1 then Names.LastName end)
from
Shelby.NANames as Names inner join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter and Profiles.Profile like 'MUAC%'
group by
Names.FamNu


Here are the results:

You can see that the Blackwells , the Carters, and the Glovers are in the choir together. In the visible results all the other individuals do not have a spouse in the choir.

With the two techniques from last week and this week, you should be able to get the names of spouses together whenever you need them in one row of results. Of course, listing them on one row is usually only part of the requirement. Often you will want to combine them together into one column as well, with a result such as "John & Jane Doe" or "Mr. & Mrs. Smith." That process is surprisingly complex, and will be the topic for next week's post.