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…

New in SQL Server 2017: Graph Databases

David has recorded and published a video of his presentation on SQL Server Graph Database. In his video which you can watch below, David provides an excellent introduction into SQL Server 2017 Graph Databases. In his presentation he looks at Tennis results at tournaments for  his favourite player "The Fed"  Rodger Federer.

David  shows how to set up graph database and work with them in SQL Server 2017.

Graph Database is not new. Other vendors have had graph database capabilities for some time so Microsoft are quite late to the market. In David presentation it appears that Microsoft have done a reasonable job of implementing some of the graph database features but he does point some of the limitations of the Microsoft product too and suggests that it is not ready for production yet but Microsoft seem serious about this feature.

Please watch the video and feel free to leave a comment or feedback - David is delivering a version of this talk on Graph databases in SQL Saturday Ka…