This is totally possible. You can use report builder. It is a MS Office based client tool that the users can use to design and deploy their own reports. Although it is not a prereq, i would recommend creating report models and then users can create reports
simply by dragging and droping without any knowledge of the underlying data sources.
http://technet.microsoft.com/en-us/library/dd220460.aspx
http://msdn.microsoft.com/en-us/library/ms167048.aspx
http://www.mssqltips.com/tip.asp?tip=1115
Hope this helps.
