December 21, 2015 at 10:51 am
Hi there,
Is there a way to get a SSRS report with a single stored procedure that returns two datasets which can be controlled via a flag. I am trying to write a report that passes a flag to the stored procedure and based on that flag, it returns one of two possible outputs but it doesnt work as it always returns the fields for the first output no matter what value I pass to the bit flag.
This is happening at the dataset level when i try to refresh the fields in the SSRS report UI. Is this possible or is there a better way to accomplish this?
Thanks!
R.
December 22, 2015 at 2:40 am
Hi Renzo,
If the fields of the returns datasets are not identical it will not be possible. SSRS cannot dynamically generate a dataset.
The reason it returns the first dataset when you click refresh fields in the Dataset Properties is because SSRS uses the SET FMTONLY ON flag. This is to return the dataset structure without data. However, this flag causes SQLServer to pass through all your conditions and flow (IFs etc) as if the conditions evaluate to True every time.
You can explicitly add SET FMTONLY OFF into the start of your sproc and it will evaluate correctly however you will have problems if the dataset structures returned are different.
Your best bet is two datasets with flags, and two tablixes with visibility set by the flag.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply