I always thought Picard was seen as failing in the Borg encounter not saving earth. We see the whole thing play out, but the perception in the federation seemed more dim.
In First Contact they don't trust him to fight the Borg. He gets offered a job by his old friend like it is assumed his starfleet career is over. Sisko is disgusted with him (and I assume plenty of others who lost loved ones).
If anybody gets credit, I think it is Riker. Who the admiral in that episode (I think) specifically thanks. I always thought that was why Riker went back to being commander after his field promotion and what the federation viewed as his success and didn't keep getting offered commands. First, they had just lost a bunch of ships, but second, they lost a little faith in Picard (admittedly unfairly) and wanted Riker as the fail safe (I admit that last part is just my own conjecture).
Anyway, to the first point, I thought that was why Robert was hostile. Picard was this great captain, admired and thought of as a great success (which obviously he is) and he ignores his family. Then his fortune reverses and all of a sudden he is taken down in the perception of folks. Then at his lowest, he returns home. It's like insult to injury to Robert to have the great Picard come looking for answers now that he is low, when he didn't try to see his family when he was happy at the height of his success.
Sorry that was so long. Hope my argument made some sense. Also, this sounds like I'm dissing Picard, but I more just always took it to be that he lost stature within the Federation.