Reporting Services, part of SQL Server, is very useful for producing management reports based on SQL data. These can be formatted to suit, can be produced on a schedule and can be exported to various formats.

Setting up the report designer requires a few things:

User Pre-requisites

The person who will be designing reports needs to have a basic (or more advanced) understand of how to build SQL database queries. Unless someone is going to create the datasets and leave the report design to the user, they cannot produce a report without either creating a stored procedure or otherwise producing some kind of SELECT.... Once you add report parameters and grouping/sorting, there is a lot of scope for flexibility but also additional learning requirements for the user. If the user is from a database or general programming background (including databases) there should not be too much problem but if the user is not, this stage might take a long time and include many, "Can you help me..." emails!

Ideally, the user should be familiar with Visual Studio since the report designer is mostly based around standard Visual Studio commands and setup. People who are not used to it might feel very intimidated by the number of menus and commands in Visual Studio even though not many of these are needed.

Since there are a number of technical steps, it is helpful if you know the user can find, download and install the software they need, otherwise this would potentially be a 2 hour or more assisted piece of work! As an admin, you will still need to setup the user and permissions information before this person can access the report server.

Software Pre-requisites

There are actually a number of ways to produce reports, including some third-party tools but I am recommending SQL Server Data Tools which is a module for Visual Studio and which comes from Microsoft for free (you need a licence for your report server however). This is partly because I know it works and is supported by MS but also because I am more familiar with it. It can be downloaded from here

Permission Setup

You should setup user permissions on the report server for each user, or possibly at group level depending on how many people you have. You should NOT just share credentials across multiple users. If you need to lock someone out or change the credentials, you do not want to have to manage this across multiple people. It also allows you to track who has changed something when it is done incorrectly.

To do this, you need to setup a top-level site permission (admin or user) and then add item-level permissions, although the item-level permissions are inherited down into folders so you can set this once at the root level if desired. You might also have to consider who can access what with regard to data sources etc. to avoid someone accidentally overwriting one.

To set the site-level permissions, choose "Site Settings" in the top-right of the reports URL:


Then choose the Security tab on the left and add your new user. If this is not a domain account that already exists, you will need to create a local account on the report server itself with a relevant password that the user will need to type in when they visit this site. Choose whether they will be an admin or just a normal user.

Once this is done, go back to the home (top level) directory and assuming you want to apply permissions from here, click the Folder Settings button in the middle:


And again add the user to the list in security with a relevant role to what they are doing.

Setting up the Software

You might be sharing a project, using source control, across multiple users, in which case you should just be able to open this, make your change and deploy directly. If not, however, you will need to create a new Report Project and setup the relevant endpoints in your project properties to point to your server. The defaults should all be OK but you want your TargetReportFolder to point to /Reports/ (by default) and your TargetServerURL to point to http(s)://yourserver/ReportServer (by default).

Once this is done, you can create a new test report and attempt to deploy it to check that it is all setup correctly. Before deploying, you can also visit the site in Internet Explorer (http(s)://yourserver/Reports) in order to check that your login credentials work and you can access the existing reports.