Skip to main content

Introducing SQL Server Reporting Services

In an earlier post I introduced the SQL Server umbrella and briefly discussed a number of products that make up the SQL Server stack. One of those products and a key element of the SQL Server family is SQL Server Reporting Services (SSRS). SQL Server Reporting Services is Microsoft’s enterprise reporting platform.


SQL Server Reporting Services compliments the other components that sit under the SQL Server umbrella. Your reports can be developed to present data from both the SQL Server relational databases and Analysis Services cubes. However it is important to note that the data source doesn’t have to be a SQL Server database or analysis services cube. It can be any data source that SSRS can connect too using OLE DB or ODBC connections. I have many clients who run other database platforms such as oracle but utilise SSRS as their reporting solution.

Competitor products and tools available on the Market that compete with SSRS include but are not limited to 
  • Crystal Report
  • Business Objects

The report life cycle can be broken down into three phases and depending on the job you do and what responsibilities you have will influence the phases in the life cycle that you are involved with. It might be you are involved in all phases end to end, in some sort of dev-ops capacity that is you write reports, deploy reports and manage reports.  However it’s not uncommon,, depending on your job role for you only to be involved in one or perhaps two of the life cycle stages. It might be that you are developer writing and authoring reports or DBA deploying and managing them.


Authoring

Authoring is the first stage in the reporting life cycle, after collecting user requirements including what data needs to be presented; in this stage you go about putting your report together. This will involve: 
  • Defining the data source – specify where the data to be included in the reports lives
  • Define the data set – write the query that will retrieve and extract the data from the data source
  • Layout the report define the look and feel

The resultant document is written in Report Definition language (RDL) which is XML document that contains the report metadata. Just a note no actual data is contained in the report. It's metadata on the report itself. The data is retrieved from the data source when the report executes.

There are two tools that can be used for authoring reports
  • Report Design – which is integrated into SQL Server Data Tools
  • Report Builder – which is standalone application
You can actually write in any tool that produces a correct RDL document

Management 

When the reports have been written they get deployed to a central reporting server, which will allow you to make the reports available to those users with permissions to view them. SSRS has a robust role based security model allowing you to lock down your reports and implement a robust security model. Once deployed there are many other management tasks that you might need to complete including setting up execution and delivery schedules

The tools involved in the management of SSRS include:
  • Report Manager – Which is a web application. This web application can also be used to view reports
  • Reporting Service Configuration tool which is used to manage the setup of SSRS

There are some command line tools too. RS.exe can be used to run scripts against the report server which allows you to automate and programmatically control things like report deployment and auditing.

Delivery 

How reports are delivered will very much depend on how your SSRS instance is installed and setup. If you are using Sharepoint integrated mode you can view and consume your reports on demand through Sharepoint. If you are using a native instance then you can view reports on demand through the Report Manager Web portal. Different report  formats are supported. You might have two users who consume the same report in different formats. One might have the report rendered in an excel format and another user might consume the report in pdf. There are several report formats available ‘out of the box’. User can also setup subscriptions to reports allowing them  to automate the report delivery. E-mail is probably the most common type of report delivery mechanism but a file-share location can also be used a second delivery option.

This is a short introduction to SQL Server Reporting Services. Learning Tree course 140 provides a hand-on look at reporting services from report authoring through to deployment and management. We have done a great deal with Learning Tree and we are offering seats on the 140 SSRS course running at the London Education Centre staring on the 2 August at just £750 plus VAT. If you are interested please email training@gethynellis.com for more information





Comments

Popular posts from this blog

SQL Server 2012 and Virtual Service Accounts

This post is written by David Postlethwaite
If you are using SQL Server 2012 you will probably have noticed that the default account for the SQL services has changed from that used in previous versions. With SQL 2005 and 2008 the default account for SQL service and SQL Agent service was “NT Authority\System”. This is one the built in accounts on a Windows machine, managed by the machine and selectable from a dedicated dropdown list

The Network Service account was introduced in Windows 2003 as an alternative to using the LocalSystem account, which has full local system privileges on the local machine, a major security concern.
The Network Service has limited local privileges easing these security concerns but when many services on a machine use the Network Service account it becomes harder to track which service is actually accessing resources and performing actions, because all the services are using the one Network Service account.
Also, this account, by default, has sysadmin per…

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also hav…

How to Setup Kerberos Correctly

David was in Copenhagen this weekend delivering his Kerberos talk Taming the Beast: Kerberos for the SQL DBA to SQL Saturday Denmark. I have had a quick chat with him via email since he got back and he said he had a great time. The event was very well attended with 280+ attendees and his talk was well attended.

I think David is planning submitting a few sessions to SQL Saturday events in Europe in the next few months so look out for him there and we'll keep you posted as to his whereabouts when schedules get finalised later in the year.

David has pre-recorded his Kerberos talk. You can watch on you tube and I have also embedded it in this post if you want to see what his kerberos talk  covers...



If we can help you with a SQL Sever problem visit our SQL Server Consulting page or contact us