Freelance SSRS Reports Developer

I started developing SSRS reports when it was first included as part of SQL Server 2005. It’s a product with tremendous range. You can create simple reports very quickly, or build more elaborate custom solutions. Plus, you can harness amazing results with data driven subscriptions. Best of all, it’s free with Microsoft SQL Server.

If you know very little, or nothing about SSRS, there is a very good introduction by Microsoft: What is SQL Server Reporting Services. Alternatively, I’m happy to help you get up to speed on SSRS, too.

If You Already Know About SSRS

I don’t want to lose your attention by getting long-winded on SSRS basics. This page goes over some development tasks toward creating an SSRS report. If you are interested in seeing examples of my work, please go to the following link:

https://majorleaguereports.com/freelance/ssrs-developer/sample-reports/

If you know enough already, and you’d to talk about your specific needs then call me or send me an email:

Email: chris@majorleaguereports.com
Phone: 407-865-4940
Zoom: meeting ID upon request

Basic Parts of an SSRS Report

If we’re going to work together, I think it helps if you are oriented to the important, basic parts of an SSRS report. Here they are:

Data Source
Data Set
Report Designer
Parameters

I develop reports using Visual Studio, and each of those four parts are shown in the editor. Here is a screenshot:

Data Source: in the left pane, red arrow. The data source is a connection to a database on a SQL Server (in this case), or to other similar sources like Azure SQL or SSAS Server. You can have multiple data sources in a report.
Data Set: also in the left pane, highlighted yellow. The data set is the query of the data source. You can have multiple data sets in a report.
Report Designer: in the middle, highlighted green. This is a WYSIWG designer that enables you to place elements from the data set onto a report.
Parameter: in the left pane, blue arrow. Parameters allow you to do things such as filter a query down to a certain value, and to determine what that value should be at the time the report is run.

In this very basic example of an SSRS report I made a connection to my data source, and I wrote a pretty basic SQL query. I prefer to write SQL code in another tool (SSMS) and then I paste it into the SSRS data set designer. This example uses a Microsoft sample database called “ContosoRetailDW” which is a very simple data warehouse. All my query does is get a list of customers, their state, and the dates of their first and last purchase. This is not a typical use of data warehouse data, but it’s simple for the purpose of explaining SSRS.

SSRS Reports Manager

SSRS reports are often deployed into the SQL Server Reports Manager. It gives you a very useful web interface to SSRS reports. There are other ways to deploy SSRS reports, but this is the most common and fits our example.

This is what the example looks like when you run it from the SSRS Reports Manager. Granted, it’s a very simple example, but all of this took me less than ten minutes from creating the new solution in Visual Studio, to creating a connection, writing the query, and finally deploying it.

Using Parameters in SSRS

In the example report, you can see it is not filtered. It shows all customers, and all states. The data contains about 8000 customers, and that makes this report less than ideal. A better report might be to filter the customers by state. For instance, if you have a sales person assigned a territory of a specific state, sales people can pull up only the state they want. This is achieved by using parameters.

When you’re using parameters in reports, it’s helpful to limit the values that users can request. Case in point, if a user runs our report and types in “CA” for California, but the database stores the full string, “California,” then it will return no records. Therefore, in order to increase the usability of the report, we can write a query to get all the values of states where there are actual customer records. Using the existing example, I added a parameter and created such a query, as shown below:

I’ll explain how that works. First, I created a new query called, “ParameterStateProvinceName” and then I added a new parameter called “StateProvinceName.” Both of these objects are in the left pane, highlighted yellow and gray, respectively. After creating the parameter, I am then able to give the parameter Available Values that come from the query. I wrote the query to produce a list of states with customers, each state appearing only one time in the list. Now when I run the report it gives me a chance to show only customers from the selected state:

You can see the contents of this report no longer shows customers from Arizona which had previously appeared at the top of the list. When I ran the report, I chose “California” for the parameter value (see the blue arrow, above).

SSRS Subscriptions

Lately SSRS doesn’t excite people like it used to. The visualizations you can create in Tableau are far beyond the capabilities of SSRS. Plus the breadth of Power BI, Microsoft’s newer reporting and dashboard solution make SSRS appear basic in comparison. However, SSRS has tremendous power with subscriptions and this alone makes it still a worthy platform for reporting.

There are two types of SSRS subscriptions:

Standard SSRS subscriptions
Data driven subscriptions

Rather than define each one of them, I’ll compare and contrast. First of all, the data driven subscriptions require SQL Server Enterprise Edition. That costs extra, and not everyone has it. Data driven subscriptions, however, provide a great incentive to pay extra for Enterprise.

In a standard subscription, I can subscribe to the report I showed you, and my subscription will always deliver to me, the one report I ask for, with the parameter value that I choose when I set it up. For instance, I can get the list of California customers every month (or other frequency). If I get assigned another territory, I’ll need to change my subscription.

In a data subscription, we can setup a single subscription that sends out the list of customers to the sales representatives who are assigned each state. This can become very powerful. I’ll show you an example of one, but first I’m going to define a new dataset to drive this subscription. I need a set of sales reps, their email addresses, and each rep’s territory, like shown here (SQL query highlighted yellow):

This example is simplified for the purpose of demonstration. It isn’t very realistic data — having exactly one rep for each state, but it should get the point across. After defining the dataset (query), we can use the fields in the subscription. Here is how that comes together in the SSRS Reports Manager:

Each record in this query result will become a subscription delivery. This data driven subscription is only one subscription, but the query results define multiple deliveries.

The subscription delivery options then allows you to plug in the values from the query. In the highlighted boxes, the Email To will be filled in by the SalesRepEmailAddress, and the report parameter Customer State will be filled in by the Territory. Both of these parameters are filled in by the dataset (the query) that was defined for this query.

Summary – SSRS Capabilities

This overview provides a simple tour of SSRS reporting capabilities. The point is to introduce some of these capabilities because they comprise much of the work that I do for my clients. I am happy to explain things in greater detail as it pertains to your data and your reporting needs.

If you would like to see some examples of the work I have done in SSRS, please look at the following page of example SSRS reports:

https://majorleaguereports.com/freelance/ssrs-developer/sample-reports/

If you are not ready to contact me because you are weighing other options, then you might be interested in how I compare against other BI developer options:

https://majorleaguereports.com/chris-adragna/competition/

Please email or call, and I’ll be happy to discuss your needs.

Email: chris@majorleaguereports.com
Phone: 407-865-4940