Parametered stored proc as data source

Question

Post reply

Parametered stored proc as data source

evaleah

Ten Centuries

Points: 1372
More actions
June 30, 2005 at 12:31 pm

#109987

Ok, this may turn out to be something I can't do either but I figure it is worth a shot....
I have a sproc that I want to use as my datasource. However, the sproc calls for parameters. Right now the report is rendering nicely with the defaults but I want to be able to have users set those parameters to different values. Is there a way to do this?
I tried setting my dataset to be of type text and putting:
DECLARE @rc int
DECLARE @date datetime
DECLARE @int int
DECLARE @bigint bigint
-- Set parameter values
EXEC @rc = [SCC Admin].[dbo].[spMyReport] @date, @int, @bigint
but I am not getting any prompting for parameters.
So, bottom line, is there a way to do this?
Eva

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ray McCoppin SSC Journeyman Points: 76 More actions · Answer 1

Try just this:

EXEC [SCC Admin].[dbo].[spMyReport] @date, @int, @bigint

as the recordset.

@date, @Int, @bigint will now be parmaters for the report.

sergioman SSC Enthusiast Points: 166 More actions · Answer 2

Instead of selecting "Text" as the command type, select "Stored Procedure". Click OK and execute the dataset. You will be prompted for the parameters and new parameters will be created under report parameters. When you run the report, you will be prompted for parameter values. See examples.

evaleah Ten Centuries Points: 1372 More actions · Answer 3

hmm... Should have been a bit more specific.

I actually tried both of your suggestions and they work nicely. My real issue is that I want the parameters to be nullable. In theory all I have to do is mark the parameters as "allow null" but then when I render the report in Report Manager it requires there to be a value in my drop down option boxes before it will run the report.

Am I missing something there?

Thanks!

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

I think I hit something like this previously. Form memory the issue relates to the fact that your using a drop down list (ie multiple values return from a query) for the paramter option/s. If you did the same thing but without parameter values (ie it just presents a textbox to the report user/consumer) then the nullable option is presented also. Possibly you could try adding a null with an appropriate description (posibly '<all>', really depends on your usage) to the list of returned paramter values. We usually do something similar for adding an 'All' option to paramter lists, nearly always using a union ie <select -1, 'All'> UNION ALL <other select statement>.

Steve.

evaleah Ten Centuries Points: 1372 More actions · Answer 5

Thanks Steve! That was it.

I unioned my drop down datasets to include an All option. Then I set the All option to the default. Works like a charm.

Thanks for the creative option.

Eva

vasanti mestri Right there with Babe Points: 786 More actions · Answer 6

Hi,

Can you expain it briefly whatever you done in Union clause?pls it will helpful for me too much.