SharePoint 2013 SP1: How to Set Up, Configure and Test SQL 2012 SP1 SSRS

Article
1/17/2024

Description

This article has step-by-step instructions on how to setup, configure and test SQL 2012 SP1's Reporting Services (SSRS) with SharePoint integration. There already exists a similar TechNet Wiki article, however, the steps provided there are fundamentally wrong. Out of respect for the original author, the article has been left there.
I am also going to show how the SSRS with SharePoint 2013 can be setup in a two-tier environment, one with Windows 2012 server that has SQL 2012 SP1 and the other with Windows 2012 server that has SharePoint 2013. To install reporting services SharePoint mode as a single server form, Click Here.

Misconceptions

Before I walk through the setup and configuration, I want to clarify some of the misconceptions and/or confusions.

Do I need to install a separate SQL database for this on the SharePoint server?
***No, you do not. You can use the existing SQL server of your SharePoint farm to setup the SharePoint ***

Does the SQL Server Integration application come to OOTB?
No, it does not. You need to install both Reporting Services - SharePoint and Reporting Services Add-in for SharePoint Products.

Reporting Services Configuration Manager does not give me the option to choose the SharePoint Integrated mode. I only see Native. What did I do wrong?
***You did not do anything wrong. This is by design. As stated in the MSDN article for 'Install Reporting Services SharePoint Mode as a Single Server Farm' . NO SETUP OR CONFIGURATION IS REQUIRED IN THE REPORTING SERVICES CONFIGURATION MANAGER. **
*
https://va9vsq.dm2301.livefilestore.com/y2pGhgQjVy9EekZWSKFJyZb4rP5xX_rsuP4_4xBPHgqjzUUEMnVQtn2n5ozS2FBOjNt-kClH4Q1uz70zMg7lbTZC2uv4MZmI8ISGu_uYh-Itr8/Capture.PNG?psid=1

In SharePoint 2013's Central Administration's SSRS, do I need to add a URL for the Reporting Services Integration?
No, you do not. You have already installed the SQL Server Reporting Services service application.

Installation

In this section, we are going to install the Reporting Services-SharePoint and the Reporting Services Add-In for SharePoint products on the SharePoint application server using SQL 2012 SP1. I highly recommend you use the SQL Server 2012 SP1 - Full Slip Stream which is available at http://www.microsoft.com/en-us/download/details.aspx?id=35575

The steps to install in a two-tier environment are very similar to a standalone environment hence I am going to refer to the ' Install Reporting Services SharePoint Mode for SharePoint 2013' article at http://msdn.microsoft.com/en-us/library/jj219068(v=sql.110).aspx, and point out the differences in the steps.

Installing Reporting Services Report Server in SharePoint mode

In this step DO NOT INSTALL THE DATABASE ENGINE SERVICE. We already have a SQL server. I also recommend installing the SQL Server Data Tools since it is a very useful feature to build reports.
Below is a screenshot of feature selection.

https://va9vsq.dm2301.livefilestore.com/y2ppXtPFD_4zgF9hj0QX4pYL3Zh7hOWYG8fUfpvYVy7zvo5SizU-Kqig4ylcjyfKz_KE5uzCOo5AGA2l17X478RkUT17vgzkWLS9ypG_a-9iAM/Capture3.PNG?psid=1

Register and Start the Reporting Services SharePoint Service

Follow the steps as is. Focus on the important message which states as follows-

https://va9vsq.dm2301.livefilestore.com/y2pgooBY3qJnE_srPVDRfBa9mnr9dfOQ26We_gaMdICzp1vtho0aHtgA2SnbP_xDeJckRJukw44Ag7cz1BowmIil34oVanrljhxjgcTelqV48I/Capture4.PNG?psid=1

Create a Reporting Services Service Application

In SharePoint Central Administration > Security > General Security creates a new service account for this application.
If the Reporting Services - SharePoint and Reporting Services Add-in for SharePoint products have been installed correctly you should see SQL Server Reporting Services Service Application as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pwLXQFTZpaOcE_mSUBanLHMK_EQogLgX-I7vOgAzDY7QeoSaX6tE87Elh8oaFu5qiL0dkmh6QBXiDhxPD1rHC8fAdNrXaeNaLNtL5NNoTQ1Q/Capture6.PNG?psid=1

Choose the correct Database Server. In this example, we already have a separate SQL server, choose that one.

Activate the Power View Site Collection Feature

Follow the steps as is.

To add the Reporting Services to the web front-end of a SharePoint farm you can refer to http://msdn.microsoft.com/en-us/library/hh479775(v=sql.110).aspx

Setting up a SharePoint site

I have already created a new site using the 'Business Intelligence Center' template. Below is a screenshot of that site's home page.

https://va9vsq.dm2301.livefilestore.com/y2pW8O3sb4SdyrZ7mBFcq-IgTqXjvBkC9n4uA46fkoOQH2q_9gyOSHc4Mmuw1bMi7Ca96sBoZVvbzDSauK6nkKPDbhC6KEA5KpFI_iaeIzh7Vg/Capture34.PNG?psid=1

I also created a new document library and called it Reports Library. Go to the library and click on Library Settings as shown below.

https://va9vsq.dm2301.livefilestore.com/y2popOo7P9V9wWn-R_lWchKukGyJU8nTxptst7xrWusD-bryEcVw40w0LGHLsmn6JvDlrr_at4rsx2X1OXZ9b_4rCqjLPLAqm-h0u0AOZyFVv4/Capture46.PNG?psid=1

Under General Settings, click on Advanced Settings.

https://va9vsq.dm2301.livefilestore.com/y2pNSRxcsT1ybjRh0ciHOBM0XgQhEIi1bbjwGcnyIuB9rQnAO7Inodi5c75Odg1SjLgolMEAGG59LWxu4mxHl5bXTcWX-I4pnlvPKVNeUAm6Vk/Capture47.PNG?psid=1

In Content Types, Allow managed of content types, select Yes. Scroll to the bottom and click OK.

https://va9vsq.dm2301.livefilestore.com/y2pmyRDgXIVUKv9-G1fsfVOGuOohgHiIR-nCSXAn1nMEUQmTf5DcH_RyA1F-7f4BpG7aGHNLisuQo_XY6UR6iPqUlvb4fFI7Kb2YWk-xcJ9WiM/Capture48.PNG?psid=1

Now you should see the Content Types option in the Settings as shown below. Click on the 'Add from existing site content types' option.

https://va9vsq.dm2301.livefilestore.com/y2pyic5HsILrBsmFu-E-WmuoUJG9t-Jxk1IVeAZD6lSQOfTDy5uXJqIIlQu6lih9Kmtzwf-MH3osIo_FNonXrhGIEaikrOCQYIeRn5EUKy4qvg/Capture49.PNG?psid=1

You should be in the Add Content Types page. If the above setup and configuration steps have been followed correctly then you should now see the 'SQL Server Reporting Services Content Types' option as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pRCRB2VcL2j0YYfQvlGpfCZTw8QsE-shbifW8Fe7bTrszprGDahHScmk9sMzSxGo0BETW66IUgarBXStiMn53gszh_zdKxKgdpgMZGathUSY/Capture50.PNG?psid=1

Select all three available content types and add them and then click OK. Below is a screen shot.

https://va9vsq.dm2301.livefilestore.com/y2pV5ZCKhOb7GmBg2_8Y-wYbDXn48qkZeUby_l6PYkmdQWlgWY154s9-Aot_UN-2r16M2VP5TUqcfOhcZA3ZxNJ4PDP7fCJhx5cGAmqfQAJ46A/Capture51.PNG?psid=1

Now you should see the following in your Report Library Settings under the Content Types

https://va9vsq.dm2301.livefilestore.com/y2pY-OXhL6InZncXoPoXiDBlLqncJSgvEzhPFfIKrVBRFRco8reFuWjbnx76Nzn8rqgrDPGpeBdgZhrkXUILys7mFP8HSlXqTdZEqJ6nHNaNH8/Capture52.PNG?psid=1

Verify SharePoint Mode Installation

Once we have the SharePoint mode setup, we need to verify the installation. We are going to follow these steps to verify SharePoint Mode installation as described in http://msdn.microsoft.com/en-us/library/ms143773(v=sql.110).aspx using the AdventureWorks Report Samples available on CodePlex.

Go to http://msftdbprodsamples.codeplex.com/releases/view/93587 and download the 'AdventureWorks2012_Database.zip. Unzip the file and place the MDF and LDF files in your SQL database folder which is similar to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA.

Attach the AdventureWorks212_Data.mdf file to your SQL database. Below is a screenshot of what your database will look like.

https://va9vsq.dm2301.livefilestore.com/y2p4FZeRasS1XpeTKBlZBW0ivt2fe2nZSkSETWA0T7kIH5YBnOHyWOCL61hJpzX-BdcMHkbBom-azYZR3dzmafEiMUZZOrwePXjLiNTKy3baW8/Capture7.PNG?psid=1

Building the Report

On your SharePoint server open up the SQL Server Data Tools (aka Microsoft Visual Studio 2010 Shell) application which was installed along with the SharePoint integration features. Below is a screenshot of the application.

https://va9vsq.dm2301.livefilestore.com/y2piS3OfW_6D0JK-L4lbq4UZH0EG2yHoScOMX8TZo7XmyIozBT4n8-OSk0TXxl1MA4mgI6o91DLUG31_HAjnFG9wy7s0us6ekP2O8c7GAz8suQ/Capture8.PNG?psid=1

Before you begin using the application for the first time, you will need to specific the type of development. In my case I choose Business Intelligence Settings and click on 'Start Visual Studio'. Below is a screenshot.

https://va9vsq.dm2301.livefilestore.com/y2pppBfbIgIIRDK_W9Ke9mbVxcEIVHJ1xnrWSh9v2toE-f9hHdWoPV7tt46wcAuwIgH69Jhid0xMnn6NGsEIqQfB8lkzB2fld5YtR4_Kvi6H48/Capture9.PNG?psid=1

You may see the following when Visual Studio is loading.

https://va9vsq.dm2301.livefilestore.com/y2pMyZ9aloDp-HGrJZOdvkZZNvMOqLbv_2Qy5wZcuU4lu0AJk6XtztSgwgW-9g-a4wP3y7VO4LGTra6inKhohLHqe561w7X-D58QBLSfS9ReD8/Capture10.PNG?psid=1

Once the application loads below is what you should see.

https://va9vsq.dm2301.livefilestore.com/y2pt-YHB10h4KPOgS_MPIvXLaeylakPxP31dOstqc02FfsJi6lpB78fRk9FQllXkA_oMo7xH63XDFjUyMJfr4pv1j6xQ1bAgDCB8J_O8VLian4/Capture11.PNG?psid=1

Click on New Project, under Business Intelligence click on Reporting Services, select Report Server Project Wizard. Give it a name and click Ok. Below is a screenshot.

https://va9vsq.dm2301.livefilestore.com/y2pYuMK22t725AqrhHNmYBNiBHly0Up2V6vLKQuNOwxU5wfixRqjR4SLr_AHWtfhL2twPaDXUcHSuJbIMj9RdYHKdkOYp9eQwU9jkGy0DtieuE/Capture12.PNG?psid=1

The report wizard will walk you through the process of selecting the database source, building the query, choosing the reporting type and building the layout and format. Below are screenshots of the wizard.

https://va9vsq.dm2301.livefilestore.com/y2pzObAAFCpBAlf-E_EzbWzIjdccW30EfBkHtj_6nozSoG457yEBWxJWMywCHaYKCoBCSkuxw3IybvMlpC5RyFNWo11wM5oRLc6KTr78bR4LQY/Capture13a.PNG?psid=1

In Select the Data Source, select Type 'Microsoft SQL Server' and click on Edit. Below are screenshots.

https://va9vsq.dm2301.livefilestore.com/y2p6TM1wSsAS-NQgUJZZkgOSZ-2fWsp1arn6A0utPGfq2zG1Cv365u9K4Kzb9KZtzdpnuxIHWqE0d2jlpHZZgQq50laxPLzQHagHbtNjJKsNKw/Capture13b.PNG?psid=1

Leave the Data Source as is, type in the Server name. In my case the server name is contoso\SQL2012SP1. Once you typed in your server name, click on Refresh and then select the database name. If you do not see the list of database names or get an error means your account hasn't been given adequate permissions to the database. In this example, I had to go back to SSMS and give my account db_datareader access as shown below.

https://va9vsq.dm2301.livefilestore.com/y2p_FJu2RFIYzGsOY2I_meJO3F47_RGzit6gZQ2tFl78CbrtiSpqTrAvYvTlfvYBut1GDUgP1NCX-Ku_NtwqbhEUebt0qqpTZDAm29vuiMHDfA/Capture14.PNG?psid=1

Now back in the Report Wizard I was able to see the list of database names as shown below. I selected the AdventureWorks2012 which I had attached previously.

https://va9vsq.dm2301.livefilestore.com/y2pD0xRlZax5J0O_lSxebfpYC4gJQtfAcgBHqvv65eqEo6UMtfTx61dAUfynxgvIqVHb7aA7ZfmiZiTzWFuffoL7EnqxP78_uv50Y9n3F4bg08/Capture15.PNG?psid=1

I recommend you click on Test Connection and confirm. Below is a screenshot.

https://va9vsq.dm2301.livefilestore.com/y2p3w_Bojj1FUf6BxlzNH8Xv98bbd289_szSnbS5mmU3KHuIgrCh07YW_gn5Ls-sN2RRMBe6NqLEkSCM3o0WtNGibgYsfNipcvoyTb0jTk2G3U/Capture16.PNG?psid=1

You will also notice the connection string as shown below. Check the 'Make this a shared data source' box and click Next.

https://va9vsq.dm2301.livefilestore.com/y2pF5BO3XGbcYUxkJjiXckZQiEyDjj9NCzDtppglXjQetHguo3KVry6X7cOQf8GXqJYDw-mD5-ZTSqD_fi2YpvcsjWpWX_6ypjIFF7W4owEfkw/Capture17.PNG?psid=1

Now we need to build the query. Click on the Query Builder as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pJeQslK1KGZx9U-LRcXrWtKAAxUXqOHBzmWwceukFFQMblAkFeZSBebG-mPM7EYPwJ9dZZt7GQ2T2RC8qTUHH9IpLucRM9P9hGTjesrHw-aY/Capture18.PNG?psid=1

The query designer can take a few seconds to load during which you will see the below.

https://va9vsq.dm2301.livefilestore.com/y2pGQXY6uIq5wIopiHQZwJtGqiMCNcPL2gw9XcmKaf22e1RvYe-BVWukreZes2bcbPWftdI0e6bURTN1VZHFvSDZ9-gRjyGsyufXwuTTg6-jro/Capture20.PNG?psid=1

Once the query designer is done loading below is what you should see:

https://va9vsq.dm2301.livefilestore.com/y2pLyJ8JtiScKl03iRN62pt3x98w6DLO6pHMIymxgtOkv_lkN50sAb1MPL1H0fvBV-nXlxfRuSv17Y9XTVgFOzMqpfUWeJAmUx6X62PWFQ0f8k/Capture20a.PNG?psid=1

Click on the Add Table option as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pWkHQEaUfM5OnOIySlaDHZXFLmOsamnIyuOO5IHGnpZNY2qtJL2mrba0Yd_ncQA1xHzCZ_WgGjsKK3mhR2ewIYBuz7tjEmvFBm-yVETsQr50/Capture21.PNG?psid=1

You will now see all the Tables, Views, Functions and Synonyms.

https://va9vsq.dm2301.livefilestore.com/y2p_piNgEAL-j1kAtvDQfPoQ_MeT_h1I1pIe-0YGevJdNoudbTzaoGszeUThZSd-e0p2g53zYNNOzS8CuuuzTTdSkIMt3l6KCafQUDkMKHr578/Capture22.PNG?psid=1

For this example, I am going to choose the Address (Person) table

Below is what my query looks like after adding and selecting the columns in the tables.

You need to make sure you have the correct relationship columns selected for the query to work

Once the query building is completed below is what you should see

https://va9vsq.dm2301.livefilestore.com/y2pPbAw-D6xYDD_GM_fvWP8qzHjXT1gkcSWqxeVBI2Hph8jl_hbbwiNmFK8dXgbWEmreGGpkrqVFtOnWN4f1gsESH3A6t4loNQFyIgOegK3YG0/Capture24.PNG?psid=1

Click Ok. You should now see your Query string as shown below. Click Next.

https://va9vsq.dm2301.livefilestore.com/y2pIMmOes9WXe9gu0wrkiNUkrAN56hPD6nZNRk-qfF5kK92N1jkbMoovecTP17Hq3dlzP1OZBY1O6A5VvXPqWATj1aJecYQIRG6ggopj27jW5o/Capture25.PNG?psid=1

Click the type of report format you would like. I have selected Tabular. Click Next.

https://va9vsq.dm2301.livefilestore.com/y2pAo0pJi0abVwJx8Lghlka6WWnfV7QRhV4LIB9fIZi3jtUaMxfMKcqcxoRl09krXSWfOyu4ZBKZSjRAULs9SapO_oYMiXgIKuG_wuzzESa1-g/Capture26.PNG?psid=1

Below is a screenshot of how I have built the groups in the table. Click Next.

https://va9vsq.dm2301.livefilestore.com/y2pMXPG74wv3KCPo_vciuBqFej-fqaVmGnxtG4tvXBTeyhwgNDZcP6JFHZJZtdOOlf1J2ylxl2OXGsY8gurKfNENqQR3rwfnFm0LhfZasl8h78/Capture27.PNG?psid=1

Below is a screenshot of the table layout. I have opted to choose Stepped and selected the subtotals and drilldown options. Click Next.

https://va9vsq.dm2301.livefilestore.com/y2pknmwdj_JTUcDmwrjIJdI_9We-k4gOItbgv5ruYLDvaczqxB1j5OiO2DCtIQkegHAcT_xn00PrBeIYw6si6_2kT8KxH83rzNF0MR_MlQBksI/Capture28.PNG?psid=1

Select your table style. In my case I have selected the Corporate style and clicked Next.

https://va9vsq.dm2301.livefilestore.com/y2p5pWO0yZWgmPVgk2MuVLVQmcPMztztp514dQYDsgsgVv_FU2158IthxjXezQnYqyUpnB0gu5fC-ZJj9RsC5-eWRShxWWXFT2v13sGqkvn3GA/Capture29.PNG?psid=1

DO NOT MAKE ANY CHANGES HERE. SIMPLY CLICK NEXT

https://va9vsq.dm2301.livefilestore.com/y2pVhmw-5tZlDvpMgw-veoetd5fwUQxCSQr4Mc-YgxCP4_LSXMiVCbgXieZH8dOlGXePnBgG5pVyE6_x5ylGKD-1yoBB-IlW_f-mV_BdRTpfco/Capture30.PNG?psid=1

Provide a report name and click Finish. In my case I gave the name TestReport.

https://va9vsq.dm2301.livefilestore.com/y2pfhauRrvjnfRpGwIFxSJQbLPO5lhrqQBP-Yg1MqAmjRT_KNgdRck-JTmf5rkMrgxSjoqhi3q-tBjm5-DU-B8X6SDsYrqZ_Y6rQPxwHnsp8y8/Capture31.PNG?psid=1

Now you should see a preview of your report.

https://va9vsq.dm2301.livefilestore.com/y2pw5ta-vtvKLqQWKIe5MwgUGwRhhOcblV8NSjkHA5QjyHvRpD3wbLh1lko6VxZXMB1es4lU2IBXPMEWu8XX0CSdd7sFAg_uUzn0Fxs19rZ4fw/Capture33.PNG?psid=1

Click on the Solution Explorer to view the Reports.

https://va9vsq.dm2301.livefilestore.com/y2pWwXu7Y2mDTCxOT67HpP2foHGq_sf8vk6t-IxQZidYPPzbYPU3pAI9sne3nDRGdZBU_vLwvmAmrCBS1jL6A9Q6qbTDlRsILOEkE7Go5yaFsQ/Capture1.PNG?psid=1

https://va9vsq.dm2301.livefilestore.com/y2pUcSIF_W2FYei1AdlSz6PKWn79Owsqb93-olVTPwQy3j_82iU8k0F-AwgCtP331O50kzMPVPJKGuAuznndgCCSJjZqglwGlBYCVaAaIrYJ6o/Capture32.PNG?psid=1

***When you click on the TestReport.rdl file you will see the Full Path. Make a note of that path since we will be using it shortly.

Setting up SharePoint 2013 site

I have already created a new site using the 'Business Intelligence Center' template. Below is a screenshot of that site's home page

https://va9vsq.dm2301.livefilestore.com/y2pW8O3sb4SdyrZ7mBFcq-IgTqXjvBkC9n4uA46fkoOQH2q_9gyOSHc4Mmuw1bMi7Ca96sBoZVvbzDSauK6nkKPDbhC6KEA5KpFI_iaeIzh7Vg/Capture34.PNG?psid=1

Adding the Report Builder Report

I created a new document library and called it Reports Library. In this library, click on the Files tab and select the 'Upload Document' option as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pDTz649W_nFqzz0HA5rHKb5tg4GNX--eB9xwC6S2QMcUjGvYI6Yi9AgS6ERYHg_AxONmRh-qvGxWfYNQf-9WcJpe1QkYqrTJvrjl-8rhJLDw/Capture35a.PNG?psid=1

https://va9vsq.dm2301.livefilestore.com/y2pSaHqmgVCzOqL39I4KQ3qUNXeZtUl7A4vT-1UEJb84zSBxlyekpPWO-vh-IcFjQk7bJ_2DzYw0SdXFHRat7E88GUjdahv4IXyjLihqwsilQY/Capture35b.PNG?psid=1

Click on the Browse button and find the location where the TestReport.rdl file is saved. Select that file. In my case the file was saved on the SQL server and hence my location is \sql2012sp1\C$\Users\administrator\Documents\Visual Studio 2010\Projects\Report Project1\Report Project1\TestReport.rdl.

https://va9vsq.dm2301.livefilestore.com/y2pSUiLQdZSdMGVJVDbotaBHH7W6IoebXCOJ2adssh2jBLFaYYMKdWuRNagYZjoafW8teci2TkkkHGyLn3-ZQ67_4Tjb9pc1sdcXsBBsbtOYUI/Capture35c.PNG?psid=1

Once you click Ok. You will now be presented with the below option. Click Save.

https://va9vsq.dm2301.livefilestore.com/y2p9g-wvKRN4ll1PJ5fgyV22uMUshU1Xw1-pneee7VTKQ25nuHPBEmdeVCki7Vtbd-IxWnfI47HIRqdyQ9Bo0B7Bk55XaVv0JwH6Q04nr87FKY/Capture35e.PNG?psid=1

You should see your new rdl file in the Reports Library as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pn3k_mU7ER4TX_wz7JdMxjhb_Guo5cwY0ZW3RWTQE13hp6jqkmtxQ1gqHbe73w1ENnihP6Q46TL67IKS5d-ZQCmX_-ntSq6iqqy4TLhEiVlI/Capture35d.PNG?psid=1

Adding the Report Data Source

Before you create the Report Data Source, go back to the SQL Server Data Tools, open the TestProject1 we built earlier and double click on the TestProjectDataSource.rds file. Make a note of the connection string as shown below.

https://va9vsq.dm2301.livefilestore.com/y2pgarQMjQAk-pBk9Hn5yOT629tAQYhnNNzhVWijW623pipAswJTJ0fhcZ--PkV_WmEz9Ey_Y6nbVsSvUGhCRVC5Ft-ZIp7fKpL4w5DocbHcSc/Capture39.PNG?psid=1

Now go back to the Reports Library we built, click on New Document and select Report Data Source.

https://va9vsq.dm2301.livefilestore.com/y2pE9CnYb6TTEcWRS8BulAMw1tl_6NLTtwWmDKTyusgfYaSYq9ecaaM56mJX3dns8EVpC1rg3FN2kYOzqxGHV-pRTvxLCmFh3DxUwjncGYmf4E/Capture39a.PNG?psid=1

Populate the Data Source Properties as shown below. We will use the same connection string we just looked before.

https://va9vsq.dm2301.livefilestore.com/y2pB6UUCr84DSJ0kgqq7gUieMzZNfPfZmDDNejQCuQRabQ4RAmwTMgjl_qqdkTS4yPIqoxYfw1GHBU1Rn4GXo_Ya9N0ffyTEjbRo9KNNMJAMkk/Capture39b.PNG?psid=1

Click on the Test Connection button to confirm that your connection was created successfully. Then click Ok.
You may get a connection error here. If you do back to your AdventureWorks2012 database and give 'NT Authority \ Anonymous Logon' the following membership access

https://va9vsq.dm2301.livefilestore.com/y2pb7xCUG_LMuKenH4ZBvSjGpy936TTyPg6IpMwa7aOSNMD7VfBH2UZXzIhq7jpX0GjZWZi-MFjG8xgOM4xIDz030doRHZvJ_D8PhFe_yywl9g/Capture39bb.PNG?psid=1

Now you should see your new Report Data Source as shown below.

https://va9vsq.dm2301.livefilestore.com/y2p7bRaQz94HOqXB-nyx810UF7olJGpDWzcadaJNvDXYnkSOn7gWyO6cbAGSeoQSfHVBPSdq3FqwsOw_-Qa74_oum_utE_8GvBiL-5NtLEvwTU/Capture39c.PNG?psid=1

Connect the report to the data source

We now need to connect the report to the data source. In TestReport, click on the options to see the Manage Data Sources. Below is a screenshot.

https://va9vsq.dm2301.livefilestore.com/y2pLyx8HGOn_RnrklIreyQEbFc3coHQJptbZptH7oyQJBj6mqav7wp-1QKz1_nqpLExPTOxKACOQRHzGZdJSZC0h106lYzR9a2kKwL3gV7os-Q/Capture40.PNG?psid=1

Below is what you should see. Click on the TestProjectDataSource.

https://va9vsq.dm2301.livefilestore.com/y2pn5JuRlB6jTR6bh6IiI1Ni9cS8i_LhKUXTsVGROKrk0dX5wkHng1L4SobtrmEzwO62IGdXht1ewI1ZWRj1Nv7bAbvqbN2_s_to15cBP1BDCg/Capture41.PNG?psid=1

As you can see below, the Data Source Link is empty. Click on the options.

https://va9vsq.dm2301.livefilestore.com/y2pTB3tuqk1nLVuMttpCO2x1QsUzwX9GKtF3BQJluxR38Dc7tyqJj0EEVNqhDVPqovU0cvvak_z5hzgdU2xziYtF6bMlRXZpNmNwZr-hSAIfHw/Capture42.PNG?psid=1

Select the TestProjectDataSource option and then click Ok.

https://va9vsq.dm2301.livefilestore.com/y2pzvfArltm4aUSuWIcwPERoaWQft7g5pz_4uoyl85ftPkIjFLtIyhmXH-JLDjxNZCFjjbyuNYpVL6zoQ5zHqJum3V4lGyayiGwt4u1PLX4lXo/Capture43.PNG?psid=1

You will still see the 'Linked data source could not be found'. Ignore that error and click Ok. If you notice, the moment you click Ok. The error disappears.

https://va9vsq.dm2301.livefilestore.com/y2plQsfnnm-be78IkFopldZh3-LNcSv3Ko2p07orDgIrfdxlQOQEGuiItltRiFwZrTXzRPMmGPvFYlzgGG5QN08jKKAC3c2yrtZE8YssaAjdSk/Capture44.PNG?psid=1

Now go back to the Reports Library and click on the 'TestReport', you should see the page loading icon and then the report that was created. Below are screenshots.

https://va9vsq.dm2301.livefilestore.com/y2pTfOHQjjBybQNoUqSxBJFlWg_SHG3ekVSf93ALS4yaVX47aQSvvAAqgrrhww5Of35dTnNXVELSHeUkYDz90HAShApmYjPDl7WIzVG3ubSRek/Capture45a.PNG?psid=1

https://va9vsq.dm2301.livefilestore.com/y2p4oQ3y_7lYJ_RxC_1h1vckkLOqYMLj-aHY3tThq3Bd8VgcqbU6Kv1GI5hzL-QD9191gWU8EkBvBn6zcE_9qldmEatJI3BgENOI7ULTjV6Gic/Capture45b.PNG?psid=1

YOU HAVE SUCCESSFULLY SETUP, CONFIGURED AND TESTED YOUR SSRS SHAREPOINT INTEGRATION!!!

Conclusion

Since SQL's Report Services Configuration is no longer required, after installing the SQL Server Reporting Services application YOU DO NOT NEED to go to the Reporting Services Integration and add the 'Report Server Web Services URL'. Also it is not mandatory to install the SQL Server Data Tools on the SharePoint server you can use the existing one directly on the SQL server.

Throughout the article I have provided links. Below is where I have listed them again.

SQL 2012 SP1 Full Slip Stream
http://www.microsoft.com/en-us/download/details.aspx?id=35575

Install Reporting Services SharePoint Mode for SharePoint 2013
http://msdn.microsoft.com/en-us/library/jj219068(v=sql.110).aspx

Adding Reporting Services to the web front-end server of a SharePoint farm
http://msdn.microsoft.com/en-us/library/hh479775(v=sql.110).aspx

Adventure Works sample database
http://msftdbprodsamples.codeplex.com/releases/view/93587

Verify SharePoint Mode installation
http://msdn.microsoft.com/en-us/library/ms143773(v=sql.110).aspx

Share via