A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Thursday, October 22, 2009

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.

Tuesday, October 20, 2009

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.

Monday, October 19, 2009

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.

Followers