Link to home
Start Free TrialLog in
Avatar of Ross
RossFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SSRS report from dynamic SQL SP

When I run my SP from SQL Management Studio directly, I get the results I'd expect in the output window - a list of data with a varied number of columns provided by the code.

When I try and use this SP in SSRS to drive a report, I cannot add fields as the results (and column quantity) are obviously unknown.

What's the easiest solution to this?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ross

ASKER

Thanks - I have reverted to defined sets and used tablix to pivot the data in SSRS instead. Shame but... there we go :)

Cheers!

Ross
A possible workaround if your client insists on this crazy, funky, free-love mode of dynamic SQL, and isn't going to get wigged out about security,  is to serve it up using Power View, where the user can always refresh, and it doesn't require a specific 'contract' unless that set has relations to other sets, or if there is custom Excel VBA that depends on certain columns.

If you want to explore this further you can ask questions in both SQL Server and Excel zones.

Thanks for the grade.  Good luck with your project.  -Jim