Using Caml Query in Reporting services with sharepoint

Create new Report server

Download project at here

Open your Site Collection and create new Custom List name CallCenter follow as, you can download CallCenter at here

Open SQL Server business Intelligence Developer Studio | New Project | input name: MSTechSharing_CallCenter

Project structure follows as

Right click Reports | Add New Report

Check to “New data source” radio button:

Name: CallCenterDataSource.

Type: Microsoft Sharepoint List.

Connection string: your site collection

Check to “make this a shared data source”

Then click next

Click to Query Builder …

Choose CallCenter List

Click to Add Filter

Change Field name to filter then check to parameter

You can continue add filter

After filter click to “Run Query” icon

Results as under

Click to Edit as Text to change the query

This is Caml Query

You can Edit or write new Caml Query from “U2U Caml Query Builder” then click “Run” icon

Dialog Query Parameter appear (in the caml query which have parameter. Therefore, dialog Query Parameter appear) Input Parameter Value then click OK

Results as under

If you don’t want to save changes, click to “Edit as Text” then click Yes button on dialog

If you want to save changes, click to OK button

Click Next

Choose “Tabular” type

Add Title to Group.

Add FullName, Age, Email, Gender… to Details>

Then click Next

Choose Stepped radio button:

Check to Include subtotals (apply to all fields with types is Number)

Check to Enable drilldown to expand or collapse group

Then click Next

Choose Table Style then click Next

Input Report name: CallCenter1, check to Preview report then click Finish

Report run successful, input parameter then click “View Report” button, result as under

Right click to CallCenter1.rdl | view code

You can see caml query, QueryParameters, and DataSourceName to get data from sharepoint list

If you want to publish this report to sharepoint you must:

Delete <DataSourceReference>CallCentarDataSource</DataSourceReference>

And insert below  <DataSource …> tag

<ConnectionProperties>

<DataProvider>SHAREPOINTLIST</DataProvider>

<ConnectString>http://devsharepoint:90</ConnectString>

<IntegratedSecurity>true</IntegratedSecurity>

</ConnectionProperties>

Result as under

Open site collection and upload “CallCenter1.rdl” to Documents(option) library

Edit any page then add webpart reporting services then input URL to CallCenter1.rdl

Result as under (image 33)

Create Report server from Report server existing

Right click Reports | Add New Report | click to Shared data source radio button then choose “CallCenterDataSouce”

Click to “Query Builder…” button

Click to “Import…” icon

Choose CallCenter1.rdl which was created before

Click Import

Click to OK

Click Next

Choose Tabular type then click Next

Design the table as under

Check to “Enable drilldown” then click next

Choose Table Style then click Next

Input Report name: CallCenter2 and check to Review report then click Finish

Result as under