SQL Saturday in Richmond, VA

I know it has been awhile since I posted anything. Thanksgiving, Christmas, and New Year's Day have all preempted my usual posting schedule. Even now I just have time to post a brief message about the upcoming SQL Saturday in Richmond, VA. On Saturday, January 30th, there will be a SQL Saturday event at the ECPI College of Technology. I will be presenting a session on SELECT Query Fundamentals at this event, and there are lots of other topics there too, for beginners all the way up to experts.

If you want to learn more about SQL and if you can be in Richmond on January 30th, SQL Saturday is the place to be.

Hope to see you there!

Registrations - Key Information

I worked today for almost two hours with a customer in California who was seeing unexpected information in a query I had written for them some time ago. There were three things wrong with the results, and I learned a lesson from each of them when I discovered the ways to correct them.

First, the query was showing incorrect Custom Field values. After double-checking that the Registrations attendee information was displaying the expected values, I started digging through the query and the tables to figure out what was going on. I spent almost 2/3 of the entire time looking for the cause of this problem. To make the long story short, what I discovered is that the [RGRegCatIndCust].Counter column is not a truly unique Primary Key column. I found that the same Counter value had been used for two different attendees' custom information answers in two different events. The information that was showing up in my query results were for the wrong attendee at the wrong event. In order to correct this problem I had to use both [RGRegCatIndCust].Counter and [RGRegCatIndCustSetup].RGEveCounter as limiters on the custom field values. Once I did that, the query displayed the expected values.

Second, the query was showing the same registrant twice. This was because the attendee had been assigned to the wrong Category at first, and when the attendee information was moved to the correct Category, the amount owed changed. A new invoice had been created to adjust the amount owed. Since my query was grouping by invoice, the second invoice created a second entry in the results. I had to go in and remove the grouping by invoice and then add aggregate functions to the SELECT column information that dealt with invoice info. Once that was done, the correct balances displayed. I learned to always use an in-line view for the Registrations invoice information, because of the way that I used GROUP BY to pivot the custom answers into columns.

Third, the query was returning the wrong number of results. It was 11 shy of the correct number of attendees. I found 10 of the missing 11 by using LEFT joins to the invoice-related tables instead of INNER joins to them. When an attendee had no balance due for registering, there was no invoice created, and therefore the INNER joins were eliminating those attendees. The final 1 person was trickier. In order to get the last person on the results, I had to re-do the FROM clause so that [RGRegCatInd] (the Attendee table) was at the root, making sure to do LEFT joins to all the subsequent tables. That was I was sure to get at least one row for each attendee. What I discovered after I made the change was that one person had registered via WebView and that somehow in the synchronization process the Registrant information was lost but the Attendee information remained. Therefore my original approach of using RGReg (the Registrant table) as the root table omitted the one person whose Registrant information was lost. After I got the final missing person's row in the results, I could readily see that the Registrant information was null.

It was a productive two hours, and I learned some tips that I will apply to all my future Registrations queries. And I will probably need to go back and change some of my prior queries as well.

Richmond Code Camp

I should have posted a message about the Richmond Code Camp weeks ago, since it actually happened October 3rd, but time just has gotten away from me all month. This was my first Code Camp experience, but certainly not my last. In case you haven't been to one, I should explain that Code Camp is a completely free day of lectures from programmers and other developers, mostly local personalities, but sometimes notable figures from the larger programming world. The most interesting lecture I attended was a presenter from Microsoft itself. More on him and his talk later.

There were so many good things going, and I learned something interesting in nearly every session. Here is a list of my take-aways from the event:

• jQuery has nothing to do with SQL. I thought I was going to learn a bit about how to query a database source from a web page, but that was not it at all. JQuery is a cool add-on for JavaScript, but it has nothing particular to do with querying databases.
• SharePoint is much more powerful (and much more complex) than I gave it credit for. I went to two sessions on SharePoint, and I am very impressed with its capabilities. However, I am also quite certain that I am far away from ever tapping its full potential.
• I picked up the word "trivial" as a descriptor for solutions that do not require much effort to implement -- and the very important "non-trivial" descriptor for solutions that do.
• I saw Microsoft Azure for the first time, the cloud-computing solution for developers that is currently in development itself but will be rolling out in production next year. It is quite exciting, especially with the cloud-computing possibilities for my own company's applications.
• I saw Microsoft's Project Gemini for the first time, a new enhancement for Excel that will do for database querying what PivotTables did for spreadsheets. The presenter was one of the developers from Microsoft itself. He shows Excel pulling in millions (!) of rows of data and then analyzing it PivotTable fashion almost instantly. He showed how Gemini can correlate ad hoc data with query data. He showed how SQL Server Reporting Services reports can serve as a data source so that users can re-package their own reports based off of the underlying query that feeds the SSRS report. He blew me away. I can't wait for this feature in the next version of Excel. And I am curious as to whether Excel Server might be a useful tool for some of our customers. It is just so exciting what Gemini is going to bring in terms of user-level Business Intelligence options.

I can't wait for the next Code Camp to come along. I'll definitely be there.

To Be Continued

Have you ever wanted to print a "Continued Next Page" message at the bottom of a report? I see this most commonly for contribution statements, but it can be on any report that sometimes requires more than one page per person in the output.

This solution for ShelbyQUERY's Report Designer (which is a variety of Active Reports). It involves three control objects and a one-line VBScript. This solution assumes the following facts about your report:

• You are using GroupHeader1 as the "per page" grouping level. If you are using another Group level, substitute it for GroupHeader1 as needed.
• You have changed the NewPage property for GroupFooter1 to 2 - After.
• The PageFooter section remains as part of the design, and it is where the "Continued" message should print.

To begin, add the following three control objects to the PageFooter section, changing the properties to match those listed beneath each control:

1. a Bound Control
   1. (Name) = ctlCurrentPage
   2. SummaryGroup = GroupHeader1
   3. SummaryRunning = 1 - ddSRGroup
   4. SummaryType = 4 - ddSMPageCount
   5. Visible = False
2. a second Bound Control
   1. (Name) = ctlTotalPages
   2. SummaryGroup = GroupHeader1
   3. SummaryRunning = 0 - ddSRNone
   4. SummaryType = 4 - ddSMPageCount
   5. Visible = False
3. a Label
   1. (Name) = lblContinued
   2. Caption = Continued on Next Page

Put the "Continued" label wherever you want it to print. Because they will remain invisible on the print out, the two bound controls can be anywhere.

Open the Script Editor and change to the PageFooter object. Then change to the OnBeforePrint event. Because the PageCount value works like an aggregate value, this script must be placed in the OnBeforePrint section. Paste the following line of code in between the Sub and End Sub lines, as shown:

Sub OnBeforePrint

rpt.lblContinued.Visible = Not Eval("rpt.ctlCurrentPage.DataValue = rpt.ctlTotalPages.DataValue")

End Sub

The code checks to see if the current page number is equal to the last page number in the segment. If they are the same, the comparison will evaluate to True. If they are different (for any page before the last page), the comparison will evaluate to False. The "Not" reverses this value, so that the "visible" property of the label will be False for the last page of the segment and True for every other page.

In short, you will see that the "Continued" message appears whenever there is more than one page for a given GroupHeader1 segment, but it will never appear on the final page of the segment.

Calculating Age in T-SQL

Wow. October has just flown by. It has been so hectic that I haven't had a chance to post anything for the last few weeks. I will try to make up for that with a few quick posts this week. This first one will be about calculating a person's age based on date of birth information.

In Shelby Systems v.5 database a person's date of birth is stored in the Birthdate column of the NANames table. The examples for calculating age will assume that structure. In addition, the software allows for month/day entries that have no year value included. The datetime data type does not allow this, so those entries are stored with a year value of 1796. Hold that fact in the back of your mind. It will be addressed before the end of this post.

At first blush, the calculation of an age seems quite straightforward, especially if you are familiar with the DATEDIFF( ) function. This is the function used to calculate the difference between any two dates, in any unit of measurement from milliseconds to years. The following query suggests itself as the simple solution:

select

NameCounter = n.NameCounter,

Age = datediff(year, n.Birthdate, getdate())

from

Shelby.NANames as n

Unfortunately, this is not an adequate solution. The reason it fails is the fact that DATEDIFF( ) counts any part of a year as a whole year. Thus someone born on December 31st, 2008 would show up as 1 year old as of January 1, 2009. Obviously, this is incorrect. To correct this, we will need to build in some conditional logic to check whether the person's birthday has occurred yet. If it has not occurred, we need to subtract 1 before displaying the final result. First, let's check the month of the year. If the person's birth month has not yet occurred, we should subtract one.

select

NameCounter = n.NameCounter,

Age = datediff(year, n.Birthdate, getdate()) - case when month(n.Birthdate) > month(getdate()) then 1 else 0 end

from

Shelby.NANames as n

This is much better, but it still leaves open the problem of running this on someone's actual birth month. Then we need to check to see if the birth day has happened yet. This will fix that:

select

NameCounter = n.NameCounter,

Age = datediff(year, n.Birthdate, getdate()) - case when month(n.Birthdate) > month(getdate()) or (month(n.Birthdate) = month(getdate()) and day(n.Birthdate) > day(getdate())) then 1 else 0 end

from

Shelby.NANames as n

Great! So far so good, but what about those 1796 dates? Those people should not have an age showing, because we cannot calculate properly. That requires a new level of CASE logic:

select

NameCounter = n.NameCounter,

Age = case when year(n.Birthdate) = 1796 then null else datediff(year, n.Birthdate, getdate()) - case when month(n.Birthdate) > month(getdate()) or (month(n.Birthdate) = month(getdate()) and day(n.Birthdate) > day(getdate())) then 1 else 0 end end

from

Shelby.NANames as n

That will calculate the age to within a year. I'll have to save the calculation of ages in months for those who are less than a year old for another day.

OnFormat vs. OnBeforePrint in Active Reports

Recently I was asked to assist with an internal report for the Sales and Marketing Department. It might interest you to know that we use Shelby v.5 for our own data management purposes, just like our customers do. The report was put together in ShelbyQUERY and the Report Designer. The report was mostly working fine before I was looped into the project except that there were a few calculated values that were not working as expected. Specifically, the report needed some calculated variances of bound controls that had been aggregated using the SummaryType property of ddSMSubTotal.

In order to calculate the variances, I wrote a VBScript to pull the values for the aggregated Bound Controls in the GroupFooter1 section and to do the necessary math, returning the results of the calculation as the caption property of a label. I put the script to trigger during the OnFormat event, which is the only event I had ever used for Active Report scripts in the body of the document. The process seemed straightforward enough, except that the calculated values that were returned by the script were based on the last scalar value of the field, not the aggregated value.

I almost started down the path of creating script for the Detail section that would sum up the values by leveraging the caption of an invisible label as a place to hold a running sum. But before I did that, I took a step back and looked at other options. One of the options I researched was the event choice for when the script activates. I discovered that the OnBeforePrint event occurs right before the object values are committed to the canvas, but after the object values have been calculated.

That was the "Aha!" moment for me. I changed the script from occurring in the OnFormat event to the OnBeforePrint event. Voila'. The calculations came out based on the aggregate values, just as I wanted to happen. So I learned the following rules:

• Use OnFormat to work with bound controls before their values are aggregated.
• Use OnBeforePrint to work with bound controls after their values are aggregated.

If I am not working with aggregated values, I will still prefer the OnFormat event, if for no other reason than it is the default event option.

Incidentally, I also discovered in this process that the way to explicitly reference the value of a bound control is to use its DataValue property. In the past I have used the implicit reference by merely using the bound control's name to stand in for its value. The following two lines of code are functionally identical:

objValue = rpt.Sections("Detail").Controls.Item("Field1")

objValue = rpt.Sections("Detail").Controls.Item("Field1").DataValue

However, because using explicit references is generally better practice than using implicit ones, from this point forward I will give preference to including the DataValue property as part of any reference to the value of a bound control.

Enumerating Options using VBScript

ActiveReports is limited in the ways that a user can input parameters at run-time. At least it is limited in the way it has been implemented for Shelby v.5's ShelbyQUERY application. In fact the only way to capture a parameter at run-time is to use VBScript's InputBox() function. This is serviceable for many contexts, but it can be a problem when the parameter is a long text string, such as the name of an Event from the Registrations application or a company name from the General Ledger. In these cases the user must remember the exact wording of the text and then type it in without any mistakes in order to match the value in the query results exactly.

Error checking can alert the user that the value has been typed incorrectly, but it would be best to help the user along by prompting him or her with what the acceptable values are. In fact it would be ideal to give the user an enumerated list of options, so the user only has to choose the number of the desired value and enter one number instead of typing a long text string. The script below will do just that.

This script should be associated with the ActiveReports Document object and the OnReportStart event. That way it will pop up at run-time and provide a filter for the contents of the report.

After you paste the script into your VBScript editor window, be sure to update the value of the strColumnName variable so that it corresponds to the desired column in your actual query results. Also change the strPromptMessage and strPromptTitle variables so that the InputBox() prompt is worded appropriately for your report.

Sub OnReportStart

Set oDict = CreateObject("Scripting.Dictionary")

Set rsQuery = rpt.DataSource_Shelby_.RecordSet

If rpt.DataSource_Shelby_.RecordSet.RecordCount > 0 Then

intCounter = 1

strCurrItem = ""

strTestItem = ""

strColumnName = "QueryColumnName"

strPromptMessage = "Enter the prompt message here."

strPromptTitle = "Enter the Prompt Title here"

Do

strCurrItem = rsQuery(strColumnName)

If strCurrItem <> strTestItem Then

oDict.Add intCounter, strCurrItem

strList = strList & vbCR & intCounter & " - " & strCurrItem

strTestItem = strCurrItem

intCounter = intCounter + 1

End If

rsQuery.MoveNext

Loop Until rsQuery.EOF

Do

intChoice = InputBox(strPromptMessage & vbCr & strList, strPromptTitle)

If intChoice <> "" and IsNumeric(intChoice) Then

strItemChoice = oDict.Item(CInt(intChoice))

rsQuery.Filter = strColumnName & " = '" & strItemChoice & "'"

If rsQuery.RecordCount > 0 Then Exit Do Else rsQuery.Filter = ""

End If

If intChoice = "" Then Exit Do

Loop

rsQuery.MoveFirst

Else

MsgBox("There are no results to use for this report. Please check the query and try again after you have a set of results.")

End If

End Sub

With this code in your ActiveReports document, your end-user will have a much easier time filtering the report without have to double-check the spelling of a long text value and will avoid frustration of locating a tiny typo in the string.