Monday, August 22, 2011

Make Report by Reporting Service with SharePoint List

(22.08.2011) -Sai Gon- Today I share with you a simple way to make a report by Reporting Service with data from a SharePoint SPList.


First at all we see what is report by Reporting Service on SharePoint Foundation look like

Can be export the report to Excel, PDF, Word, Tiff, XML, CSV, MHTML
 Now let follow 47 steps to make report by Reporting Service with data from SharePointList then show it on SharePoint as a datasource of Reporting Service Webpart

1. Open SQL Server Business Intelligence Development Studio


2. File ->  New -> Project...

3. Make new project from Report Server project

4. Reports - Add New Report

5. Report Wizard -> Next >

6. JobOrder SPList look like

7. Filling Connection string for SharePointList DataSource

8. Data Source Credentials

9. Select the Data Source -> Next >

10. Unable to connect to data source. Support Windows Integrated Security only

11.  Back to Credentials...

12. Use Windows Authentication (Intergrated Security)

13.Click Next after change Credential to Windows Authentication

14. Query Builder ...

15. Choose JobOrder list then click OK

16. Design the Query -> Next >

17. Choose Tabular then click Next >

18. Choose columns for detail of report then click Next

19. Choose the Table Style then click Next

20. Choose a name for the report then click Finish

21. Click on Preview

22. The first look like of report on Visual Studio

23. View Code of the report

24. The original of SharePointListDataSource

25.Replace DataSource reference by a SharePointList Dataprovider and a ConnectionString

26. Add Document to Share Documents

27. Click Browse...

28. Browse to folder location of the report, choose it then click Open
29. Click OK after choose the report


30. Click Edit icon

31. Click Insert

32. Click Web Part

33. Click SQL Server Reporting

34. Click on SQL Server Reporting Services Report Viewer

35. After choose SQL Reporting Services Report Viewer then click on Add

36. Click on Page

37. Click on Save & Close

38. Click on Report Viewer Web Part Menu

39.  Click Edit Web Part

40. Click on ... to choose the report on Share Document

41. Click Up icon

42.Click on Shared Documents

43. Choose JobOrderReport then click OK

44. Click on Appearance

45. Change width of report webpart then click Apply

46. Click OK at Tool Part

47.The JobOrder Report look like on SharePoint Foundation 2010

48. More information that how to integrate Reporting Service With SharePoint 2010
Intergration Reporting Service with SharePoint 2010 by 84 steps / Giải pháp miển phí tích hợp Reporting Service với SharePoint

49. Download code and list template
Report code: http://www.divshare.com/direct/15579552-347.zip
List template: http://www.divshare.com/direct/15579557-002.stp

Thanks God and Thanks all!

Only 10$/h or 10% market price I provide full Business Software Solution: mobile app (iOS and Android), Intranet Portal by SharePoint Online or On-Premise, Business Web App, Business Workflow, Power Bi, E-Office, Document Management, Chat system, Customer Interaction Center, Dynamics AX, Dynamics 365.

Or very happy to join your team to develop Mobile App or Business Web App.

Thank you!


Phone: +60 115 199 2689 (also on WhatsApp, Viber, Zalo)
Email or Skype: ThomasTrungVo@Hotmail.com
Gmail: SharePointTaskMaster@Gmail.com
LinkedIn: https://www.linkedin.com/in/abc365/

at

22 comments:

  1. bbiunionAugust 25, 2011 at 8:44 AM

    I tried to create report on sharepoint 2010 from the SQL server reporting services, everything is okie. But the problem is that when creating the report, I need to connect to the SharePoint List DataSource, so you can query from the list in SharePoint. But I do not see the name of the SharePoint stand-alone Server in the list of server name. Please help me and guide me, how can I connect to my SharePoint server from MS SQLServer 2008. The error due to SQL server is not configured correctly or the Share Point server configuration is not correct?

    Thanks and best regards,
    LongNguyen
    mail: elongnguyen@yahoo.com.vn

    ReplyDelete
  2. SharePointTaskMasterAugust 25, 2011 at 12:32 PM

    Hi Long,
    You can see the full technical solution with name: Intergration Reporting Service with SharePoint 2010 by 84 steps / Giải pháp miển phí tích hợp Reporting Service với SharePoint from http://sharepointtaskmaster.blogspot.com/2010/11/intergration-reporting-service-with.html

    Please see step C.22 and C.23 to know towau to connect to a SQL data source with Reporting Service on SharePoint 2010.

    Regards
    God bless us!

    ReplyDelete
  3. bbiunionAugust 25, 2011 at 1:52 PM

    1.
    First of all, I'd like to say thank a lot for your help on the problem that interests me. I studied full technical solution that you suggested. But the issue here, is to use DataSource to report (from the "SQL Server Business Development Intelligence studio"), I did not find any type of resource is "Microsoft SharePoint List", as shown in step 7, so I do not know how to retrieve data from the list in share point.

    2.
    I tried installing 3.0 report builder to generate reports, everything that Okie, but could not add this report to report viewer (error: rsinvalidReportDefinition)

    Looking forward to your help,
    Best regards,
    LongNguyen
    mail: elongnguyen@yahoo.com.vn

    ReplyDelete
  4. SharePointTaskMasterAugust 26, 2011 at 3:11 PM

    Hi Long,

    Please look back on step 6 to see what is your possible SharePoint URL that need to enter to "Connection string:" section.

    Base on your server name and the port of your SharePoint web application, so the url look like http://yourServerNam or http://YourServerName:PortNumber or http://yourServerName:PortNumber/SubSiteName

    Regards
    Thomas Trung Vo

    ReplyDelete
  5. Đỗ Quốc HùngAugust 26, 2011 at 4:23 PM

    helpfull article!!!

    ReplyDelete
  6. suchonAugust 28, 2011 at 4:52 PM

    Thanks so much,It is very useful :)

    ReplyDelete
  7. suchonAugust 28, 2011 at 5:05 PM

    Hi Long,
    I had do it ok,but i want to ask you : how to auto update report in site when i rebuil at Report Server project?
    Thanks.

    ReplyDelete
  8. Binary RepublikSeptember 7, 2011 at 2:07 PM

    I Really Enjoyed The Blog. I Have Just Bookmarked. I Am Reguler Visitor Of Your Website I Will Share It With My Friends Thanks and I promiss I will visit your blog again.
    Sharepoint Consulting

    ReplyDelete
  9. SharePointTaskMasterSeptember 8, 2011 at 10:43 AM

    Thank you Binary Republik, I am happy because you are enjoyed with this blog.

    God bless us!

    ReplyDelete
  10. Md. Aminul IslamDecember 12, 2011 at 7:18 PM

    Hi, Is it possible to do the same thing using Sql Server 2008 ? I am not getting any data source type like "Microsoft Sharepoint List" for Sql Server 2008.

    ReplyDelete
  11. Md. Aminul IslamDecember 12, 2011 at 7:20 PM

    Hi, Is it possible to do the same thing using Sql Server 2008 ? I am not getting any data source type like "Microsoft Sharepoint List" for Sql Server 2008. Please help me..

    ReplyDelete
  12. SharePointTaskMasterJanuary 17, 2012 at 11:17 AM

    Dear Md. Aminul Islam, You must setup SharePoint 2010 Foundation/Server and SQL 2008 R2 "SQL Server Business Intelligence Development Studio" on the same machine/PC/Laptop. Base on it you can see "Microsoft SharePoint List" provider for the report.

    Setup Logmein on your server/desktop/laptop then send me the account to remote with it. I will help you.

    If your company need more report. Please send us the the Splist template, site URL and what information that you want to display on report, we will make then send back. The first one is free. After that 50 USD/ report only 1/3 price of most Report/ERP solution providing to their customers now.

    Good luck to you Md. Aminul Islam.

    ReplyDelete
  13. wingsboxMarch 28, 2012 at 2:46 PM

    how can i get a carlander data of SharePoint 2010 in reporting service?
    The data that I get from a list data of SharePoint 2010 is empty

    ReplyDelete
  14. SharePointTaskMasterApril 2, 2012 at 1:51 AM

    Dear Mr./Ms. 최고를 위한 최선을 다하자
    With the 15th step on the article you choose Calendar replace for "JobOrder list"
    (15. Choose JobOrder list then click OK)

    ReplyDelete
  15. Soumya Ranjan DasSeptember 22, 2012 at 4:46 PM

    In Step 13 after enter site url, clicked on next and am getting access denied error. It is because site is configured with FBA.
    Do you have any solution for this.

    ReplyDelete
  16. rushdySeptember 27, 2012 at 4:52 PM

    I am creating a report using SQL Server Business Intelligence to data source from a SharePoint List
    The problem I am having is there is two list one is in the parent level eg. at http:/sharepoint/PM a list call AllProject
    And under AllProject a list of projects each project having a another list call Milestones which is at http://sharepoint/PM/Project/S019278
    So I wanted to create a report to return information from both the list.
    When I use http:/sharepoint/PM as connection sting I cannot see Milestones
    Appreciate all help and assistance
    For eg
    Project Id MilestoneName
    121 SBC1
    121 SBC2
    121 SBC3
    122 WP1
    123 CNG
    123 CNG3

    ReplyDelete
  17. UnknownOctober 4, 2012 at 5:00 PM

    this is exactly what i was looking for.....thanx a lot!!!

    ReplyDelete
  18. Rohit Warghade's BlogOctober 9, 2012 at 12:45 PM

    Hi,

    I want to create chart using ssrs and sharepoint list aldo want to pass parameter as cascading dropdown + complex query please help me
    Reply

    ReplyDelete
  19. UnknownApril 25, 2013 at 6:57 PM

    Get the below eror while inserting the webpart.Please help

    Error
    An unexpected error has occurred.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 22a89931-d82c-4dd5-aced-1b5366e4b008

    Date and Time: 4/25/2013 5:15:12 PM

    ReplyDelete
  20. SharePointTaskMasterMay 1, 2013 at 11:13 PM

    Dear Parvarhy E, please back on step 48 and make the integration Sharepoint With reporting service first than can be add reporting webpart on any page.

    Good bless us!

    ReplyDelete
  21. Boye O.September 30, 2013 at 10:01 PM

    This does not work when trying to create a dataset based on a workflow tsk list data. Data structures are returned but no data returned. Has anyone tried this with a workflow task list?

    ReplyDelete
  22. UnknownOctober 7, 2016 at 11:29 PM

    After searching all day for an answer, and reading ridiculous comments on how it is oimpossible to bring data from a SharePoint list into SSRS, I found this blog extremely useful.

    Thanks, Thomas!

    Any tips on how to remove the pound signs and ids from multi-value look-up columns?

    Many thanks again!

    ReplyDelete