Hello,
Thank you for taking the time reading my problem and replying.
@TheComedian: yes, it's a strange error. I have tried to convert a sp to a query since I didn't have a parameter in my SP, it still didn't work with SSRS and got the same error message.
@Bex: I am able to run all other sps but just that one sp I got the timeout expried error message.
However, my coworker just suggested me to back up the original sp, then throw all the records into a table then create another sp with one statement of select all from that table. Run the SSRS and make sure the fields got populated in the data set, then I just modify the existing sp and delete the back up one and it works perfect...Just wanted to share the solution.
Thanks again!
Lucy