We just stayed at the Nick hotel after our 4 day cruise. It was a big hit! I don't think I would just go there for one night, though. You really need to be there a few days to be able to take advantage of all of the activities and to get your moneys worth.
The rooms are family suites, there are a few different sizes, and are great with kids. We had the 2 bedroom suite, our kids had their own room with twin beds. It was great in the evening to have the living room area to ourselves while they were asleep. I think the price was very fair considering all that was included. The 2 pool/waterslide areas are really nice, and have great areas for the smaller kids (I have a 2 year old and she loved the small waterslides and fountains). The slime thing was pretty cool, as long as you were watching from afar. LOL The characters were very accessible and there were never any lines. Such a change from Disney! I know it is a different thing, but my kids actually like the Nick characters. We saw SpongeBob, Dora, Blue, and Patrick all within about 20 minutes of each other and had them mostly to ourselves. There were also quite a few scheduled activities and shows. There were also a few crafts (some that have an extra charge) that were really cute, my son made a tshirt with the characters. There was always something to do.
I will say the food isn't all that great and is pricey, thank goodness my kids ate for free (5 and under). The buffets were small and boring, and even eating off the menu was just OK. We did eat there most of the time as we just wanted to park it for a few days, but we did venture out to eat one night. There is also a food court with subway, a&w, and a few others, so you won't go hungry.
Anyway, I do think it is worth a stay if your kids like the Nick stuff and you have a few days to spend. It wasn't much more than staying at a moderate Disney hotel, and we had a big suite. NTM the activities and shows that are included.
As for transportation, we always rent a car. Much cheaper than the other options, and we like to have our own schedule.