Pass parameter to procedure

Question

Pass parameter to procedure

Vandy

SSCommitted

Points: 1827
More actions
September 15, 2006 at 7:10 am

#153187

Hi All
I am using SSRS 2000 and VB.Net2003.In my table i have credit & debit entries.I want to make one report which shows one drop down selection box with three entries :- 'Debit', 'Credit','Zero Amount' & One for Date Selection & want to pass this selection to my procedure.I want to set this one num parameter like this in report parameter property means:-
Lable        Value
Debit          2
Credit          3
ZeroAmount 1
so that on report it shows drop down list and user can select one value and value pass to procedure.I am trying to use procedure in reporting service first time so may be i am wrong so please correct me. Here is my procedure:-
Create procedure Daily_Post @SelectDate int, @Num int as
Set Nocount on
If @Num=1
begin
SELECT     CustomerName, Customer AS Account, Goodsamount AS NetValue, VATvalue AS VAT, Postingamount AS GrandTotal,
                      Ledgerdocumentreference AS InvoiceNo
FROM         DailyPost
WHERE      (Postingamount =0) and atecreated=@SelectDate">Datecreated=@SelectDate
end
else If @Num=2
Begin
SELECT     CustomerName, Customer AS Account, Goodsamount AS NetValue, VATvalue AS VAT, Postingamount AS GrandTotal,
                      Ledgerdocumentreference AS InvoiceNo,DateCreated
FROM         DailyPost
WHERE     (Postingamount >0)and atecreated=@SelectDate">Datecreated=@SelectDate
end
else If @Num=3
Begin
SELECT     CustomerName, Customer AS Account, Goodsamount AS NetValue, VATvalue AS VAT, Postingamount AS GrandTotal,
                      Ledgerdocumentreference AS InvoiceNo
FROM         DailyPost
WHERE      (Postingamount <0)and atecreated=@SelectDate">Datecreated=@SelectDate
end
GO
regards

Viewing 3 posts - 1 through 2 (of 2 total)

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

Stuart Johnson Mr or Mrs. 500 Points: 532 More actions · Answer 1

This should work for you. Put the procedure name in the datasource and change the command type to Stored Procedure. The system should then detect the parameters and give you boxes for entering the parameters for the report. Then in the design go to the Report selection and select Report Parameters. This will allow you to enter the parameters as a list.

Stuart

Vandy SSCommitted Points: 1827 More actions · Answer 2

Thanks a Lot Stuart

It was a only one click because i have not selected the option store procedure but your post helped me to sort the problem , it's working now.

Thanks