I love taking data and making it useful for people. One of the mini projects I’ve been working on over the past year is taking our SQL Server data and turning it into useful dashboards in Sharepoint for our Project Managers to analyze their projects. I’ve written several posts on things I’ve learned about Sharepoint to do that, but I have not discussed some of the things I’ve learned about working with SQL Server.
One of the things a lot of people don’t realize about data is that it’s very black and white with really no grey areas. Human beings can see things that computers can’t. A human can see a blank space as a zero. A computer sees a blank space as NULL. What’s null? Well, to a computer, null is tantamount to falling into a bottomless pit. There is no end, only black space. This is a great example of how humans are smarter than machines! Preventing the nulls in the first place would be a sign of true smarts, so if you’re running into null problems, check your column settings in Sharepoint and/or SQL Server to make sure that you don’t allow nulls and maybe to set a default value of 0. But that won’t solve the nulls that are already there or maybe you don’t have access to the column set up. In that case, you’ll need a way for the computer to understand your nulls.
I did some trial and error and some looking around on the internet, but what I came up with was pretty cool. Instead of trying to pretend a null wasn’t a gaping void, I just use this: ISNULL(dbo.tTableName.ColumnName, 0) to tell it to change any null it finds to a zero. It works beautifully – now all my calculations come out with numbers instead of complaints!
Next on my list as a pesky sort order problem. Computers don’t know how to read digits unless you tell them explicitly. I had set up a column that was coming in to Sharepoint from SQL Server that listed the value of “Month” by number to work in descending order. It turned out that this meant my column was being sorted from left to right by the digits. Much to my dismay, they were coming out like this:
9
8
7
6
5
4
3
2
1
10
11
12
I puzzled over this one for a while and tried a few things, but it turned out that what I really needed to do was to change the “type” so that I could add a leading zero (if the type is “number” then leading zeros get dropped automatically), and limit the characters to 2 from the right so that the double digit numbers wouldn’t lose the second digit. This did the trick: RIGHT (’0′ + CONVERT (varchar, DATEPART(mm, DateColumnName)), 2). Now my results look like this:
12
11
10
09
08
07
06
05
04
03
02
01
These two little phrases made my months turn up in order and banished the nulls. I hope they might come in handy for you too! Data can never be too tidy or well-behaved.
