I have one on my wagon, installed by the PO. He did a good job on the install. It's a standard after-market pop-up one, and in general, I agree with the people who say don't do it, because it probably will hurt the value of your car. How it looks is a personal opinion - sometimes I like it and other times I think it looks crappy.
On the other hand, I don't have any problems because of a weak roof, and no leaks. Plus it lets an astonsishing amount of heat out that would normally stay inside and make the car miserable in the summertime - very nice to have here in Phoenix. So while I probably wouldn't cut into a perfectly good roof to install one, I use it all the time.