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.

Padding Numbers with Leading Zeroes

In some contexts a number needs to be padded in order to look right or to work in the desired context. For example, in the Shelby v5 software, the Company Number and Fund Number values are always displayed as three-digit or four-digit values, with padded zeroes for one- and two-digit vlaues. Thus, company "1" is displayed as either "001" or "0001."

Another example from Shelby v5 is when the NameCounter is used to create the path for an individual picture. Pictures are stored with a file name that includes seven digits for the NameCounter value, so the number is padded with however many zeroes are needed to make seven digits.

To show you how each of these situations can be handled, I will do some sample queries on the Shelby v5 table called CNHst. This table contains contribution history, but that is incidental to this exercise. I am using this table because it contains both a CoNu (company number) column and a NameCounter column.

To start with, here is a basic query to pull the values we eventually want to pad with leading zeroes. An example result set appears underneath.

select

CoNu,

NameCounter

from

Shelby.CNHst

The original values are numeric, and therefore they will always show up with only the digits in the number itself. We cannot "pad" numeric values, so the first step is to change the numeric values into character values, using the CAST() function.

select

CoNu = cast(CoNu as varchar(4)),

NameCounter = cast(NameCounter as varchar(7))

from

Shelby.CNHst

The only visible change is that the values are now aligned to the left of each cell instead of aligned to the right. However, now that the values have been changed into character values, we can add zeroes to the left by a simple concatenation of a literal text string of zeroes.

select

CoNu = '000' + cast(CoNu as varchar(4)),

NameCounter = '000000' + cast(NameCounter as varchar(7))

from

Shelby.CNHst

To make each row the same fixed length of four digits (for CoNu) and seven digits (for NameCounter), we need to cut off the extra zeroes. The best way to do this in T-SQL is to use the RIGHT() function to pick up the string of characters starting with the rightmost character and counting left the desired number of digits.

select

CoNu = right('000' + cast(CoNu as varchar(4)), 4),

NameCounter = right('000000' + cast(NameCounter as varchar(7)), 7)

from

Shelby.CNHst

Now we have uniform output with each number reflected as desired with a fixed number of digits and padded zeroes as needed.

Counting Rows

I was travelling last week and didn't get a chance to post, so I'm going to post a "bonus" message today to make up for it. In fact, it was last week when I got an issue of the SQLServerCentral.com newsletter with an article that caught my eye. It was entitled How to Get Table Row Counts Quickly and Painlessly by Kendal Van Dyke. The article points out that the total row counts of all the tables in a database are stored in system tables that may be queried.

I am always interested in getting useful information out of the system tables, especially when it can provide a new perspective on the database itself. If you read my earlier blog entry on finding all the tables that contain particular column names, you probably know that already. The technique in the article is a way to look at all the row counts of all the tables in a list, which could be a great guage to the overall "size" of the database, and it could give insight to sluggish queries, if a table happened to be much larger than expected.

Of course it would be possible to write the simple query to count the rows of one table at a time, like this:

select count(*) from Shelby.NANames

This approach is valid, and it is still the only way to do a count on rows that meet particular criteria, because you can add a WHERE clause to limit the rows. The system table approach always yields the total number of rows, with no such filtering possible.

Nevertheless, getting an overview of the database rows from the system tables can still yield interesting, if not essential, information. Here is the code from Kendal's article, though I encourage you to sign up with sqlservercentral.com and read it for yourself as well.

-- Shows all user tables and row counts for the current database

-- Remove is_ms_shipped = 0 check to include system objects

-- i.index_id < 2 indicates clustered index (1) or hash table (0)

SELECT o.name,

ddps.row_count

FROM sys.indexes AS i

INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id

WHERE i.index_id < 2

AND o.is_ms_shipped = 0

ORDER BY o.NAME

For Shelby v.5 users, this code can run straight from ShelbyQUERY, so there is no need to have any special querying software running to check out the database. Try it, and take a peek "behind the curtain" of the database.