Planning Your Report Design in Microsoft SQL Server 2012 Reporting Services

Reviewing report options

Although many changes to a report can be made at any time during report development, the most important decision you need to make early in the process is the type of layout to use. However, there are other options to consider as well. You need to understand any limitations you might have when retrieving data, and you must give some thought to whether to implement parameters and how best to configure them. If your users intend to access reports by using mobile devices, you should also consider how best to design reports to accommodate the smaller form factor. Similarly, you need to consider how printing a report affects the layout.

Layout decisions

With some practice and a good understanding of how a tablix works, you can convert a table to a matrix or vice versa, but when you’re new to Reporting Services, you might find the necessary steps to be unintuitive. Moreover, you cannot change a table into a chart or map nor turn a list with various nested data regions into a table. Therefore, deciding the layout to use is a critical decision to make.

You can use a table layout when you need to present a simple list of items with a relatively small number of fields per item. If there are a lot of fields, you need to consider whether it’s acceptable for the user to scroll horizontally to view all the fields online. If the user is going to print the report, there are additional considerations we discuss in the Printed reports section of this chapter.

Generally, you should design the reports for online viewing to display a limited number of columns. It’s much easier for users to scroll up and down to view information than to scroll left and right. For that matter, you should question whether users really need to see all available columns at once. It might make more sense to set up a parameter to allow users to choose the columns they want to see. Then you can hide or show columns on demand.

You can also use multiple detail rows and stack the fields vertically if necessary, as shown in Figure 6-1. That way, you can include a larger number of fields in the layout while keeping the width of the data region smaller than the user’s screen width or the printed page.

Figure 6-1 You can use multiple detail rows to reduce the number of columns required to display all fields for a dataset row.

Another option for handling a large number detail fields is to create a grouped list. The advantage of a list is the ability to arrange text boxes in a freeform fashion. You can then accommodate text boxes of varying sizes on each “row” of the list, as shown in Figure 6-2.

Figure 6-2 You can arrange text boxes inside a grouped list to minimize the width of a report.

A matrix is useful when you need to provide a crosstab layout. However, you run the risk of having too many columns on the screen or printed page if you don’t plan ahead. A table has a fixed number of columns that you define during report development, but a matrix can have a variable number of columns because the data determines the column groupings. Therefore, if your goal is to fit a matrix to a specific size, you should consider implementing a filter to reduce the number of possible column groupings.

On a more granular level, you need to consider how wide each text box should be to comfortably accommodate data. A text box will never grow wider than the size you define, but it has a CanGrow property that is set to True by default, which expands the text box vertically if the data is wider than the text box, as shown in Figure 6-3. This property might be acceptable for reports that you view online but can disrupt the alignment of printed reports by pushing report items from one page to another.

Figure 6-3 When the CanGrow property is set to True, the text box expands vertically to display the entire text it contains.

Data considerations

Rather than create a large report that contains hundreds or thousands of pages, find out whether all that data is really necessary. Usually it’s better to add a filter or create a query that aggregates data at the source. When users view reports online, they usually want to look up a piece of information and don’t need to scan an entire report to find that one item of needed information.

Rather than create a report that includes pages of detail records with group totals, create a summary report to show only group totals. Have users start their review process from a summary report, and then include an action on the report to drill through to a detail report. The detail report displays only a subset of data related to the portion of the summary report that the user wants to investigate. In both cases, you have a report that retrieves a smaller number of records and generally runs faster than a report that retrieves thousands of records.

Another aspect of managing data in reports is the data definition itself. Some organizations have strict control over access to data sources, requiring report developers to use stored procedures or views maintained in the source database. This requirement simplifies maintenance of commonly used queries by centralizing the query.

Using stored procedures and views is not an option for data that comes from non-relational data sources. For those situations, you might consider using shared datasets to achieve a similar effect. The downside of shared datasets is that they are primarily intended for use in Report Builder. If you use SSDT to develop reports, you must either obtain the current version of a shared dataset from a source control system or download it from the report server. SSDT does not provide direct access to published shared datasets.

Parameters

Parameters are useful elements of a report. You can use them to filter data, set report item properties dynamically at run time, or provide input for calculations. Throughout this chapter, we suggest using parameters to address a variety of scenarios. As you plan your report, you must make several decisions about implementing each parameter, including the following:

• User input You must decide whether a parameter accepts user input. In most cases, you should avoid this option due to the increased security risk of an injection attack and due to the limited validation available in Reporting Services. Usually it’s better to give the user a list of values from which to make a selection, but you might prompt the user when you want to create a filter by using a user-defined string and wildcard or when the number of possible values is not practical to present in a list, such as a sales invoice number.

• List of values The preferred method for prompting the user for a value is to provide a list of valid values. You can hard-code this list if necessary, but using a query gives you more flexibility in the long run and allows you to easily reproduce the same list of values when using the same parameter in multiple reports. You can create an independent query, or you can use a query that depends on the user’s selection of a value for a separate parameter. This technique is known as cascading parameters and is described in Chapter 14. You must also decide whether the user can select only one value or multiple values.

• Default value If you do not include a default value for a report parameter, the report cannot execute until the user provides a value. This might be a desired behavior when there is no reasonable default, such as a report that displays information about a specific sales invoice. Wherever possible, you should provide a default value. This default can be hard-coded in the report or produced as the result of a query.

Mobile devices

We pointed out earlier in this chapter that you should design reports to use screen space effectively and minimize scrolling when people are working on a desktop. However, with mobile devices, the same rules do not necessarily apply. Users are accustomed to not seeing all information on the screen at one time. A useful feature of tablet devices is the ability to use gestures on the screen to navigate, either by zooming in and out as necessary or scrolling. Therefore, you might not need to worry as much about fitting the contents of a report to the screen, but always check with your users to understand their expectations before establishing a policy one way or the other.

If you are reproducing a dashboard for mobile devices, you might consider limiting the number of perspectives or views of the data to no more than four. If you have four views laid out in a quadrant format, keep in mind that the most important information should be placed in the top-left quadrant. That’s where our eyes naturally start, and on the tablet, that should be the information that’s most likely to fit completely on the screen.

Another suggestion is to keep the report simple for mobile users. Having too much information on the screen makes it more difficult for users to focus on the information they’re looking for, especially if they’re in a hurry. Having legends or lots of parameters for filtering can create visual noise and interfere with their ability to locate the data. In particular, when you know your users will be viewing information on tablets, don’t use tiny fonts. In addition, if there are actions in a report, associate the actions with report items big enough to handle comfortably on a touch screen.

Printed reports

When designing reports that are destined for printing at some point, you must factor in the page size as you position items in the report layout. You can set report properties to fix the orientation as portrait or landscape, which we explain in Chapter 15. Many of the same considerations that we describe in the “Layout decisions” section of this chapter also apply to printed reports, and perhaps more so. Preview your report, and switch to Print Layout mode so that you can see how the rendered report fits on each page. You should also export the report as a PDF file to check the rendering more carefully.

If a report spans multiple pages, you should configure repeating column headers in the tablix. That way, users don’t have to flip back to the first page to determine what a particular column contains. Chapter 15 explains how to set properties in the tablix to repeat the column headers. The same principle applies to row headers for a matrix. You can repeat the row headers on the second page if a matrix is too wide to fit on a single page.

By default, Reporting Services tries to fit as much content as it can on a page and inserts logical page breaks for a report. For greater control over the location of page breaks, you can define explicit page breaks relative to report items or groups within a data region.

Regardless of whether you set explicit page breaks or rely on the default logical page breaks, each page of the report is assigned a page number. You can reset this page number to maintain a separate page numbering system within sections of a report, but there is also an overall page number that you can use. When you know a report will be printed, it’s a good idea to include the current page number and the overall page number in the page header or page footer, which are the only locations in which the page number can display. That way, if a user inadvertently shuffles the pages of the report or misplaces ones, the presence of page numbers helps the user reorder the pages correctly or notice the missing page.

You should also include the report execution date and time in the page header or page footer. That way, the user can easily tell if the report is recent enough to remain useful. You might also consider including the report server name and report path to help the user locate the report online when it’s time to execute a fresh report.

Sometimes you can design a report that looks good both online and in print, but that’s not always possible. You might need to design two separate reports. Rather than have users open one report and then have to switch to another report, consider having a link visible in online mode only that users click to output the report as PDF or Excel. Reporting Services doesn’t actually allow you to create links dynamically, but you can create an action and then underline the associated text to make it appear to be a link. In the action definition, you can use an expression to dynamically create the URL to produce the requested output. See Chapter 13 for information about actions and Chapter 35, “Programming report access“, for information about URL access.