Not a month goes by without some shiny, new, all-singing, all-dancing business intelligence product being announced as the Next Big Thing. A few months later, no one can remember it ever existed, except your CEO, who wants to know what her organization is using to deliver corporate business intelligence (BI). She wants a solid, stable, and mature platform that can deliver flawless reports to users automatically and regularly. She does not want her staff wasting time developing their own reports and learning corporate data structures. Secretly, many users agree with her. She does, however, want a certain level of interactivity. Of course, she expects BI on mobile devices and a tangible level of “wow factor.” At least, that is what you thought she said, before your eyes glazed over in fear as you wondered which product was going to be forced on your lovingly engineered SQL Server universe.

The product quite simply could be SQL Server Reporting Services.

SQL Server Reporting Services (SSRS) has been around for over 15 years. Longevity like this, fortunately, can have its advantages, and in the case of SSRS they are the following:

• SSRS is a mature, stable, and efficient platform.
• SSRS can accept data from many different sources, most often relational as well as dimensional and tabular.
• SSRS handles the details of scheduled report creation, snapshots, and automated report delivery with consummate ease.
• SSRS slots into a complete SQL Server BI stack efficiently and smoothly.
• SSRS reports can now also be made available in the cloud using Power BI.

Often underestimated, SQL Server Reporting Services is an extremely powerful environment for delivering clean, clear, and polished reports to users. If you add a final layer of BI polish to the mix (and that is what this book is all about), then you have an incredibly wide-ranging and effective business intelligence tool at your disposal.

SSRS is not, however, designed to be a self-service BI tool. It exists to deliver corporate BI, where prebuilt reports are what the user wants. Indeed, in this world the user specifically does not want to develop his or her own reports. They may want to change some core parameters or drill down or across through data, but they definitely do not want to spend (they might even say “waste”) their time formatting their own reports. These users prefer to have an MI (Management Information) department prepare their reports for them.

So, if self-service BI is what you are looking for, then Power BI is probably a place to start, and my book High Impact Data Visualization with Power View, Power Map, and Power BI (Apress, 2014) should be your first port of call.

However, if you are looking at delivering pre-built, polished, and interactive business intelligence to your users, where timely, accurate, and attractive information delivery is the major requirement, then SSRS is probably the tool that you need. It can provide the following:

• KPIs
• Scorecards
• Dashboards

These features can be delivered to schedule or displayed on demand. The data can be updated according to your business requirements. Not only that, but SQL Server Reporting Services, if you are using SQL Server 2012 SP1 or later, can deliver your BI not only to browsers, but also to smartphones and tablets.

While users cannot generate reports, and slice and dice the data as they can in the self-service world, they can

• Set multiple parameters to define the data that will be displayed.
• Drill down into report hierarchies.
• Drill across to other reports

This book exists to show how you can use SSRS to provide corporate business intelligence that delivers all that is described above, plus add the “wow” factor that your boss wants. The trick is to learn how.

Business Intelligence Concepts

As this book is about delivering business intelligence using SQL Server Reporting Services, it will help to clarify a few basic concepts from the outset. When delivering corporate BI, you will mostly be developing key performance indicators, scorecards, and dashboards. Let’s begin by defining these as succinctly as possible.

Key Performance Indicators

Key Performance Indicators (KPIs) have been an essential part of business intelligence for many years. They display a set of selected fundamental measurements that managers use to make productive decisions. A classic KPI will probably contain the following:

• A value: This is the figure returned from a business system.
• A goal: This is the figure that should have been attained, and comes from a budget document that has been loaded (somehow) into an accessible data source.
• A status: This is nearly always shown as a symbol that indicates how aligned the value is to the metric (in other words, how well you are doing). In some cases, it can be the color of a display element.
• A trend: This shows how the metric being measured has evolved over time and it can be displayed as a symbol or a small graph called a sparkline.

Put simply, a KPI will let you display a result, how it maps to target, and where it is going. It lets you see anything that can be measured that is of strategic or tactical importance.

Scorecards

Scorecards are collections of KPIs. They might all relate to the same area of business, or give a view across multiple business areas. Scorecards might themselves become part of a dashboard.

Dashboards

There are many overlapping–and sometimes conflicting–definitions of what makes up a dashboard. I have no intention of getting caught up in arcane theoretical disputes, so I will consider a dashboard to be a visual overview of essential corporate data. It can display many possible data snapshots of multiple aspects of a business, or show a highly specific set of metrics relating to a business area or department. I consider that it can include targets and objectives, but that these are not compulsory.

A dashboard can be made up of multiple elements, which many people also call “widgets” or visualizations. SSRS lets you use the following as the building blocks of your dashboards:

• KPIs
• Scorecards
• Tables
• Charts
• Gauges
• Sparklines
• Maps
• Images

These core elements can often be combined to produce multiple variations on a theme. How they can be added to reports and combined to deliver BI is the subject of the book.

SSRS for Business Intelligence, Practically

Now that you have had a rapid overview of the theory, it is time to get practical. You need to see more generally how SQL Server Reporting Services can deliver BI elements to your users. Specifically, you need to see what you will be doing when developing your BI solutions.

With SSRS for Business Intelligence, you will be developing the following:

• Reports: This is a more generic term that I use to describe an SSRS .rdl file, whatever the type of presentation it contains.
• Visualizations: BI dashboards, scorecards, and reports are normally made up of several elements. They could be charts, gauges, tables, maps, images, or combinations of these elements.  I use the term visualization to describe a single element, even if it is composed of different elements from the SSRS toolbox. For instance, a KPI containing a sparkline, some metrics, and an indicator will make up a single visualization.
• Widgets: A widget is any element (or object) that can be stored independently and reused. In most cases it is really only a synonym for a visualization.

Delivering BI is not just a technical matter. It involves the following three basic threads that have to be melded into a final delivery:

• Data
• Design
• Platform

Let’s take a look at these three.

Getting the Data Right

Making sure that the data is perfect is the key to delivering effective business intelligence. The data must be not “good enough,” not “nearly right,” but absolutely one hundred percent accurate. Obviously, this book is not the place to discuss data architecture, data cleansing, data quality, master data, or the definition of metrics and processing for BI. However, in the real world, you will have to deal with all of this.

As we are only dealing with the presentation layer in this book, I will simplify the learning curve by using a single database whose data we will presume is perfect (and thus ready for use in SSRS) to underpin the visualizations and dashboards that you will be creating. I will also make this single database the repository for all ancillary information that you may need when developing your outputs. By “ancillary” I mean data such as

• Budget data, used in KPIs, for instance
• Geographical data, used when creating maps or analyzing data by country or region
• Any other data that is not habitually sourced from an OLTP or OLAP system, such as certain types of reference data

However, having the data accurate and accessible is only the first part of the story. How you then present the data to SSRS is also fundamental. There are several aspects to this.

Prepare the Data

As you could well be sourcing data from multiple tables (for instance, you could be aggregating sales data from one table or view and comparing it with budget data from another set of tables), I generally advise that you mash up the data in the database before you present it to SSRS. There are several advantages to this approach, compared to filtering, joining and calculating data in SSRS itself:

• You are using data from a database, so it seems more logical to use the unrivalled power of the database language (T-SQL) rather than the reporting tool. SSRS is necessarily more limited where data manipulation is concerned.
• The heavy lifting takes place where the data is kept: in the database.
• SQL Server provides an amazing toolset to help you collect, rationalize, and output data. So do not be afraid to use temporary tables, CTEs, table variables, and all the other tools in the T-SQL toolkit to shape your data.

So what should your aims and objectives be when preparing datasets in T-SQL? Everyone has their own opinions, but I suggest that you take the following points into consideration when setting up your data for reports:

• Output only the precise data that is needed by your report. Extra rows and columns “in case they might be useful one day” slow down report processing and weigh on network transmission needlessly.
• Similarly, your T-SQL may include intermediate columns used for calculations. Often these are not displayed in the final report, so you may not need to output them to SSRS. They will likely only cause confusion when you (or your team) are developing or maintaining the report.
• Do not make code re-use into a target. Dashboards and reports change all the time, so trying to develop “one size fits all” stored procedures or scripts that underlie a group of reports can be a waste of time. Be prepared to create a stored procedure for each different visualization. This way, when the requirement for a minor change comes in, you will not have to worry about its ramifications across a whole range of dashboards. Alternatively, in the cases where you have a clear group of reports that are all based on the same data, reusable stored procedures can be a boon.
• Do document your code to remind yourself of any common elements across stored procedures. This way you know that if you change something for one chart (say), then you will need to modify other stored procedures too.
• Try where possible–and this may be a contentious point–to provide all the required data for a visualization in a single data set. This may seem obvious, so let me explain further. Some visualizations require data that is not a metric, such as the maximum for a gauge pointer or the thresholds used in ranges. These are only single, scalar figures. However, it can be easier sometimes to add them as columns to a dataset (even if the same figure is repeated identically in every record) and use them as the source data. This often only adds a few columns to a datasource, and has the advantage of keeping all the data in the same place for a visualization, and not cluttering up the report with added datasets containing only one or two values.
• Sometimes, however, you may want or need to reuse data such as thresholds or maximum values in different visualizations in the same report. In this case, do use separate datasets in a report to access these elements.
• Remember that in some cases (the second KPI in Chapter 2 is an example) you will have to create several datasets for a single visualization. Experience will tell you when you have to use multiple datasets and subsequently link them inside SSRS to produce the required result.

Of course, you may disagree strongly with these ideas. Feel free to do so; the essential thing is that you have thought through the reasons for how and why you have set up the data in the way that you have chosen. If you can justify them to yourself, then you can insist on them for your team–and defend your decisions to your boss.

Use Views and Stored Procedures

While it is perfectly possible to custom code every piece of SQL (or MDX or DAX) that feeds data into SSRS, it is frequently a much better practice to aim for some reusability. This nearly always means adding views and stored procedures to your data layer.

Views

It can often be worth the effort to prepare views that you can use and re-use as a conformed source of source data. You might be able to use views directly in certain reports, or (more likely) use them as the basis for specific processing and output using a stored procedure. If you will repeatedly be joining tables or aggregating data, then a view will save you a lot of repetitive coding. Of course, in most situations you will never really know what data you will be using before you start building visualizations. However, it can be worth refactoring your initial SQL queries into views earlier rather than later in the project lifecycle.

Stored Procedures

SSRS will let you use both T-SQL code blocks and stored procedures to assemble and deliver data as a dataset. However, I am a firm believer in using stored procedures rather than freeform code blocks in SSRS. My reasons include the following:

• Data manipulation is centralized in the database. You will not spend time opening multiple reports and then digging through datasets and scrolling through code in a tiny dialog in SQL Server Data Tools.
• Parameters are created automatically and correctly when you specify a stored procedure as a data source for a data set.
• Large and complex processes delivered as stored procedures will not exceed the character limit of the SSRS code box.
• You work using a suitable UI, rather than trying to code in a text box or (possibly worse) copying and pasting back and forth between SSRS and SQL Server Management Studio.
• You are a data professional and so probably feel happier using a more structured and coherent method of working.
• You are minimizing the risk of SQL injection attacks.

Some Ideas on Source Data Definition

I believe strongly that preparing the data for a visualization is a fundamental part of successful BI report creation with SSRS. This is why for every example in this book I always not only give the code used to deliver the required data, but show sample output and then explain the reasoning behind the T-SQL. I do this to draw the reader’s attention to some of the many ways in which you can prepare data for BI reporting. As you will see, I tend to use temporary tables and CTEs quite regularly. I also tend to break down separate logical elements into small elements of code, rather than aim for monolithic structures. These are personal choices, and all I want to do is to provide some hints and suggestions for code use–and re-use. How you write your code is your choice. All I can suggest is that you try to make it as efficient as possible.

There are many design decisions that you will have to make when defining the datasets that feed into your reports. You may prefer to create many datasets–possibly not only one, but several for each widget. Alternatively, you might try to cram everything into a single datasource for all the elements on a dashboard, and then apply filters to apply only the relevant data to specify charts or gauges individually.

My approach is usually to find a common ground between these two extremes. In most cases, I find that a data source can be created (and subsequently filtered) for associated groups of elements–gauges, charts, or KPIs that make up a visualization. Sometimes an element may need several datasets. In yet other cases you may need multiple datasets because you are using disparate data sources.

Anyway, in this book you will find examples of most of these approaches. This will not mean that a certain way to prepare the data is best suited to a certain type of visualization, just that I am taking advantage of a scenario to show some of the ways that datasets can be used–and combined–in SSRS.

On a more general note, I find that spending time defining the data requirements at the start of a project–or even for a single BI element–can help me to focus on what each visualization is trying to achieve. It can also save a lot of development time as you will minimize the number of times that you modify your T-SQL code, and subsequently add, rename, and delete columns of data in SSRS datasets and objects. So I can only advise you to think through the data before you start building a chart, gauge, or map. To encourage good practice, therefore, I begin every example with the model that you are aiming to build and the code needed to produce the data that is required. This way you can see what is required for each visualization, why you need it, and what is, in my opinion, the best way to deliver it.

Of course, my SQL programming style may not be the same as yours. After all, we all have our own way of delivering BI to our users. So I am not saying “this is how you must do it,” but merely “here are some ideas based on my experience.” How you prepare the data for your dashboards and reports is completely up to you.

Real-World Data

In the real world, you will use data from many different sources in a variety of formats. SQL Server might not be the only relational database you are using. Relational data may not be the only data format; you could be using dimensional data from a SQL Server Analysis Services database or even data in the Analysis Services tabular format.

SSRS will allow you to use data from these varied platforms easily and simply. However, I decided not to use all these potential data sources in this book and stick to SQL Server relational data for the following reasons:

• Not everybody is using all the potential data sources that SSRS can handle.
• Many users are likely to be federating data in a SQL Server reporting layer (using techniques such as linked servers or ETL processes using SQL Server Integration Services).
• All readers are likely to know T-SQL, which makes it a comprehensible lingua franca for explaining how data is produced.
• Using SQL Server as the data layer means that readers can concentrate on SSRS rather than getting distracted by other languages such as MDX or DAX.

However, preparing the data is not the end of the process. You need to be able to reuse data in certain circumstances, and almost certainly you will need to cache both data and reports to ensure that your users enjoy a top-class BI experience. The final chapter of this book will outline some of the techniques that can help you enhance the user experience and deliver output smoothly–and quickly.

Designing SQL Server Reports for Business Intelligence

Once your data is in place, you can move on to designing the output. This is admittedly the fun part, and is often where the real challenge resides. You need to remember this, especially when faced with seemingly impossible demands from management or users, or apparently insane demands for stylistic pirouettes from the Corporate Style Police.

Presentation

Your first choices will inevitably concern the presentation approach that your reports will be taking. They could be

• Text-based
• Graphical
• Hybrid

You could be aiming for a traditional look and feel, or attempting something different. The choice is yours, as long as you remember that variety should not mean distraction, and novelty can wear off faster than you think.

Design

This is the area where your choice of backgrounds, borders, images, and text will come into play. Color and style are vast areas, and ones that are probably best left out of the hands of technical people like me. Nonetheless, a few comments are necessary.

The French have a saying that translates as “there is no discussing taste and color.” This usually means that everyone knows that they (and only they) are right in matters of good taste–especially where report design is concerned. Moreover, any discussion with these people is pointless as it is clear (to them, at least) that they (whoever they are) are right. I agree: they (or even you) are always right. Or more probably, your boss, or another department somewhere in the organization, is inevitably right when it comes to the choice of colors and report presentation generally.

Consequently, to placate these people, I have adopted a color scheme that avoids garish colors and uses shades of gray for text and borders. This approach lets the information speak louder than the color scheme. You, and your internal clients, may prefer other color palettes and design styles, and this is up to you. The steps describing color and style are only there to indicate how to apply colors, not necessarily the color that you must use.

In some cases, I have thrown these principles out of the window, as style can also be a function of the delivery method. So, specifically when defining output for smartphones, I tend to use bold primary colors or even white-on-black displays. This is because on a phone a competing distraction is only a swipe away, so I want to grab the user’s attention.

One thing that you will have to make abundantly clear to your users–and in-house style gurus–is that SSRS is not a design application. It cannot deliver the kinds of currently fashionable interfaces that certain other products can. What it can do (and this is the mantra you will need to repeat) is deliver BI in a fraction of the time and cost of many of the alternatives. Not only that, but it integrates perfectly into your SQL Server ecosystem, does not require expensive training, and is easy to maintain. Oh, and you will not need the licensing costs of third-party software. Or the extra consultants to get it up and running. Or to explain exactly why all of your reporting infrastructure needs to be replaced, yet again.

Layout

Having admitted that there are limitations to what SSRS can deliver visually, I now want to compensate by saying that it can nonetheless produce some really slick and impressive output. To get the most out of SQL Server Reporting Services to deliver visually compelling BI, you need to know a set of techniques–and a few tricks–that help you get the job done. These include

• Aligning visualizations
• Enclosing objects in other objects
• Controlling the size and growth of objects
• Applying images as backgrounds to many objects

Ideas for these aspects of report creation are discussed in Chapter 7.

Interface and Interactivity

You probably do not need me to remind you that the SSRS web interface (Report Manager) will never win any prizes for ergonomics. With a little effort, however, you can revamp and even replace this tired old UI to give the user a more pleasant experience. This revamping also covers ways to enhance the (admittedly limited) interactivity that is on offer.

This revamping is mainly based on using the following features:

• “Postback” and hidden variables: This means using the Action property of an item on a report to re-display the same report where some parameters are changed and others kept the same.
• SSRS Expressions: Expressions are the key to enhancing most aspects of SSRS, and interactivity is no exception. You will see many examples of expressions in this book. Fortunately, you do not have to learn an entire programming language, as only a handful of Visual Basic keywords are essential when revamping reports. So, if you are prepared to learn to use If(), Switch(), Lookup() and one or two others, then you can bring the SSRS interface into the 21st century.

Chapters 8, 9, and 10 will show you some of the techniques that can be applied when revamping the report interface.

Multi-Purposing

Many of the techniques and most of the widgets that you learn to build in this book can be used in a plethora of circumstances. Just because you learn to apply a technique to tablet BI does not mean that it cannot be used effectively in reports that are read on a laptop. Certain techniques explained in the context of output for smartphones can be used on a PC. So please do not think that just because I may explain an approach to business intelligence delivery in the context of a certain output device that you can only use it on that specific device. Indeed, I encourage you to experiment and to discover which techniques work best in which circumstances and on which devices.

The Sample Database

The sample database that is used throughout this book as the basis for all reports contains the sales data for a small English car reseller called Brilliant British Cars Ltd. It has been going since 2012 and now exports a small set of English luxury and sports cars to Europe and North America.

Preparing Your Environment

If you intend to follow the examples in this book, you will need to set up a SQL Server Data Tools (SSDT) environment that is correctly configured so that you do not waste any time and can create your own version of the reports that are detailed in the various chapters. If you have copied the CarSalesReports solution from the Apress web site and installed it as described in Appendix A, then you already have an environment that contains all the examples. However, as it can be fruitful to build your own reports from scratch, here is how to set up and configure an empty SSDT project.

Creating an SSDT Project

The first thing to do is to create an SSDT project.

1. Run SQL Server Data Tools (Start All Programs SQL Server 2014 SQL Server Data Tools).
2. Click New Project.
3. Click Report Server Project. Set the name and select a location such as C:BIWithSSRS. Uncheck Create directory for solution.
4. Click OK.

Adding a Shared Datasource

You now need to add a shared datasource that connects to the source database. I am presuming that you have downloaded and restored the CarSales_Reports database as described in Appendix A.

1. Display the Solution Explorer window (Ctrl+Alt+L).
2. Right-click Shared Data Sources and select Add New Data Source from the context menu.
3. In the Shared Data Source Properties dialog, enter the following:
   1. Name: CarSales_Reports.rds
   2. Type: Microsoft SQL Server
   3. Connection string: Data Source=YourServerYourInstance; Initial Catalog= CarSales_Reports
4. Click OK.

Add Shared Datasets

You now need to add the shared datasets that are used in the examples in this book. Before adding them, it is best to know what they are and what they do.

• CurrentMonth: This gives the current month that is passed as the default to the ReportingMonth parameter of most reports.
• CurrentYear: This gives the current year that is passed as the default to the ReportingYear parameter of most reports.
• ReportingFullMonth: This gives the month names and month number. It is used in a couple of reports to display the full month name as a report parameter.
• ReportingMonth: This gives the months in the year. It is used in the ReportingMonth parameter (found in most reports) to allow the user to choose the month for which data will be displayed.
• ReportingYear: This gives all the years for which there are data in the database. It is used in the ReportingYear parameter of most reports to allow the user to choose the year for which data will be displayed.

You add a shared dataset like this, using ReportingYear as an example.

1. Right-click Shared Datasets (in the Solution Explorer window) and select Add New Dataset from the context menu.
2. Enter a dataset name (CurrentMonth in this example) and set the Data source to CarSales_Reports.
3. Set the Query type to Stored Procedure.
4. Select (or paste) the stored procedure Code.pr_ReportingYear as the stored procedure name.
5. Click OK.

You can now create the five other shared datasets, using the following properties:

Configuring Parameters

Nearly every report used in this book will use two parameters to choose the month and year for which data is displayed. These parameters are

• ReportingYear
• ReportingMonth

Here is how to add them to a report.

1. With a report open (or freshly created) right-click Parameters in the Report Data window and select Add Parameter from the context menu.
2. In the General tab, add the parameter name (ReportingYear, in this example).
3. Set the data type to Integer.
4. Click Available Values on the left.
5. Select Get values from a query.
6. Set the following:
   1. Dataset: ReportingYear
   2. Value field: ReportingYear
   3. Label field: ReportingYear
7. Click Default Values on the left and set the following:
   1. Dataset: CurrentYear
   2. Value field: CurrentYear
8. Click OK.

You can now do exactly the same for a second parameter named CurrentMonth. The only differences are that the Dataset, Value field, and Label field will use ReportingMonth for the available values, and the default values will use CurrentMonth for the dataset and Value field.

You can now use this project to build the examples from this book without any risk of overwriting the examples in the sample project. Indeed, you can then compare your work with the sample report files in the sample project, or even copy and paste items between projects if you want to speed up the learning curve.

When you are following the examples in this book, you will see that I am giving each SSRS report the name of the example that you can find in the CarSalesReports project. This way you can compare your work with the model that was used to produce each image that you can see for each example. You can name your reports anything you want, of course.

Code and Stored Procedures

All the code examples in this book are shown as stand-alone code snippets that you can run as-is in an SSDT query window (assuming that you have downloaded the CarSales_Reports database from the Apress web site, restored it to your computer, and are running the query against this database). When it comes to creating the actual reports, however, I will presume that you have either made the code into a stored procedure, or are using the referenced stored procedure from the CarSales_Reports database. As I am assuming that you know how to create and use stored procedures I will not be explaining how to tweak the code to add a ‘sproc header.

Reusability

BI reports and dashboards can be immensely varied. They can use a multitude of different types of visualization. Not only that, but each type can vary in a myriad of subtle ways.

One of the objectives of this book is to provide you with a starter kit of reusable widgets that you can then tailor to suit your specific requirements. These widgets are not, however, designed to be generic. They are designed to illustrate the techniques that can be brought to bear to solve BI reporting challenges using SSRS.

However, you should be able to adapt most of the widgets described in this book, or taken from the source code download page on the Apress web site, to your own requirements. One hint that I can give you is that if you need to change, for instance, field names in a widget to suit your data, the best approach is to follow these steps.

1. Display the Solution Explorer window. Ctrl+Alt+L is one way of doing this.
2. Right-click the report file that you want to modify and select View code from the context menu.
3. Select Edit Find and Replace Quick Replace.
4. Enter the text that you want to find and the replacement text. This could be a file name, for instance.
5. Click Replace All.
6. Save the file and close it.
7. Reopen the report normally.
8. Preview and test the report.

If all has gone well, you will have taken a major step towards adapting the widget to your specific requirements.

I hope also that the code samples will be reusable and customizable to some extent. Obviously, they cannot cover more than a fraction of the needs of a reporting system, yet many of the overall approaches that are taken can hopefully provide you with some ideas and prototypes.

The CarSales_Reports Database

If you are following the examples in this book, you must install the CarSales_Reports database as described in Appendix A. This database is deliberately simple, so that you can concentrate on building dashboards and BI reports rather than struggle to understand an overly complex mound of data.

As a nod to the real world, the source data is in a series of relational tables, but the reports source their sales data from a view over the OLTP tables. This avoids you having to create the same joins again and again, as well as reminding you that reporting data is often prepared in ways like this.

The sample database also contains three user-defined functions that round up output. They are used for charts and gauges to set the upper limit of scales in many cases. The reason for this is that if you set a scale limit to a “rounded” figure, the scale increments (and gridlines and tick marks, if appropriate) will be much easier to read. If you need to adapt these functions to set other increments to round up by, you can use them as a starting point.

Book Audience

This is not a book for total SSRS novices. If you are using this book, then I am presuming that you can already create reports and are at ease with the concepts and practice of

• Data sources
• Datasets
• Tables
• Matrixes
• Basic charts
• Images

You will need a basic familiarity with these elements because BI with SSRS requires you to build on these foundations and to add

• Gauges
• Complex charts
• Sparklines
• Indicators
• Data bars

Not only will you be using these more “BI-oriented” elements, but you will be learning to combine most of the tools that SSRS has to offer. Moreover, you will be using all these tools together to create dashboards and output for mobile devices.

This book has no pretentions about being exhaustive. There are simply too many ways to use SSRS for business intelligence to cover in one small volume. Consequently, you will find only a selection of techniques and approaches that my experience has shown to be useful. There are plenty more ways to develop and use gauges and charts, for instance, than are explained in this book. Indeed, I hope that you will want to take the models that you find here and further enhance and develop them to create your own visualizations.

You are welcome to begin your BI development using this book as a starting point if you wish. However, if you feel that you need to revise some core knowledge, then Pro SQL Server 2012 Reporting Services by Brian McDonald, Shawn McGehee, and Rodney Landrum (Apress, 2012) is a perfect place to get the information that you might need.

As I am assuming that you are an intermediate-to-advanced SSRS user, I have had to make some presumptions about your level of knowledge. Nonetheless, I do not want to leave near beginners completely out in the cold, as there are simply too many cool things that can be done even if your knowledge of SSRS is only rudimentary. My approach is to start many chapters with an initial example that explains many of the core techniques and tools that you will be using in greater detail further on in the chapter. This first item isolates and explains each step in the process of developing a BI visualization. I hope that this approach will give novices a rapid introduction to the relevant techniques. From then on in the chapter, all other items are explained a little more succinctly. Specifically, the multiple properties that most elements require to be set are given as a table of properties that you have to set from one of the requisite dialogs.

SSRS allows you to set many properties in two or even three ways. I will try always to explain how to set properties using the dialogs that appear after you right-click an option in the context menu. This does not mean that you cannot use the properties window (or any other available method) if you prefer.

Some SSRS objects–charts spring to mind–are quite complex to configure. It follows that defining all the required properties for an arresting visual element can take quite a few clicks. This can also mean traversing a multitude of dialogs, panes, and windows full of settings.

It can also be difficult to select the appropriate part of a gauge or chart when you want to modify it. Admittedly, this becomes second nature after a little time, but until then you may need a little practice to get the job done.

Nearly all the examples in this book tend to use virtually the same basic template where the only parameters are the year and month. Inevitably, your dashboards and visualizations will be more complex than this, but I prefer to stick to a simple core report rather than cause confusion by altering the parameters for each report. This way you can concentrate on the elements that make up a dashboard rather than the mechanics of building and configuring different parameter sets. The report __BaseReport.rdl can be used as a template for nearly all reports if you wish to save time when it comes to adding the shared data source and shared datasets, and configuring the core parameters. Just remember to make a copy of the file first, so that you can reuse it easily for other reports.

How Best to Use This Book

This book is designed to be read in a linear fashion, from start to end. Indeed, some later chapters build on visualizations developed in previous chapters. To avoid this becoming a constraint there is nothing to stop you from merely taking the completed report or widget from the sample application and using it as a basis for development if you are jumping in at a later chapter. Equally (and if you are already a proficient SSRS developer) you can certainly leap in to any chapter or example to glean some useful information and tips.

Chapters 2-7 are designed to be read as a coherent unit. They explain how to build dashboards from component units. The basic components are described in Chapters 2-5, as follows:

• Chapter 2 is an introduction to KPIs and some of the ways (from classic to more adventurous) of presenting your Key Performance Indicators.
• Chapter 3 introduces gauges in business intelligence for SSRS. You will see some fairly standard gauges alongside some of the more interesting things that you can do with gauges.
• Chapter 4 looks at some of the chart types that my experience has led me to believe are suited to BI. This is not a complete introduction to all the chart types that SSRS offers, but a trip into some of the less traditional aspects of chart design and creation.
• Chapter 5 introduces maps and geographical data in SSRS. In a break with the format of this book, it provides a more complete introduction and does not presume prior experience with maps in SSRS.

Chapters 6 and 7 show how to assemble these components to build sample dashboards.

• Chapter 6 shows how to use and manipulate images to standardize presentation in dashboards.
• Chapter 7 shows how to assemble the composite parts of a dashboard into a coherent and attractive report.

Chapters 8 and 9 show how to customize and revamp the SSRS user interface and add interactivity.

• Chapter 8 shows how to extend interactivity in SSRS using slicers and highlighting. You will also see how to create a kind of popup menu to enhance the user experience.
• Chapter 9 shows how to enhance the user experience through adding tiles to the SSRS report interface. You will even see how to imitate Power BI carousels to scroll through lists of data as well as developing controlled paged datasets.  Finally, you will see how to create “tabbed” reports.

The final three chapters cover mobile output and a series of techniques to deliver BI efficiently and quickly:

• Chapter 10 gives you a series of tips and tricks that you will find useful when preparing reports that are destined to be viewed on tablets and smartphones.
• Chapter 11 explains how to work more efficiently through standardizing certain methods and techniques.
• Chapter 12 concludes the book with approaches that help you deliver data faster to the output devices and consequently enhance the user experience.

Conclusion

Now that you have seen what this book is about, it is time to get practical and to start using SSRS to create and deliver business intelligence to your users. The first stop on the road to the next level of SSRS is producing KPIs. This is the subject of the next chapter.