Wednesday, April 4, 2012

How to quickly create a report against Sharepoint List Data Source without any Code

First we will need to create a Data Source pointing to the SharePoint Site that host All the SharePoint list. when you create a new shared data source, we must select Microsoft SharePoint List from the Type drop down list.



Second we will add a new report to the SSRS report project. followed the wizard to add the new report, select the data source that we created from the above step. if you did not use the wizard and want to create the report manually, you will find that you have too much task to tackle. since you need to find out the GUID for some specific site columns in the SharePoint list.
you will have to use the SharePoint designer to generate DataView of the SharePoint list, then you can find the GUID for each individual columns in the SharePoint list.





third you will face a great challenge if you want to manually create the CAML script and paste it into the Query String Window which i had mentioned in the step two.




Actually when you click on the Query Builder Button, a Edit window will load all available List for the specific sub site of your SharePoint site.





Select the SharePoint list that you want to generate the SSRS report, all the fields will be shown in the field window, you can remove any fields that you do not want to show in the report. you can use
filter button to setup any filters that required to reduce the number of records on return.



Click OK button, the CAML script will be automatically generated in the Query String window. we will find that FieldRef Name actually use SharePoint GUID instead of the column name. Here is the most difficult part if we try to create the report manually, if you use the column name to query the SharePoint list in CAML query, those column will return NULL value. since the FiledRef use GUID to reference the column in the SharePoint List. 



Then we just follow the rest of the steps in the wizard to generate the SSRS report.

if you want to check GUID mapping with specific SharePoint site column, you can
go to the DataSet property window, and select fields in the left navigation menu.
then you can easy drag and drop those fields into the report.




You just need to follow the above step, then you will be able to create a sharepoint list report without any code.











No comments:

Post a Comment