Reporting Services 2005 and Stored Procedures

I am a complete Newbie with RS2005 and had some questions if anyone would be kind enough to answer:
Can I use Stored Procedures in my report? I have a lot of calculated fields that need to go on the reportWould it also be easy enough to display graphs along with the data from the results obtained from the stored procedures?

Hello, in response to your questions...

Yes, you can use stored procedures, and it's highly recommended. When you create a DataSet, you simply set the command type to 'Stored Procedure', and then put the stored procedure's name in as the query string.

And yes again, charts are an integrated part of RS 2005. If you are developing your reports in Visual Studio, you can drag and drop a chart control form the toolbox, and then drag and drop the data fields you wish to use onto the appropriate sections of your chart from your DataSets window.

Hope this helps - Dan

Reporting Services + Analysis services Data Source

Hi all,

Till now all the reports for our application was created in Reporting Services 2005 with SQL Server Database(OLTP) stored procedures.

Most of the reports take 10 to 15 report Parameters and all are multi valued.

Now because of the slow response time,we have decided to use OLAP cubes data source. We have created the OLAP cube in Microsoft Analysis Services.

Can someone tell me how to use the report parameters to query the Cube.

Any links,books for reference would be of great help..

Thanks,

Looking forward to your replies

Hi there,

Try searching http://blogs.msdn.com.

Here is a link on how to use parameters.

http://www.databasejournal.com/features/mssql/article.php/10894_3504651_11

and here

http://blogs.msdn.com/bwelcker/archive/2005/04/24/411588.aspx

cheers,

Andrew

|||Thank you very much for replying.. i am looking at these sites.

Reporting On Sproc Update Dates?

Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?

In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.

Thanks!

Wow, already off the first page so quickly?

Sorry for the bump, I just wanted to be sure there was no quick answer to this. I would have thought it would be an easy thing to do, I may be mistaken.

Any takers?

Thanks again.

|||

You can get any object now using the sys.objects view. For sprocs, you'd want something like this:

Code Snippet

select * from sys.objects where type='P' and modify_date between getdate() - 1000 and getdate()

-Jessica

|||

Perfect! I knew it must be something simple I was missing.

Thanks, Jessica!

Reporting On Sproc Update Dates?

Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?

In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.

Thanks!

Wow, already off the first page so quickly?

Sorry for the bump, I just wanted to be sure there was no quick answer to this. I would have thought it would be an easy thing to do, I may be mistaken.

Any takers?

Thanks again.

|||

You can get any object now using the sys.objects view. For sprocs, you'd want something like this:

Code Snippet

select * from sys.objects where type='P' and modify_date between getdate() - 1000 and getdate()

-Jessica

|||

Perfect! I knew it must be something simple I was missing.

Thanks, Jessica!

Reporting off of stored procedures

Hi, I'm new to SQL Reporting Services. I want to create a report from a dataset where the source is a stored procedure. The sp does several things to generate the ultimate reporting rows; temp table(s) are created and updated, joined with other tables and views, etc. The final line of the sp is: select * from #temptable. I'm getting an error that the temp table is an invalid object. Also, this sp used to have input parameters which caused errors also, so I just hard coded values to get past that. Any ideas here? Thanks, Marta.

You might want to put the data you are ultimately querying in a permanent table, not a temp table. It may be that when you return the cursor, the temp table is no longer available and when the report is going through the cursor and trying to get records, the data is not available anymore.

|||

Hi,

The problem must be elsewhere. I am doing this without problem.

Did you set the report dataset to type StoredProcedure?

Did you try to call your sp from a query window?

Did you create the datasource as to be of type Microsoft SQL Server?

Also, I do not like the " Select * " try to specify the columns if you can.

As Jayplus said, a permanent table would be better (if the data will remain unchanged for a while).

Philippe