We are using SQL Server 2008 R2. We need to provide the business users an interactive data exploration facility which they can drag and drop the attributes(columns) they want to create their own ad-hoc report.
I know in SQL Server 2012 it can be done using Power View and Sharepoint, but what about SQL Server 2008 R2?
You're probably looking for Report Builder 3.0 and/or Power Pivot.
Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 R2 Reporting Services.
Your Excel ninja users will likely prefer Power Pivot, your more typical tabular report folk will feel right at home with Report Builder. There's a useful summary of the differences between Report Builder and the BIDS Report Designer at Designing Reports in Report Designer and Report Builder 3.0 (SSRS).