Post reply

Stored Procedures as dataset sources in reporting services

  • June 15, 2009 at 2:42 am

    #206262

    I've been tasked with creating a cost model in SQL, the model is fairly complex, (around 500 lines of SQL).

    The results of the model will be displayed on various report pages in both graphs and grid view objects.

    The issue I have is that I want to return both, datasets for the gridviews/graphs and scalar values from text boxes from one stored procedure, using several stored procedures works but means lots more code re-use and server load. Is it possible to do this?

    I think not as it seems that the Business Dev Studio wizards that create the RDL files can't differentiate between input paramaters and output ones.

    Is there a way around this.

    Thanks in advance

    Alex

  • June 16, 2009 at 6:25 am

    #1010118

    I've not had any luck with this either, aside from

    creating say, 2 stored procs...1 for the dataset and 1

    for the several values.

    I"d love to know if this is possible to do as it would reduce

    duplicate code/logic.

    Thanks.

  • June 16, 2009 at 2:58 pm

    #1010506

    [font="Verdana"]The simple answer is: you can't work with multiple result sets returned from the same stored procedure (yes, I've tried it too.)

    You can have a parameter to the stored procedure that tells it which result set to output. A better option is to create one result set that contains all of the data that you need for both, and then to display some of it in the table and some of it in the graph.

    You can use the same result set in multiple places in reporting services. So set your (reporting services) table to use the result set, and the graph to use the same result set.

    [/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply