There are many options available for building SharePoint-based applications. This blog post introduces another non-traditional option –
building SharePoint application using SQL Server Reporting Services. To introduce this approach, I will demo how to create a simplified second-hand car listing solution (within less than one hour):

Firstly, we go to a Sharepoint site and create a Sharepoint list to store the details of the cars. Then we open BIDS and create a report project. Within this project, we add a Sharepoint List data source and set it to connect to the Sharepoint site where the Cars list was created.

Then, we need add a data set to retrieve data from the Sharepoint Cars list. We can use the query designer to build the query.

The sharepoint CAML query is then generated and will be used to query Sharepoint list.

After we have created the dataset, we had the fields ready for creating the report.

We drag a table onto the report body and only keep one column, and then add a ‘Rectangle’ into the row cell and use the ‘Rectangle’ as the canvas to build the car listing item template.

When users click the ‘Details’ link at bottom right corner on each car item, we expect the report to drill through to the car details report. Firstly, we add a new report (the car details report) into the BIDS project, and then create a parameter called ‘CarSaleID’ which will receive the item id passed through from the clicked car item.

Then, we need create a dataset to get the car item from sharepoint list filtered by the given parameter. After we had the dataset ready, we can start to build the car item details template.

After the car details report has been created, we go back to the car list report and create an Action on the ‘Details’ link which trigger the drill through to the details report with the car item id as the drill through parameter.

Now, we have had our reports created in BIDS and we need deploy to either report server (in Native mode) or SharePoint site (in Integrated mode). After the reports have been deployed, we could add Report Viewer Web Part to host the report on SharePoint page. The Report Viewer Web Part shipped with Integrated mode offers more features compared to the Report Viewer web part in Native mode. For example, we could connect filter web parts to Report Viewer Web Part (integrated version) to dynamically set the report parameters.

• SSIS/SSAS/SSRS/MDX