Stored Proc with SQL Reporting

  • Is it possible to pass a parameter from a stored proc into a data set that runs a report in SQL Reporting Services?

    I have a stored proc that generates new tables for expenses each month, and I would like then to be able to pass the month into the Reporting Services to generate and email the reports as needed. Can this be done? I haven't been able to find anything about it yet...

    Thanks in advance!

     

  • I am relatively new to SSRS myself, having written only about 30 reports. I had need for similar functionality and our SSRS guru said that this could be done with SQL 2005 SSRS and we are using SQL2K. My workaround was to default the parameter to the current month, which I obtained through a dataset having the following query:

    select convert(datetime,cast(cast(YEAR(GETDATE()) as varchar(4)) + '-' + cast(MONTH(GETDATE()) as varchar(2)) + '-01' as datetime),101) as FirstDate,

    convert(datetime,dateadd(dd,-1, dateadd(mm,1,convert(datetime,cast(cast(YEAR(GETDATE()) as varchar(4)) + '-' + cast(MONTH(GETDATE()) as varchar(2)) + '-01' as datetime),101))),101) as LastDate

  • You have to do it by creating subscriptions.

     

    Give this a read, it may be the ticket.

    http://www.databasejournal.com/features/mssql/article.php/3370191

  • I am new to reports, so I guess I can't make the connection - I don't see how creating a file share and data driven report lets me pass a variable from a stored procedure, but for now, in the stored procedure, I have just added a new step, placing this months data into a table that will hold the information until the proc is run again. From this table it will then be placed in the appropriate 'month' table, so my report data set doesn't have to look for the month.

    Thanks for your help

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

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