I've used WMM 2, PhotoStory3, PSE 4.0 slide show program, and Proshow Gold. You can get some cool FX for WMM2 from a German company,
http://www.adorage.de/gb/index.html, for about $15 (10 months ago). There are also a few web sites that have user-developed FX...here are a couple:
http://www.hitthebongo.co.uk/videofx/ http://www.papajohn.org/ (also has some info on Photo Story 3)
PS3 also has a neat little music creation program that allows you to avoid copyright issues.
WMM2, for me, was the best tool to cut teeth on when learning to do slide shows (video clips in WMV format).
I currently use ProShow Gold to create video clips for my church. And now, will use it for wedding and senior photo gigs (recently got back into snapping pics). But, do wish it had a few more bells and whistles...and wished it used the MainConcept Codec used by Sony Vegas Movie Studio.
ProShow Gold is a great program. Very easy to create DVDs and a PCAutorun CD of your slide show. However, I'm not real pleased with its audio editing tool. I prefer Audacity or Sony Acid for audio editing. Create your video in PSG (render low resolution, MPEG1)...load the MPEG1 file into video editor...add audio, slice/dice audio in audacity/Sony Acid/ then sync your audio in the video editing tool (I use Sony Vegas Movie Studio) that allows multiple channel audio editing, so on and so forth...then render the audio as a WAV file and upload to PSG. Then render your PSG work in hi quality. Works great.