Not to get picky, I think you meant Rogue Leader, not Rogue Squadron...I guess I was the only Nintendo fan with the foresight to not buy Luigi's Mansion and buy Rogue Leader instead.
FF:CC is a bit of an oddity, though, I'll give you that. I don't know if the idea was foolish, but maybe the way it was presented and perceived was. As Penny Arcade pointed out, with the GBA selling much more than four times more than the GameCube, you'd be more likely to find someone with a GBA than a GameCube. It shouldn't've been "who has some GBAs so we can play FF:CC?" but more like "who has a GameCube so we can play FF:CC?" It's still an extra hardware requirement for any of those who only have one or the other, true. How do you market a console game to those who like handhelds? How do you market a console game that requires a handheld? I personally play console and handheld games (and also PC games), but I guess I'm an oddity - those are apparently different markets and a game like FF:CC apparently wasn't enough to bridge that segregation and get different kinds of gamers to try it out. I think bearing the Final Fantasy name there were some expectations (even though FF has had its fair share of spin-offs before that). I still don't think it's foolish on its own - nothing is foolish if it sells. And it just didn't hit it off.
At least with DS-Revolution connectivity, you won't need a cable, and that would remove one physical barrier to connectivity. Assuming Nintendo will try to keep going with it. If it actually works in a game, or more like, if enough people are even willing to try it out, it is indeed something Nintendo has that the competition doesn't. I haven't heard of PSP-PS3 connectivity.
Enough about that, though. I'm sure Nintendo would try really hard in many other ways to distinguish themselves from the competition, and I'm sure Sony/Microsoft fanboys are simply going to say they tried too hard. I'm sure the majority just don't get it, seeing as how most people that have actually used the device liked it. Or maybe they're waiting to see it in play in actual game situations. That's one thing that simply has to be waited for.
To answer the question about what gamers want in the next Nintendo system? I'm guessing Nintendo gamers want more of the same of the good stuff (their favourite franchises continuing while avoiding staleness) and I think all gamers in general would want Nintendo to deliver on their promise of new gameplay experiences.