locked
Problem with multiple datasets calling the same SP on one report RRS feed

  • Question

  • User625999351 posted

    I'm creating a report in VS2010 SP1.  I'm a bit rusty with this sort of thing.

    I have two pairs of datasets that each call the same procedure (one proc for 2 datasets) and each return a single value field with the same name.  The report as a whole has datetime parameters, startdate and enddate.  Each stored proc call takes the same parameters.  Textboxes are created on the report by dragging the fields from the dataset onto the report without modification.

    The second data set takes those same parameters but recalculates them for the call to the stored proc.  For example: =DateAdd(DateInterval.Year, -1, Parameters!startDate.Value)  I am reasonably certain this technique is working since I tested this by puting the recalculated parameters themselves on the screen.

    My question is this:  Is there anything fundamentally wrong with this technique?  I ask because, up until a few minutes ago, one dataset with recalculated parameters was working as expected and the other was failing.  Now as write this, the second one has suddenly started behaving itself as well.  (All of this testing has been in Preview mode.)

    Suggestions?  Potential pitfalls?  Or am I just having a bad day?

     

     

    Tuesday, September 24, 2013 10:30 AM

All replies

  • User-734925760 posted

    Hi,

    According to your description, we want to call the same procedure for the report datasource, but the procedure will return two datasets.

    But as I serach it is not possible. Allow a report in reporting services to use all result sets returned from a stored procedure. Currently only the first result set can be included on the report.

    For more information, please refer to the link below:

    http://connect.microsoft.com/SQLServer/feedback/details/125454/reporting-services-recognize-multiple-result-sets-returned-from-a-stored-procedure

    I suggest to use two procedures, then we can merge the two datasets.

    For more information about merging the DataSet, please refer to the link below:

    http://stackoverflow.com/questions/5313935/how-do-i-store-multiple-results-from-a-stored-procedure-into-a-dataset

    Also we can use union all in the procedure for the two query.

    For more information, please refer to the link below:

    http://stackoverflow.com/questions/9310736/multiple-datasets-from-stored-procedure-in-ssrs

     

    Hope it’s useful for you.

    Best Regards,

    Michelle Ge

    Wednesday, September 25, 2013 3:47 AM
  • User625999351 posted

    Sorry but I think you misunderstood me.  

    I have 4 datasets in that one report.  Each dataset calls a stored proc and gets back one resultset.  

    It's just that 2 of the datasets call one stored proc and 2 call the other, but independently of each other.

    Wednesday, September 25, 2013 1:52 PM
  • User488649576 posted

    It's just that 2 of the datasets call one stored proc and 2 call the other, but independently of each other.

    I'm not understand your descrption, I want to make sure that, in a procedure, if we will have two select query, for example:

    Select * from Table1

    Select * from Table2

    If yes, I think you should union all the query.

    Friday, September 27, 2013 5:10 AM
  • User-830595639 posted

    Hi,

    As per you explanation I don't think there should be any problem, but I didn't understand the problem you are facing. Is that you are getting the same value from both procedure or it is giving you some error, please let me know then I might be able to help you. I hope that in your text box you must specified the scope.

    Monday, September 30, 2013 1:42 AM