SQL Server 2017 Reporting Services: The Basics Part 3

Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

This is the Part 3 of our SQL Server 2017 Reporting Services (SSRS) Basics Series. You can find Part 1 here and Part 2 here. Part 3 covers report deployment.

You may remember that in part 1 we set up and installed the SSRS Web Service in the localhost http://localhost/reportserver. In our case, the hostname is the computer name (instead of the literal localhost).

You may want to check your SSRS Web Server URL on your Report Server Configuration Manager (you can find it in your start menu).

There are two ways of deploying a report:

• Visual Studio Project Deployment
• Manual Upload and Setup of the Report Files

Project Properties

In part 2, we created a report server project. Open the project on Visual Studio. Build the solution if you haven’t done so. Navigate to Build > Build Solution. You can also build via the context menu of the Solution in the Solution window (see the screenshot below for reference).

In the Solution Explorer window ( press CTRL + ALT + L if it’s not showing), select the project (we named it SSRSTutorial_001 last time). Right-click the project and select Properties in the context menu.

That will display the Project Properties dialog box. You can set up different Configurations depending on your target environment (e.g., Production, QA, Dev, UAT, etc). Let’s pretend we’re deploying this to Production so we choose Release as our configuration. Pay attention to the TargetServerURL property. Make sure that it’s the same as the Web Service URL in the Report Configuration Manager.

Project Deployment

Click OK. Select the Project again, right-click and select Deploy.

You may also deploy the individual components one by one.

A successful deployment message and other related info are now displayed in the Output window (press CTRL + ALT + O if it’s not showing).

Go to the Web Portal (http://localhost/reports). Check the Report Server Configuration Manager if you’re not sure of the URL. Click Browse to navigate to the deployed folders. You should have three folders containing the different components of our reports: Data Source, Dataset, and the Reports Definition Language (RDL) File. You can set the name of these folders in the project properties dialog box (see above). The properties are called TargetDataSourceFolder, TargetDatasetFolder, and TargetReportFolder respectively.

Manual Deployment

Let’s create three new folders in the Web Portal. Let’s name these folders with the same name above, plus the prefix term manual to distinguish them from the folders we deployed via Visual Studio.

In the Web Portal, click New and select Folder. Create three folders: Manual Data Source, Manual Dataset, and Manual SSRSTutorial_001.

Create The Data Source

Click the first folder, Manual Data Source, to open it. Click New and select Data Source. Name this data source as Manual_DS_SSRSTutorial_001. Select Microsoft SQL Server for the Connection Type. Input the following in the Connection String (you can basically copy this from the Data Source property of your solution in Visual Studio):

Data Source=./;Initial Catalog=WideWorldImporters

Leave the Credentials as is. Make sure to check the option Enable this data source. You may want to test your connection to make sure you’re connecting to the SQL Server. Click Create.

Upload the Dataset and Report

Click the Manual Dataset folder to open it. Click Upload from the menu. Navigate to your project bin files. In my case, that’s C:\Users\Marlon.Ribunal\Documents\Visual Studio 2017\Projects\SSRSTutorial_001\SSRSTutorial_001\bin\Release. Select the
Dataset file (ds_SSRSTutorial_001.rsd) . Navigate back Home in the web portal. Upload the Report file (rdl_SSRSTutorial_001.rdl) to the Manual SSRSTutorial_001 folder.

Set up the Dataset

Navigate to the Manual Dataset folder. Select the ds_SSRSTutorial_001 dataset or the ellipses on the top right-hand corner and select Manage. Select Data Source on the panel.

You get a warning that says, We can no longer find this data source. If it was moved, choose it from its new location. Click the ellipses under the warning. That will open a dialog box showing all the folders in the web portal.

Select the Manual Data Sources folder. Select the
Manual_DS_SSRSTutorial_001data source. Click Apply.

Set up the Report

Navigate back to Home and select the Manual SSRSTutorial_001 folder. Click the ellipses on the top right-hand corner of the report file. Select Manage. Select Shared Datasets from the panel.

You will also get a warning that says, We can no longer find this dataset. If it was moved, choose it from its new location. Click the ellipses under this warning. Select the Manual Dataset folder. Then select the ds_SSRSTutorial_001 dataset. Click Save.

Viewing the Reports

Navigate to Home. Select the SSRSTutorial_001 folder. Select rdl_SSRSTutorial_001. This contains the report we deployed via Visual Studio Project Deployment. Navigate back to Home. Now, select the
Manual SSRSTutorial_001 folder. This contains the report we set up manually. Select rdl_SSRSTutorial_001.

You should be able to view both reports.

And we’re done with the deployment part. We’ll set more properties in the future posts. For the meantime, stand by for the next installment of this SSRS Basics Series, basic parameterized report.