• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Pulling From and To Parameters from Stored Procedure

Hello:

I have a SQL Reporting Services 2008 report that pulls data from a stored procedure.

I want to add a simple set of "From" and "To" parameters in the report itself, based on one of the fields from the stored procedure.  (It's the Customer ID field.)

This is pretty simple to do, when your report is based on a table or a view.  But, I'm finding this difficult for a report that is based solely on a stored procedure.

For one thing, when I try to create a new dataset for the parameters based on customer ID, I can't say "select distinct customerid from 'the name of the stored procedure'" order by customerid.  When I try this, the app barks at me claiming "invalid object name".

I don't want to put the parameter in the stored procedure.  I just want to put a simple from and to parameter, in the report.  I just don't know how to do it, since I cannot say "select customerid from 'the name of the stored procedure'".

How do I get this very simple task accomplished?

Apitech
0
apitech
Asked:
apitech
1 Solution
 
vdr1620Commented:
you might need to write a sql to create a temp table and then insert the values into temp table and then select from temp table

Or try converting the Stored procedure to function

http://blog.sqlauthority.com/2009/09/23/sql-server-insert-values-of-stored-procedure-in-table-use-table-valued-function/
0
 
apitechAuthor Commented:
Shoot!  There's no way simply in reporting services to do this?  That's disappointing.

It seems like SQL Reporting Services is not much more than writing code in the SQL Management Studio.  If you have to do all of your programming there, anyway, what's the benefit of using the Business Intelligence Development Studio?  Heck, you could just have your programming reside in the SQL Managment Studio and give the use user a secure app to query the database itself.  No sense in monkeying around with the Business Intelligence Development Studio.

Just not seeing any value in using the Business Intelligence Development Studio, if SQL Reporting Services amounts to nothing but database programming that you have to do in the SQL Management Studio anyway.

Again, very disappointing.
0
 
vdr1620Commented:
That's the way it is ...
0
 
ralmadaCommented:
you can also try with Openrowset in your query there. Something like

http://msdn.microsoft.com/en-us/library/ms190312.aspx
select distinct customerid 
from OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=YourServerName; UID=username; PWD=PASSWORD',  'Exec yourstoredprocedure') AS a
order by customerid

Open in new window

0
 
MadRacer1Commented:
Hey Apitech,

There are multiple ways to do thew same.
One of the way is to add filter at report level. i.e. at table/ graph.

You can add filter to table for your specific condition.
So that report while loading fetches only those data which is satisfying your condition.
But in this case, flow will be like as, Dataset will fetch data for all. and after that while displaying you will be displaying selective data only as per business scenario.

You can not access stored procedures data selectively. If needed either you have to create another stored procedure.
1. Same procedure updated with your parameter
2. Create another procedure which will create #table, insert result-set in #table. Apply and filter your business logic and return that expected result-set. and at last delete that #table.

If you don't want to do all these lengthy and tough execution / calculation, then you can go for first option which is pretty straight forward. and don't have complex execution.



 

 




0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now