Report Parameter

Question

Report Parameter

Susan S

SSCommitted

Points: 1771
More actions
May 10, 2006 at 11:21 pm

#168925

Hi Anyone,
Please help me.
I created a store Proc with 4 paramaters and user can either to fill 1 para or 2 para or 3 para or 4 para
Then I created a report with report wizard, in order to get through the wizard I put
EXEC "MyStoreProc" with hard code parameter then I modify again in DATA TAB.
I change the query type from a text command to a stored procedure
and remove the word EXEC and any hard-coded parameter values following the SP name.
I click on the Execute (!) button and see a window prompting me to enter parameter values. I have tried a few values in here to make sure it works. Everything is OK
Then my last step is connect Report parameters to Query parameters. I created dataset so user can choose the para value from the query list. I put the default value is null.
I click on preview to test and ask to select the para value so I fill 1 para value ( eg. Branch) BUT it keeps asking me to provide 2nd, 3rd and 4th para value too.
What should I do?
Which part I do wrong?
Below is my store proc
CREATE PROCEDURE " MyStoreProc "
(@Branch varchar(10)=null, @DivCode varchar(10)=null,
@HACCProj varchar(10)=null, @PrimZone varchar(10)=null)
AS
SELECT
CNo,CName,[Search Name],CFName
FROM 'My View"
WHERE
(@branch is null or Branch = @branch)
AND (@DivCode is null or DivCode = @DivCode)
AND (@HACCProj is null or HACCProj = @HACCProj)
AND (@PrimZone is null or PrimZone = @PrimZone)
Thanks,
Susan

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

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

While defining the report parameters set allow nulls to true and default value to NULL. Also check if you have bound your dataset stored proc input parameters to your report paramters properly under parameters tab.

Prasad Bhogadi
www.inforaise.com

Susan S SSCommitted Points: 1771 More actions · Answer 2

Thanks Prasad.

Ok I have set:

my 1^st param and give the dropdown list so user can choose.
my 2^nd, 3^rd and 4^th param set to null

when I preview it. The other 3 params have a Null checkbox so if I untick this box it will allow me to fill it.

However I want this param also have a dropdown list instead of manually typing

If I use ‘Available value from query’. It will go back to my previous problem that this 2^nd params become a mandatory field.

How do I do this?

Thanks again

Raj Deep Alapati Ten Centuries Points: 1118 More actions · Answer 3

Give like this

for listing the values in the dataset

Select null your_column from your_table

union

select yourcolumn your_column from your_table

it wont ask for null

as now null is the part of the data provided by the query

Thank you

Raj Deep.A

Susan S SSCommitted Points: 1771 More actions · Answer 4

Thanks so much.

It works fine now.

actually there is a walkthrough for this for dynamic query listing

cheers,

S