Formatting data for reports seems like 90% of the work involved. Only show whole numbers, add a percent sign to this column, bold that, blah blah blah. Of particular fun is dealing with dates. Personally, I prefer to see dates in yyyy/MM/dd or dd/MM/yyyy format. It just makes more sense than the American format of MM/dd/YYYY.

Enough griping, in SSRS (Microsoft SQL Server Reporting Services) there are several ways you can format your dates. Take your pick:

Formatting in the Query

TSQL provides several choices for getting your date formatted:

CONVERT

TSQL has a built in function that will convert your dates to predefined formats. Unfortunately there aren’t enumerations to make it easy, you have to remember the magic numbers for the format you want, and I’m going to assume you want 4 digit years. If you don’t you can look it up in MSDN easily enough:

· CONVERT(VARCHAR(30), GETDATE(), 101) will produce “09/29/2009”

· CONVERT(VARCHAR(30), GETDATE(), 103) will produce “29/09/2009”

· CONVERT(VARCHAR(30), GETDATE(), 112) will produce “20090929”

There are several other formats, but these are the main ones you’ll want to use most of the time. However, perhaps you want different dividers or something. That brings up the other TSQL option.

DAY, MONTH, YEAR

The other TSQL option is to just concatenate the string yourself. To do that just call the DAY, MONTH and YEAR functions with the date you are working with:

SELECT YEAR(GETDATE()) + ‘-‘ + MONTH(GETDATE()) + ‘-‘ + DAY(GETDATE()) will produce “2009-09-29”

This approach provides the greatest flexibility but requires a bit more work than the built in CONVERT function.

Suppose, for whatever reason, you can’t do the work in the query. You are not limited to formatting in the report’s query. You can do formatting work in the report too.

Formatting in the Report

Once you have your report started you can right click in the fields and select properties (TextBox properties). In the General tab go to the value field and click the formula button. In there you can enter a Visual Basic expression to format your date. You can go pretty wild here and even call out to custom assemblies. However, for our purposes here we just need to format a date. We can use the FormatDateTime fuction. FormatDateTime has 5 formats available, but be aware that they use the regional settings on the server that is running the report. The format choices are:

· vbGeneralDate

· vbLongDate

· vbShortDate

· vbLongTime

· vbShortTime

Overall I think this approach to formatting a date is pretty useless b/c you are giving up control to a system setting that might not be set the way you expect. Imagine that you deploy the report, get everything setup the way you want and switch to something else. Three months later along comes a new sysadmin who decides he likes some other regional setting better (the reason isn’t relevant, the BOFH doesn’t need a reason). Now you get to spend a day trying to figure out why your report looks different because you won’t remember that the regional setting determines the format for your date. The solution? Don’t do that.

Build the date format using either string.format or plain old string concatenation:

· String.format(“{0}-{1}-{2}”, Year(Fields!DateCreated.Value), Month(Fields!DateCreated.Value), Day(Fields!DateCreated.Value))

· Year(Fields!DateCreated.Value) & “-“ & Month(Fields!DateCreated.Value) & “-“ & Day(Fields!DateCreated.Value)

· Or Now.ToString(“yyyy-MM-dd”)

Both achieve the same result so it is just a matter of style preference. I’d probably use the DateTime.ToString() approach because it provides the greatest degree of control.

Where to do the formatting

Sense you have a choice; where should you format data? I would suggest doing the formatting inside the report. That way you can reuse the query. Formatting tends to remove information and tends to be for specific purposes. If you can put that logic in the report itself, instead of the query, then you’ll have less work to do when asked to make another report using the exact same data.

Conclusion

Formatting a date really is difficult, but there are a number of choices available. Try to format in your report instead of the query and use DateTime.ToString to format the date to get exactly what you are after.

I have a bad habit. I’m constantly looking for an answer, a book, a guru, a leader, something that will let me in on the secret that all of you have been keeping hidden from me. Whether it is the key to how to program really well, go faster on my bike or start and run a business – I’m constantly searching for something.

Recently though it has been dawning on me that it is time to stop. There isn’t an answer to any of it. Sure there are small facts and little details that I have picked up along the way. This blog is full of that garbage. All of it can be learned easily through Google and a willingness to read and experiment with the information gathered.

In the realm of programming I’ve been long done with the search. I don’t know if I’m great, but there isn’t much that worries me when it comes to developing software. The other areas of my life are another matter. Particularly the desire to be an entrepreneur. I’ve read piles of books, most people who have read as much as I have at least have an MBA to show for it. Me, I’m still searching. Or at least I was. Two things just happened to me that have changed my mind.

First I attended a class on internet marketing. I learned a few things, but they were not necessarily what was on the agenda for the weekend. I ended up blowing off the second two days. From reading the work book this class was really aimed at people that had no idea how to operate WordPress, register a domain or use social media. Those are all things that I really don’t have any trouble with. I did figure out a few things about keyword research and how I use my blog that I will be doing differently. I also figured out, or they shouted it enough that it got through, that the problem isn’t “knowing what to do”, the problem is “doing it”. Plenty of people who want to lose weight know what to do, they just don’t do it. Same thing here, I know that the way to sell products and get cool lucrative consulting engagements is to advertise and market. I just don’t do those things.

The second thing that happened was a brief call to a person I thought would have answers for me. Or even better: “THE ANSWER”. Turns out he really doesn’t (great that it only took me 6 months to figure this out). It doesn’t mean he isn’t successful at what he does, he just doesn’t have the ability to transfer it to me. Plus, it is likely that what he is doing and what I am doing are too far apart to cross pollinate. Whatever it is, no hard feelings but I’m going to stop looking for answers.

Instead what I’m going to do is spend my time actually doing and making my own answers. There will probably be a lot of wrong answers that I’ll make. I’ll probably take a lot longer than some people would, but I’ve already been at this for nearly two years. What’s another year or two? The search for other people’s answers is really just an attempt to short cut the learning process.

Enough with trying to know it all, it is time to start doing it.

This one turned out to be really easy:

The WSS (Windows SharePoint Services) object model has a static object called SPContext that you can use to get the context that your control is working in.  For instance if you want to grab a list from the site, you would do the following:

            SPWeb thisWebSite = SPContext.Current.Web;
            SPList spContactList = thisWebSite.Lists["Contacts"];
            foreach(SPListItem contact in spConactList)
            {
                //do some work
            }

Pretty cool stuff. The more I get in to SharePoint’s API the more I’m liking it.

This is a bit outside my normal domain but still related. For nearly two years I’ve been reading about and learning marketing. In particular I’ve focused on Internet Marketing (IM here on). This coming weekend I’m even going to a 3 day workshop to see if I can move from passively learning to actively putting what I’ve learned in to action.

One of the things that has held me back is the marketing/advertising process for a business. One of the main tools that get talked about in IM is the sales page. If you click around enough you’ll run in to plenty of them. For me I know I’m on a sales page before it even finishes loading. Here is an example: http://www.musthavemarketing.com/traffic/

To be very clear, I’m not judging this page’s quality or the product it is selling. I’m just pointing out that it is a sales page and has all the standard features of a sales page:

· Bold headline fitting the usually copy writer’s formula for writing a head line (Reason-Why Headline)

· Short 3 – 5 sentence paragraphs with short (less than 20 word) sentences.

· Lots of white space

· Charts, graphics and other stuff you don’t really have to read

· Bolded section that draw you in

· Pictures of the product you’ll receive when you buy

The only thing missing is testimonials. You’ll usually find them on sales pages with a picture of the person. Sometimes for added authenticity they’ll make the testimonials appear to be hand written.

Set aside the veracity of the sales page. For the moment assume everything stated is true, and that you’ll receive all the benefits of the product when you buy it. The question I have is: will this page, or any sales page, all on its own be enough to cause you to buy a product?

Most of my friends are very technical like me and they believe they have the same ability to spot a sales pitch from a mile away. Like me, they are able to hit the back button before a sales page loads. We’re smart guys and can’t be suckered in by a sales pitch. Are you sure: http://www.red-gate.com/products/Exchange/index.htm . Picked purely at random. Has all the features of the above minus the copy writing skill. It even has a testimonial! Bold text! Actually, these guys are better at it. This page doesn’t cause the allergic reaction the other page causes. They’re a lot better: plenty of supporting video, case studies, you can even give them a call to ask questions. Good luck getting somebody to answer the phone at the other place.

I can find plenty of sale page examples to show. You can to. The key bit has to do with the skill invested in the sales page and the understanding of your audience. If you are selling to technical people you would do well to copy redgate’s pages. I’m not sure who the audience is for the other page, I can make some assumptions, but I think if you try to sell developer tools with a page like that you’ll not be well received.

Then again – that brings up another topic: split testing! But I’m out of time now.

A screencast from SEOMoz: Screencast Tuesday: A Walk Through the Basics of Applying Linkscape for SEO

I’m going through my Google Notebook cleaning things out.  Here is another good link: 'Better than a license to print money' -- Build your own tiny software company -- slide deck from TechEd Australia 2008

This is probably one of the most useful links I have:

Get Smart: 12 Free Educational Resources for Web Workers

The article is just a list of links to other resources, but they are useful resources and it makes finding what I need easy.

I wonder how many popular blogs have to deal with. 

I was running with comments just turned on, but the spammers came a calling.  So I switched over to require my approval.  Only problem with that is that I don’t really check very often.  Guess I need to write a tool to help.

I started a personal project a long time ago (early summer or so) that I had hoped to already be finished with. I initially setoff to use MOSS (Microsoft Office SharePoint Services), in particular I wanted to use Forms Server because it seemed to be a perfect fit for what I was doing.

I showed it to my partner and he seemed ok with it until I mentioned the price tag. He freaked. To a large degree I don’t blame him. Dropping $30,000 just for software is insane when you are used to using open source software and paying $9/hr for coding.

The result is that I basically stopped. Why? On one hand it meant that I had to start over from square one. On the other hand I was dealing with a fairly difficult client. Throw in family and some play time and basically the project got stuffed in the back corner.

Being a bit OCD though I couldn’t leave it alone so a few weeks ago I started again. This time I was going to just do it using straight ASP.NET and SQL Server. I did a bit of design, planned out the features and started coding. And then I really got depressed.

For the entire year I’ve been developing with CRM, SharePoint and BizTalk. Why on earth would I hand create a table to hold contacts? Never mind creating a web-page to view, edit, search, and etc. The more I tried to work on this the more I rebelled against it. This is stupid!

So over the weekend while enjoying the twisty roads of Northwest Arkansas I decided that I don’t need MOSS, WSS (Windows SharePoint Services) would do what I need. WSS is a free feature of Windows and can be hosted for only a little more than a normal ASP.NET account. I’ll still have to do some coding, but some really major pieces of functionality are provided for me right out of the box.

Being even smarter I’m also grabbing a few pieces from CodePlex that provide pieces of my application’s functionality readymade. In one case this is saving me a week worth of coding.

The point of this is that before writing a single line of code you really should look around first. For instance, by using WSS I’m getting a ‘free’ UI framework, security framework, a component management framework (you can switch WSS features on and off via the admin tool) and I’m sure a few other nice benefits. Grabbing an appropriately licensed bit of open source code also saves time, even though I have to give up some time learning the API and possibly debugging and testing their code. Thing is I’m way ahead of where I’d be otherwise.

Tonight I cleaned out the old version of my project from SharePoint and started over. In about 5 minutes I had all of the UI stuff in place (just created a site collection) which was way more than my ASP.NET project had. It took about 10 minutes to create most of the basic entities for the application using SharePoint lists. What remains is the core application which will still require some coding and an application database, but it’s a lot less than I’d have to do otherwise.