how to use stored procedures in report

Question

Post reply

how to use stored procedures in report

Luke-640044

SSC Rookie

Points: 42
More actions
June 24, 2008 at 3:45 am

#188408

Hi
I need to set a stored procedure as source for a report. how can it be done?
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 1

Just use

EXEC [Your Stored Procedure]

in your Query String. You can use Parameters also as follows

EXEC [Your Stored Procedure] @[AnyParameter]

Of course you have to write your procedure yourself 🙂

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Luke-640044 SSC Rookie Points: 42 More actions · Answer 2

thanks for your reply

but I don't understand how to tell that some parameters can have empty value.

do you know how?

thanks

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 3

You can say

EXEC [Your Stored Procedure] null

It will depend on your stored procedure what result it will give if you provide null as a parameter value to the stored procedure which is using an input parameter.

I hope I am answering what you asked.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Luke-640044 SSC Rookie Points: 42 More actions · Answer 4

i set sp params with = null

but when i run the report it says that i musto provide all the values for all params even if I set them to null inside sp

checkai Hall of Fame Points: 3041 More actions · Answer 5

You have to setup the parameter within the report to allow for null values. By default you must enter a value.

Luke-640044 SSC Rookie Points: 42 More actions · Answer 6

ok. can you explain me how to set inside report please?

thanks

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 7

Do as follows in the report designer- layout - report parameters

1. Check the items: Allow Null value and Allow blank value

2. Add blank/null in the available values

It works for me.

Also you have to check the logic in the stored procedure itself how it is designed to behave in case of a null value supplied.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

Luke (6/24/2008)
Hi
I need to set a stored procedure as source for a report. how can it be done?
thanks

It is very easy when you create a new datasource click on the ellipse you will see the properties of the datasource change text to stored procedure. This is actually a better option because most code Reporting Services reject as command text will run as stored procedure because the relational engine is controlling the execution.

Kind regards,
Gift Peddie