ĐÀO TẠO DOANH NGHIỆP : SỞ KHOA HỌC CÔNG NGHỆ TỈNH ĐỒNG NAI

ENTERPRISE TRAINING: DONG NAI DEPARTMENT OF SCIENCE AND TECHNOLOGY.

HÌNH ẢNH TẬP HUẤN LỚP SHAREPOINT WORKFLOW VÀ KIẾN TRÚC SHAREPOINT

PHOTOS OF SHAREPOINT WORKFLOW AND ARCHITECTURE CLASS.

HÌNH ẢNH TẬP HUẤN LỚP SHAREPOINT WORKFLOW VÀ KIẾN TRÚC SHAREPOINT

PHOTOS OF SHAREPOINT WORKFLOW AND ARCHITECTURE CLASS.

Monday, May 21, 2012

Using Caml Query in Reporting services with sharepoint


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