Pulling From and To Parameters from Stored Procedure

Posted on 2011-05-11
Last Modified: 2012-05-11

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?

Question by:apitech
    LVL 16

    Accepted Solution

    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
    LVL 1

    Author Comment

    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.
    LVL 16

    Expert Comment

    That's the way it is ...
    LVL 41

    Expert Comment

    you can also try with Openrowset in your query there. Something like
    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

    LVL 1

    Expert Comment

    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.




    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now