Planning Your Report Design in Microsoft SQL Server 2012 Reporting Services

Before you read this chapter, you should have Reporting Services installed and have a general understanding of how to create a basic report. If you are still fairly new to Reporting Services report development, you might not fully understand how to implement some of the concepts that we discuss in this chapter, but that should not deter you from skimming through it. The purpose of this chapter is to help you think about how to prepare for the report development process. Knowing who will view reports and how they want or need to interact with reports has an impact on how you should design those reports.

Reporting Services provides a lot of flexibility in report design, which can be overwhelming at first. You can spend a lot of time trying out different approaches to report design before you discover that a feature that users need most conflicts with the features you’ve been implementing. With some advance planning, you can decide which features are most important for your current project and be more productive throughout the report development process. The suggestions we make in this chapter are not exhaustive, but they describe situations we commonly encounter in our consulting practice and strategies we have found useful.

Most importantly, we recommend that you involve users as much as possible during the development process. Prototype early in the development cycle with a subset of data (or even fake data if necessary) and solicit feedback. Make sure you ask questions to clarify your understanding of the requirements, and working with a prototype can help enormously with this process. A prototype is something tangible around which you can generate a discussion. With a prototype, uses can confirm that the design is right, wrong, or maybe close but not quite what it needs to be. Furthermore, users are better able to answer your questions about whether a particular design feature would be useful if they can see it in action.

Knowing your audience

Knowing your audience is important. An understanding of how users typically interact with information, their technical skill and interest level, and whether they plan to use the data in other ways will affect how you approach the design of reports. More likely, you will have a mixed audience and will need to create different styles of reports to accommodate a diverse audience.

One of the first things to know is how users will be viewing reports. The access method they use to view a report can give you more freedom for creativity in the report design or can impose certain restrictions. As you gather information about report access, consider the following scenarios:

• Will users go online to find a report and view it there? If users are viewing reports online, you can consider any interactive feature that Reporting Services supports. However, many of these features are useless if users will be referring to printed reports. For interactivity features that you can add to online reports, see Chapter 13 "Adding interactivity". If you must plan for printed reports, see the Printed reports section later in this chapter.

  When designing a report for online consumption, users typically don’t want to scroll through pages and pages of a report to find needed information. The addition of report parameters, in conjunction with query parameters, can enable the user to focus on information of interest. You can learn more about parameters in Chapter 14 "Creating dynamic reports with parameters and filters."

• Will users primarily receive reports via an email subscription? If so, you need to find out whether the email should contain an embedded copy of the report, include an attachment to the report, or simply provide a link to the report online. In the latter case, you can design your report by using the same criteria for an online report.

  If the report must be embedded in the email, the report should be relatively small in size, containing a minimal number of records and using a layout that fits easily within the width of the user’s monitor if they usually use a computer to view emails. If the users rely primarily on mobile devices for email, consider sending reports as an attachment instead. A report sent as a PDF attachment must be designed with pagination in mind, which we explain in Chapter 15 "Managing the page layout". However, if users need access to the data to combine with other sources or to perform computations, you can send the report as an Excel attachment, and you can focus more on the data structure and less on conforming the report appearance to a corporate standard.

• Will users be using mobile devices to access reports? A recent development is the increasing use of tablets and smartphones to access corporate information. Rather than try to fit everything into a one-size-fits-all dashboard-style of report, consider focusing on a few key subject areas and then provide access to additional information through links that open separate reports or jump to another section of the same report.

  When users are in the office, they’re often accessing the BI system to check on the general status of operations. They’ll be looking at the big picture to see whether things are going as expected or not, and they can then explore areas of interest to get into more specific information as needed. By contrast, the mobile audience is usually responding to a circumstance, either searching for something specific to answer a question or looking up the high-level status of some business process. They’ve either encountered a problem that needs to get resolved or anticipate that they’re on the verge of a problem that they want to pre-empt. Mobile users might be under pressure standing in front of a client or in an otherwise distracting environment, so the reports must be designed to make it as easy as possible to get to specific information and to follow a train of thought by drilling from one type of information to another type of information, and to filter the data down to relevant details as needed. Parameters and actions are useful features to implement in mobile reports. See Chapters 13 and Chapter 14 to learn how to use these features.

The technical skills and interest level of your users also play a role in determining how best to make information available. Technical skills among users can vary widely in an organization or even within a department. You should be prepared to provide building blocks for users who are capable of some level of self-service reporting if interest exists. The following are some strategies to consider for different types of users:

• Data-savvy advanced users Some users spend their entire day focused on data. This group of users likely has the technical skills necessary to acquire and manipulate data from relational or multidimensional data sources. They want as much unfettered access to data as you’re willing to give them. You can set this group up to work with Report Builder and allow them to build reports from scratch, or you can create a library of starter reports that they can modify as needed.

• Motivated power users This group of users knows their data very well. Perhaps they are capable of building reports but are unable to write their own queries. You can give this group permission to use Report Builder and publish shared datasets for them to use when creating their own reports. That way, they have the freedom to create the reports they want and you can control the data that they use. See Chapter 8 "Retrieving data for a report", for more information about shared datasets.

  Within this group, you might even have people who know what they want when they see it but are unable to put together their own queries and are intimidated by the thought of building a report from scratch. For this group, you can publish report parts that represent commonly accessed data in a variety of data structures—tables, charts, maps, and so on. Then this group can use the Report Part Gallery in Report Builder to browse published report parts and piece together multiple report parts to build a report just the way they like it. Chapter 29 "Reusing report parts", explains how to do this.

• Basic users Some users lack the time, the skills, or the interest to explore data and build their own reports. They just want access to information as quickly and flexibly as possible. When creating reports for this group, you might find a lot of similarities between reports. To save yourself from creating a maintenance nightmare and also save users the trouble of trying to determine which of the many possible reports has the answers they need, try to consolidate multiple reports into a single report as much as you can. Use parameters to modify the content of the reports, either by filtering the data or by hiding or showing elements dynamically. You can create linked reports, as we describe in Chapter 23, "Deploying reports to a server" to create the illusion of multiple reports if necessary and hide the parameters that you don’t want users to change.

Yet another aspect to understand about your audience is how they plan to use the report. The intended use can make a difference in the direction you should focus your development effort. The following are some common ways that people use reports and related development considerations:

• Standard reporting The most common reason to implement Reporting Services is to provide standard reporting at the department, division, or organizational level. This type of reporting typically requires the use of standard styling to produce a consistency of appearance across multiple reports.

  After the standards for your reporting environment are established, you can create one or more base reports to use as templates. For example, you might create a portrait version and a landscape version of a table layout and include common parameters in each version. You can store the report definition files in the Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject folder on your computer. When you use the Add A New Item command in SSDT to create a new report, you can select the template and continue report development by adding a dataset and assigning fields to a data region.

• Raw data Reporting Services can export data to Excel, CSV, XML, and ATOM formats. Some people just want the raw data so that they can develop their own Excel workbooks or import it into PowerPivot for Excel or other applications. Creating a report to deliver data might be preferable to allowing direct access to data sources because you might need to enhance the data with calculations, you might want to schedule data retrieval to manage the impact on source systems or your network, or you might want to deliver the data by using subscriptions rather than require the user to go to the report server. If the report is simply a means to an end, you don’t need to spend a lot of time conforming a report to a standard format with page heads and footers, and so on. You should keep these reports in a separate folder to emphasize their purpose as data structures.

• Quick information retrieval Sometimes people need access to information without a lot of formality. This information might come in the form of an email to report the status of a process, or it might be a lookup to get customer contact information. Either way, this type of report is not typically something a user prints out to share with others or present at a meeting. Therefore, the layout can be simple, without the formatting that you would require for official corporate reports.

• Operations monitoring This type of reporting can take the form of a dashboard or detailed reports that include conditional formatting to highlight trends and exceptions. When designing a dashboard, you should consider the standard size screen for the majority of users so that you can use the screen effectively. A dashboard should convey summarized information clearly at a glance, using data visualization techniques to provide comparisons, display trends, or highlight exceptions. Ideally, the user can see the entire dashboard without scrolling. Use the chapters in Part III. Adding data visualizations, to learn about the variety of data visualizations at your disposal.

  You should also consider the questions that might result from viewing a dashboard and prepare supporting detailed reports. You can add actions to each data visualization to open a related report and pass the context of the user’s click to that report. For example, in a chart that displays sales trends by week, a user can click a specific week to open a report that displays the daily detail for that week. See Chapter 13 for information about actions.

• Management or external stakeholder reporting The most formal type of reporting that you might have is the set of reports prepared for management or external stakeholders. Although these reports might be viewed online, you should anticipate that they might be printed or shared as PDF documents. You typically apply consistent styling and branding to this type of report and must take care to adjust the design so that the report produces a clean layout in print or PDF format. We provide suggestions for styling in the "Developing standards" section of this chapter.

• Information as a service You might produce a series of reports as a service to customers. This type of reporting often requires consistency in appearance. You might provide access to reports online, or you might deliver a set of reports as a single PDF document. To combine reports into one document, you create individual reports and then create one parent report that uses subreports to organize the individual reports in the correct sequence. When working with subreports in this way, to avoid introducing blank pages, you must take care to manage the page sizes of the individual reports as well as the parent report. See Chapter 14 for information about subreports, Chapter 15 for page size management, and Chapter 32 "Understanding Report Definition Language, for an alternative approach to combining reports.