I created a stored procedure that includes a variable for a name, and variables for start date and end date.If a name is passed in, the stored procedure ignores the start/end date values.If start/end date values are passed in, the name is ignored.The stored procedure works as expected.If I execute the stored procedure with just a name, the desired data appears.If I execute the stored procedure with just start/end dates, the desired data appears.My problem is with SSRS.I created a new report and pointed the dataset to the stored procedure.The variables are visible and can be NULL.When I preview the report and insert a name (but not the dates), the report works.When I preview the report and insert start/end dates (but not a name), no data appears.I verified valid start/end date values are being passed in, and checked the parameter values. They appear to be OK.I even changed the datatypes for the start/end dates.Again, it works in SMSS but not in SSRS.I have other reports that include start/end dates, and they work as expected.I checked the values of these reports against my newly created report, and everything appears to match.I even checked the code of the new report (sometimes an update in the GUI doesn't update the code).I tried to ensure nothing has been overlooked, but I just can't find the solution.To any SSRS gurus out there, any suggestions will be welcome.Best RegardsWendell BaskinBass Hall - Fort Worth
Wendell
I've fallen foul of this situation before, if I've understood you correctly. When you have an SSRS report with a stored procedure that has multiple pathways through producing different potential results, you have to make sure that each result set that could be produced by the SP have exactly the same columns in them, even if some of those columns are redundant. When you add the call to the SP as a datasource within SSRS it produces a blueprint of the output generated from the SP. If one of your alternative pathways deviates from that blueprint in any way, the report won't work when run in Tessitura.
I've even had to write some dummy code in an SP just returning the columns I need as NULLs to get the blueprint generated correctly at design time, and then swapped out the SP code for the real stuff after the SSRS has been compiled.
I hope this makes sense and helps you solve your problem.
Christine