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

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  May 11, 2006 at 12:56 am

  #637160

  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

  May 11, 2006 at 8:07 pm

  #637428

  Thanks Prasad.

  Ok I have set:

  1. my 1^st param and give the dropdown list so user can choose.
  2. 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

  May 12, 2006 at 4:18 am

  #637486

  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

  May 15, 2006 at 4:27 pm

  #638031

  Thanks so much.

  It works fine now.

  actually there is a walkthrough for this for dynamic query listing

   

  cheers,

  S