I got it working.

The proc uses dynamic sql based on parameter values from the report. SQL gets stored in a variable and then that sql is executed.

I modified the dynamic sql to insert the data into a global temp table and return the result set from that table.

This works great.

Running it in SSMS without the temp table resulted in the 'Commad completed successfully' message.

Running it in SSMS with the temp table gives me column headers with no rows. I guess that's the only way the report can understand there is no data where essentially the 'Commad completed successfully' message constitutes data?

Not sure but it is working so...