Over the course of the last few chapters you have seen how to create many different types of visualizations. As business intelligence is all about delivering multiple views of the organization’s data, you now need to see how some of these individual elements can be combined to deliver insights that are even more powerful for being coalesced into a single coherent view of specific information.

This chapter will give you a series of hints and tips on ways to assemble dashboards and other BI presentations in a structured and controlled way. First, you will see how to use existing widgets (which I also tend to call components in this context) to assemble dashboard-style reports. Then you will see how to align objects, group and contain objects, and control the way that certain elements grow. Nearly all that I will cover concerns the presentation aspect of business intelligence with SSRS. There is a simple reason for this: your audience will not only be distracted by a lopsided final output, they will also lose belief in the validity of your information. So the final presentation tweaks can be nearly as important as the accuracy of your data.

This chapter presumes a certain way of working with BI reports. As you have probably noticed, I tend to build reports up starting with the smallest object, which is then assembled into bigger visualizations and then-and this is one of the aims of this chapter-a series of visualizations finally becomes a complete report. Of course, there are other ways of working, and I do not for one second want to imply that this is the only method. It just happens to be a technique that suits the learning process that a structure like a book imposes, and it can reap rewards in practice. Once again, you are free to adopt any style of dashboard creation that gives you the results that you want.

This chapter builds on the work done in the preceding chapters. This does not mean that you have to have built all the components that you will be using. You can download the sample SSDT application and take certain “widgets” that you find there as the basis for building the examples in this chapter, if you prefer.

Remember above all that dashboards, scorecards, and most other business intelligence reports are designed to be relatively immobile. They are not normally designed to grow or shrink. Consequently, you will probably spend a large amount of time setting the size and alignment of objects in a report. This is perfectly normal when developing SSRS BI visualizations. This chapter aims to give you some ideas as to how to do this efficiently and productively.

A First Dashboard

To begin with, I will show you how to use the series of visualizations that you created in some of the previous chapters to assemble a first dashboard. This dashboard will illustrate the principles of dashboard assembly in SSRS.

Figure 7-1 shows what your first dashboard will look like. As you can see, it is composed of five widgets:

• _SalesPercentageByMake.rdl: This is the top left-hand set of four gauges that shows how a sales map targets for four main makes of cars. This component is described in Chapter 3.
• _MapOfEnglishCarSales.rdl: This visualization shows the sales to date in England by region. This component is described in Chapter 5.
• _ThermometerYTDSales.rdl: This gauge displays the sales up to and including the selected month along with the target for the year. This component is explained in Chapter 3.
• _BubbleChartMarginByAgeAndMake.rdl: This chart shows the percentage profit by make of car for vehicle age ranges. This component is described in Chapter 4.
• KeyFiguresForMonth.rdl: This text-based KPI gives key information for five essential metrics. This component is described in Chapter 2.

That, then, is the theory. Now let’s move on to the practice.

1. Make a copy of the report __BaseReport.rdl and rename the copy Dashboard_Yearly. If you are starting from scratch, follow steps 1-5 from the first example in Chapter 3, or indeed the initial steps of many of the examples that you have seen so far in this book.
2. Resize the report area so that it is approximately large enough to hold all of the objects. I suggest about 16 inches by 10 inches to start.
3. Open the report _SalesPercentageByMake.rdl and take a look at the datasets that it uses. In this case, there is only one, SalesPercentageByMake. Recreate this dataset (paying attention to capitalization) in the new dashboard file that you created in step 1.
4. Select everything in the report _SalesPercentageByMake.rdl (Ctrl-A can be useful). Copy and paste the widget elements into the dashboard. Place it on the top left of the report area, possibly leaving a little space above and to the left.
5. Open the four remaining reports listed above and recreate their datasets, then copy and paste the widgets into the dashboard. Place the different elements so that the dashboard looks reasonably similar to the one in Figure 7-1.
6. Tweak the sizes of the five component elements until the proportions suit your aesthetic requirements.
7. Adjust or add any borders that are needed to ensure that the component parts share a common look and feel. In this example, for instance, this means setting the map border to Line rather than Embossed (as it was in the source file). You may in practice have to set all the borders to be the same color and weight to ensure a standardized presentation across all the elements of the dashboard.
8. Select the three components at the top of the dashboard that share a common top edge. Then select Format Align Tops.
9. Ensure that any other elements that need to be aligned vertically are aligned using the same approach. This will specifically apply the KPIs copied from the KeyFiguresForMonth file.
10. Adjust any fonts and colors to ensure a harmonious aesthetic for the dashboard. You saw several examples of how this is done in Chapters 2 to 5, so I will not repeat the techniques here; instead I refer you back to those chapters.
11. Select all the dashboard elements and position them towards the top and left of the report area, leaving a small amount of white space above and to the left of the components that make up the dashboard.
12. Reduce the height and width of the report area so that there is no empty space at the edges of the dashboard.

How It Works

This dashboard presents no arcane difficulties, so is a good example to learn how to assemble multiple components into a single report. First, you need a “core” dashboard to contain the datasources and datasets that are common to all of the widgets. Then you add the datasets and widgets from the sub-elements that will be used to assemble the dashboard. You can add the datasets all together, or add them one at a time as you copy and paste the individual widgets into the dashboard; it is entirely up to you.

You may find that a major part of dashboard assembly is the time spent tweaking the fonts and colors of your components. This will be inevitable, but it does emphasize the point that applying corporate standards to the creation of individual reporting elements that are assembled into more complex presentations can save you time in the long run.

Hints and Tips

These tips and tricks will help you with this example.

• Remember that simple components like charts and maps are easy to resize. More complex composite elements like multiple gauge widgets can take longer, as you may have to resize individual elements inside the component. Anything based on a table can take a while to resize, as you will have to adjust the width and height of columns and rows, possibly across several elements.
• Aligning the top and left edge of certain objects is not just for aesthetics. This will ensure that a slight (and possibly invisible) horizontal discrepancy does not cause one element to appear on the final output below another, where it has been “pushed” by the rendering engine.
• When fine-tuning the relative sizes of component parts in a dashboard, remember that you can use the size and location properties to configure the position on the report to a fraction of an inch.
• Although I did not mention it during the process above, remember that SSRS also gives you the possibility to set the horizontal and vertical spacing automatically. These options are in the Format menu, and can be extremely useful when assembling complex visualizations such as dashboards.

Grouping Objects

As you probably know by now, SSRS does not have a function that enables you to group objects so that multiple items can be manipulated as a single object. There are, however, a few workarounds to overcome this limitation.

At the start of Chapter 3, you saw some slightly “edgy” gauges of sales by make of car. However, you did not group these gauges into a coherent visualization, the sort of output that is shown in Figure 7-2 (where, incidentally, the output is for January 2013). I want to take this as an initial example of using tables to group objects.

You have already created initial gauge for this tiny dashboard (which is destined to be displayed on a smartphone) in Chapter 3. Even if you did not, it exists in the sample application, so here is how you can make a single gauge into a coherent presentation.

1. Make a copy of the report CarSalesGauges.rdl. Name it SmartPhone_CarSalesGauges.rdl.
2. Extend the size of the report area to give yourself plenty of room to work.
3. Add a table to the report. Set the dataset to be the same dataset as the one that is used for the charts (or gauges) that it will contain. In this example, it is SmartPhoneCarSalesGauges using the stored procedure Code.pr_SmartPhoneCarSalesGauges.
4. Delete the detail row (and confirm the deletion).
5. Set the text box borders to None.
6. Add five new rows, or as many as are required to end up with a six-row table. The table should contain three columns. If not, add or delete columns until the table is five rows by three columns.
7. Select the table. Display the properties window and set the BackgroundColor property to Black.
8. Set the borders for all of the text boxes and the table itself to none.
9. Select the left-hand text box in the first row and set its size to 4 centimeters wide by 0.6 centimeters high. Do the same for the right-hand text box on the first row. Then do the same for the text boxes on the third and fifth rows.
10. Select the left-hand text box in the second row and set its size to 4 centimeters by 4 centimeters. Do the same for the right-hand text box on the second row. Then do the same for the text boxes on the fourth and sixth rows.
11. Select any text box in the second (middle) column and set the width to 0.6 centimeters.
12. Resize the existing gauge to 4 centimeters by 4 centimeters. Drag the gauge inside the left-hand cell on the second row.
13. Copy this gauge and paste five copies as follows: two in the first column in: the fourth and sixth rows and the other three in the second, fourth, and sixth rows of the third column.
14. Set the filter for each of the gauges to the make of car that is displayed above each gauge in Figure 7-1. You do this by right-clicking each gauge and selecting Gauge Panel Properties followed by Filters. The existing gauge is filtered to display only Aston Martin, so all you have to do is change this to Rolls Royce, Jaguar, etc.
15. Add the title for each gauge in the cell above the gauge. Center the title and set the font to Arial 10-point white.

You now have a set of gauges that is contained and grouped in a table.

How It Works

As you can see, you are concentrating your efforts principally on creating a table that will contain the required number of gauges. Nearly all of your time is spent on defining column widths and row heights. Then you ensure that any table borders and backgrounds (or their absence) map to your presentation requirements. Finally, you place copies of the widget that you want to display inside the table and tweak the filters to ensure that you place the precise gauge that you want in the right part of the table.

Hints and Tips

Although this technique is not difficult, there are a few things that you need to know when using tables to control object alignment.

• Objects cannot be added to row groups. So you need to remove row groups first.
• Use added rows for spacing/blank white space. This is a frequently-used technique, and it can seem a little laborious at first. However, it is well worth the effort as it makes it easy to define the spacing accurately by setting the row height for the “spacer” rows.
• The table needs a dataset, so it seems easier to use the same dataset as the objects in it. However, you can use an empty (or “dummy”) dataset if you prefer, as the charts or gauges can use their own datasets independently of the table dataset. Using a dummy dataset is described a little further on in this chapter.

A More Complex Dashboard

Now that you have seen some of the basic techniques needed to control object placement, I suggest that you make another dashboard to learn how to present more complex business intelligence reports using SSRS. This dashboard is DashboardMonthly in the sample application, and it looks like Figure 7-3.

This dashboard uses the six source components that you saw in previous chapters. They are

• _MonthlySalesAndProfit.rdl
• _GaugeSalesByCountry.rdl
• _ColorSales.rdl
• _PyramidSales.rdl
• _MonthSalesByMakeOverTime.rdl
• _RatioAnalysis.rdl

Creating the Basic Dashboard

Before you can add the components, you need a report to place them in. You can create this by following these steps.

1. Make a copy of the report __BaseReport.rdl. Name the copy Dashboard_Monthly.rdl.
2. Set the size of the report body to approximately 17 inches wide by 12 inches high.

You can, if you prefer, start with a blank report and add the datasource and datasets that you have used in most of the reports you created in previous chapters.

Grouping in Tables

As you saw earlier in this chapter, tables can be an excellent tool for grouping and aligning other objects. Fortunately, tables let you place most other objects inside a table “cell.” This means that you can place gauges, images, or even charts inside a table to control the position of a series of objects.

To extend the concept a little, tables can also be very useful when grouping and aligning widgets vertically. Indeed, this not only groups a set of charts or gauges, but it can solve an otherwise tricky issue. The problem can be that you have a set of, say, charts that you have placed in a report one above the other. So far, so good. You have aligned them perfectly, and all looks fine-until you preview the report. Then some of the charts suddenly drop way down on the report, far below the others. After much frustration, you discover that the problem is a table at one side of the set of charts. As this table grows, it pushes any object that was below the bottom of the table in the design view beneath the bottom of the (now much longer) table in the report when it is displayed.

Unfortunately, this is standard SSRS behavior where tables are concerned. However, there is an elegant solution, which is to use a table to position a set of objects (charts, in this case) vertically. As an example of this, let’s take the chart _PyramidSales.rdl that you saw in Chapter 4. Suppose that you now want to align three of these charts one above the other. To make this example clearer, I suggest that the top chart displays sales for the current month, the middle chart shows sales for the current quarter, and the bottom chart sales for the current year. I do not want to reiterate how to create charts here, so I will refer you to Chapter 4 for the principles, and the stored procedure Code.pr_WD_PyramidSales in the CarSales_Reports database for the code that returns the data for the three pyramid charts. To create this type of presentation, follow these steps.

1. Drag a table on to the report. Set the dataset to be the same dataset as the one that is used for the charts (or gauges) that it will contain. In this example, it is PyramidSales using the stored procedure Code.pr_WD_PyramidSales.
2. Delete all of the columns but one. Then delete the data row.
3. Set all the text box borders to None. Ensure that the table border is also set to none.
4. Add four more rows. Set the width of the column to 2.7 inches.
5. For the second and fourth cells in the column, set the height to 0.25 inches. Set the font size to 3 points; otherwise the height of the row will stretch to the (larger) point size.
6. Copy and paste the chart _MonthPyramid into the top cell. Set the dataset to PyramidSales and the chart filter in the chart properties to ChartType = MonthlySales.
7. Copy this chart into the third cell and set the filter to ChartType = QuarterlySales.
8. Copy this chart into the fifth cell and set the filter to ChartType = YearlySales.
9. Tweak the vertical titles for the charts to read Current Month, Current Quarter, and Current Year (from top to bottom).

This set of charts will now look like the rightmost visualization (the three pyramid charts one above the other) in Figure 7-3.

How It Works

As you can see, this dashboard has a table to the left of the pyramid charts. If the pyramid charts were not contained-and constrained-by a table, then their vertical alignment would be skewed when the table of color sales grew, and the second pyramid chart (of sales for the quarter) would have been placed under the bottom row of the table to its left. As it is, the fact that the charts are enclosed in a table controls the way that the SSRS rendering engine displays the final output.

Grouping Inside a Rectangle

I will now use this dashboard to show some further techniques for aligning visualizations-and elements in visualizations. A first trick is to use a rectangle as a container for other elements.

This dashboard contains two examples of using rectangles as containers to group inner objects. The first one is probably self-evident. On the left is a set of gauges inside a rectangle, and then the rectangle provides the border-only it is supplied by a background image, as described in Chapter 6. The only real tricks to know when placing objects inside a rectangle are the following:

• Always draw the rectangle outside the objects that you want to enclose. If necessary, extend the size of the report provisionally to create the space needed for this.
• Ensure that the rectangle is at the back (or bottom, if you prefer) of the object “layer.” To define this property, select the rectangle and choose Format Order Send to Back.
• Drag the objects inside the rectangle once the rectangle attributes (and background image) are set.

Now that you understand this, let’s see the steps required to assemble the Sales by Country set of gauges in this dashboard. To make things clearer, Figure 7-4 shows you a close-up of the widget.

1. Drag a rectangle onto an empty area on the report. Resize the rectangle so that it will be approximately big enough to hold all the elements that you want to place inside the gauge.
2. Add the image file EmbossedBordeFull66By37.png to the report.
3. Set this image as the background for the rectangle, and set the BackGroundRepeat as Clip. If you need to revise this, it is described in Chapter 6.
4. Open the report(s) containing the object(s) that you will be adding to the dashboard. In this case, it is _GaugeSalesUK.rdl.
5. Add any datasets used by the objects that you will be placing inside the rectangle to the report. You can see these in the file containing the “source” object. Be very careful to use exactly the same names for the datasets, and remember that SSRS is case-sensitive for these names. If you are building the objects directly inside the final dashboard, then this will not apply.
6. Resize the base gauge to suit your requirements, and place it inside the rectangle.
7. Make the number of copies that you need of the source gauge (four in this example) and place them inside the rectangle.
8. Set the filter for each gauge to the relevant element. In this example, it means selecting a country for the filter. The country names are indicated in Figure 7-3 on the top right of each gauge.
9. Alter the country name in the copies of the gauges to display the country used for the filter.
10. If necessary, adjust the font size of the country name at the top right of each gauge.
11. Prepare an image containing the rotated text “Sales by country” as described in Chapter 6 and add the image to the report.
12. Drag the image onto the report inside the rectangle containing the gauges. Resize the image and place it to the left of the gauges.
13. Add a text box to the right of the first row of gauges. Set its text to read “x 1000”. Set the font to Arial 10 gray.

How It Works

Assembling this particular visualization is not desperately difficult. The key is to remember that once the elements are placed inside the rectangle they can be moved, copied, or deleted as a single unit. Adding the rectangle border was to remind you of one of the techniques you saw in Chapter 6. This has the added advantage of ensuring a certain visual conformity as far as the borders of certain components of the dashboard are concerned.

Adding Custom Elements

Sometimes a component widget needs a little enhancement before it can be used effectively in a dashboard. The fourth of your component parts in this dashboard (_MonthSalesByMake) is a case in point. It needs both a border and a legend. To minimize risk and to ensure a fallback position, I suggest first duplicating the source file. Then you add any new elements, and finally copy the new, improved source file into the dashboard. To give you a more readable view of what you are doing, Figure 7-5 shows the progression from the initial set of four gauges to the complete component that you will copy into the dashboard.

1. Make a copy of the file _MonthSalesByMakeOverTime.rdl. Name the copy _MonthSalesByMakeOverTime_Extended.
2. Open the copy.
3. Create an image file for the title Sales by Make. Import this image into the report and then drag the image from the toolbox into the report. Resize and place the image, and set its BackgroundRepeat property to Clip.
4. Add a text box to the right of the first gauge. Set it to be tall and thin (more precisely, 0.19 inches wide by 1.65 inches high). Enter the text “Rolls Royce” but add a return between each letter or space.
5. Copy this text box to the right of the two central gauges, and to the left of the right-hand gauge. Set the texts to Jaguar, Aston Martin, and Other as you see them in Figure 7-5.
6. Add four rectangles under the gauges. Make them into squares and space them out as in Figure 7-5.
7. Set the colors of the four text boxes to correspond to the colors of the pointers in the gauges. In this example, they are Dark Blue, Blue, Light Steel Blue, and Aqua.
8. Add a text box to the right of the first colored rectangle. Ensure that it is large enough to hold the longest text that will be used, once you have set the font (Arial, 9 point) and font color (grey). Set the text alignment in the Properties window to Middle (for VerticalAlign) and Left for TextAlign.
9. Copy the text box three times and place the copies to the right of the colored rectangles. Change the texts to those shown in Figure 7-5.
10. Select the four colored rectangles and their text boxes, and align all the objects vertically to Middles. Leaving the eight items selected, click Format Horizontal Spacing Make Equal.
11. Add the same image file to the report (EmbossedBordeFull66By37.png) that you used for the previous component.
12. Add a rectangle to the report, away from the existing objects and in a large clear area of the report. Add the background image that you specified in the previous step and adjust the rectangle properties as you did for the previous component.
13. After any final tweak to perfect the component, save it and copy it in its entirety to the dashboard.
14. Add the MakeSalesComparedOverTime dataset from the component report to the dashboard.
15. Place the component in the dashboard and finalize it by tweaking the size and proportions as required for the overall aesthetic.

How It Works

In this case, you add the decorative elements first, and then set the frame afterwards. This is a conscious choice because it can be easier to manipulate objects without the irritation of selecting the background rectangle by mistake. Equally, selecting multiple objects-the better to align them, for instance-is much easier without a background rectangle getting in the way.

Adding the Radar Chart

Finally you come to a component that can probably be used as-is. This is the radar chart that you saw in Chapter 4. To add it, follow these steps.

1. Open the file _RatioAnalysis.rdl.
2. Find the dataset that it uses (RatioAnalysis) and add this to the dashboard.
3. Copy the chart into the dashboard.
4. Position and resize the chart.

Grouping Two Components

A rectangle that you are using to group elements in this dashboard is probably less obvious. In fact, there is an invisible rectangle around the Sales by Make and Key Ratios visualizations. You might be wondering why this is necessary, as it is blissfully easy to align the two widgets and assume that the work is done. Here, as well, the table is the villain of the piece. Because the Key Ratios radar chart is under the table, it will be pushed downwards as the table grows. If, however, you place the two visualizations (Sales by Make and Key Ratios) inside a rectangle, and then set the rectangle’s border to None, you have created a container that will force the two visualizations to move together. This is altogether more pleasing visually.

This is what you need to do.

1. Enlarge the report so that there is some empty space at the bottom.
2. Drag a rectangle from the toolbox into the space that you just created. Make the rectangle slightly larger than the two components that you want to group.
3. Place the two components inside the rectangle.
4. Select the two components and set them to align vertically at the top (Format Align Tops).

Finishing the Dashboard

A final element that needs to be added is the dynamic table of color sales. Fortunately, adding this table is easy.

1. Open the file _ColorSalesWithBorder.rdl.
2. Find the datasets that it uses (there are several of them) and add them to the dashboard.
3. Add all the images that the component file contains (one by one!) to the dashboard.
4. Copy the table into the dashboard.
5. Position and resize the table. This can mean adjusting the width of multiple columns.

Now you can align any elements-such as aligning the top edges of the Sales by Country rectangle, the color sales table, and the pyramid charts table, for instance-that need it. You have completed your dashboard and can now test the result.

Hints and Tips

Make use of these tips and hints for this example.

• I always advise previewing a complex dashboard every time you add a new component. In case of error it can help to have some context as to what the (often cryptic) SSRS error messages mean, or at least an idea which objects it refers to.
• You must add any image file(s) to the destination report (the dashboard) if the source file (the component) contains images. Otherwise, the images will simply not appear in the dashboard. Of course, if you have used an SQL Server source for images, then providing that you have set up the appropriate dataset in the dashboard, your images will appear perfectly.

Creating a Legend

Sometimes you need to create your own legend for a set of charts because the legend has to be common across several charts. An example is the trellis chart that you saw at the end of Chapter 4. To provide a solution to this issue, let’s create the legend that you see in Figure 7-6 (using the file _TrellisChartCostsPerMonth.rdl from Chapter 4).

1. Insert a table below the trellis chart table. Add the datasource named Dummy (if this does not exist, then it is simply a datasource whose SQL reads SELECT ''). Add three rows outside the existing row group and then delete the existing row group and header row.
2. Set all the BorderStyle to None for all the borders in the table.
3. Add columns until the table has ten columns.
4. Set the width of the first and last columns to 0.125 inches.
5. Set the width of the second, fourth, sixth, and eighth columns to 0.25 inches.
6. On the second (middle) row  set the BackgroundColor of the textbox to the following:
   1. Second: Aqua
   2. Fourth: Gold
   3. Sixth: Lime
   4. Eighth: Tan
7. Set the font size for all textboxes in the top and bottom rows to 2 points. Set the row height of these rows to 0.125 inches.
8. Select the entire table and add an outside border.

Once this is done, your trellis chart should look like Figure 4-14 in Chapter 4.

Hints and Tips

These hints and tips will be helpful for this example.

• The Dummy dataset only exists to prevent SSRS complaining that you have a table without valid data attached.
• Adding empty rows and columns to create spacing from the outside border in the legend is a little long-winded, but it allows you to create a much more aesthetically pleasing custom legend.
• You may want to add a few spaces before the text in the legend to produce a pleasing balanced effect and distance the text from the colored textboxes.

A Few Random Tips

To finish this chapter, I have added a few tips that you may find useful. As they do not really fit into any of the previous examples, I will give them without any attempt at categorization.

• You can use an outer table as a placeholder for other tables. This means first adding the “placeholder” table and then adding tables inside the cells of the outer table. However, if you do this, you are limited to using a single dataset-the one attached to the outer table.
• You can place multiple textboxes into a single cell of a table. Just remember to add a rectangle to the textbox first, and then add any nested textboxes inside the rectangle.
• You can use the cursor keys to move objects around the report area rather than the mouse. This has the advantage of keeping objects on the vertical or horizontal plane where you have already positioned them. If you find that the cursor keys move objects in increments that are too large, you can use Ctrl-Cursor to move in tiny increments.

Conclusion

There are many, many ways of assembling complex reports. Users will have many varied requirements and also, each of us has our own preferred techniques and favorite ways of getting things done. It follows that it is not possible to anticipate all the requirements that you will have to address. I hope however that you will find these methods and techniques useful when you assemble your own dashboards.