Drag several components to the canvas.

As we have seen in this recipe, we can hide components and/or groups of components, which will make your life easier if you are using a lot of overlapping components. By checking Hide for some components, you won't be bothered by these and you can work with the others that are unhidden.

Also, we saw that we can lock one or more components or groups of components. Doing this makes it impossible to select these components, so it won't be possible to move, change, or do anything else with them. In this way you can be sure you won't accidentally alter these components.

Finally, we changed the order of components on the canvas. This is an important feature when we are using overlaying components in our dashboard.

To move a component on top of all other components, you can also right-click on it and select the Bring To Front option. Send To Back will move the component all the way down. The options Bring Forward and Send Back do the same as the arrows in the Object Browser: they move the component one step up or down at a time.

Make sure you have a dashboard that contains a set of components.

As you can see, the search functionality is a useful feature as you can easily find objects by their name.

Drag several components to the canvas.

When your dashboard gets more complex, not only will the data model in the spreadsheet grow, the number of components used on the canvas will also increase. Using groups to differentiate the canvas components from each other is a great way to stay in control of your dashboard.

You need a basic SAP BusinessObjects Dashboards file containing a few components in the canvas with some bindings to the data model in the spreadsheet.

As you can see, there are many roles that cells in the spreadsheet can play. If these were not color coded, you would be faced with a daunting task when updating the Excel model in the future. In our example, we colored calculations in green, external data input in blue, and component input data in yellow.

To make the data model readable, not only for yourself but also for others, it is helpful to create a legend in your spreadsheet that explains what each color represents. Any color scheme can be used, but it is important that you stick to the chosen scheme and use it consistently throughout the development of your dashboard.

It is important to create a separate worksheet that houses the legend, as seen in the following screenshot. You can also use this overall summary worksheet to include the information such as project name, description, usage, version (history), and so on.

You need a basic dashboard containing a few components in the canvas with some bindings to the data model in the spreadsheet. You can also just reuse the dashboard from the previous recipe.

Comments are related to one spreadsheet cell only and are shown if you hover over the cell. This is a great way to document information that you do not need to see all the time, and keeps your data model clean.

A little remark about the usage of comments: they increase the size of the SAP BusinessObjects Dashboards file a bit.

You can use the same basic dashboard as in the previous examples.

We will now discuss three more topics regarding spreadsheet borders: using the toolbar border button, using multiple worksheets, and placing Excel logic within the spreadsheet.

Using the toolbar border button

Instead of right-clicking on the cells and using the Format Cells option, you can also use the Border button on the toolbar to adjust the border styles for a cell or a group of cells. You can find this button in the Font section of the Home tab. If you select the cell(s) and click on this button, a list of options will be shown, which you can choose from.

Using multiple worksheets

You can use borders to split data within a spreadsheet. But if your dashboard contains data from a lot of different (functional) areas, it is recommended that you split your spreadsheet into several worksheets. This will help you to keep your dashboard maintainable.

A good strategy to split up the spreadsheet is to divide your data in different areas that correspond to certain layers or tabs that you created on the dashboard canvas. You can also use separate sheets for each external data connection. Give each worksheet a meaningful name.

You can use one of the dashboards from the previous recipes, or just create a new blank dashboard.

Using named ranges makes your formulas more readable, especially when you are working with multiple worksheets and using formulas that refer to cells in other worksheets.

In this section, we will discuss two ways to select and manage your named ranges.

Defined named ranges

Clicking on the little triangle in the Name Box will show a list of all your defined named ranges in all your worksheets.

The Name Manager

If you use a lot of named ranges, the Name Manager can be a helpful tool to manage your named ranges. Here, you can also edit and delete the existing named ranges. You can find the Name Manager under the Defined Names section of the Formulas tab or by using the shortcut Ctrl + F3.

For SAP BusinessObjects Dashboards developers, it happens very often when they have to copy colored cells that represent different types of logic such as dynamic visibility cells, insertion cells, and so on.

The Format Painter tool works by taking the source cell that you have selected and applying the formatting to the cell(s) that you paint to. This is very useful because we can copy cell formats without having to perform a Copy and Paste Special action every time.

An alternative to accomplishing the same task is to copy a cell and then click Paste Special... and choose the Formats option from Paste.

The Ctrl + ` hotkey works by showing the underlying formula of a cell. This is extremely useful if you are comparing formulas from multiple cells, as the developer does not have to flip between formulas in order to see what they are doing wrong when comparing multiple cells. Developers can quickly analyze their worksheet and find the cause of their problem.

The following screenshot shows the results of two Excel formulas in cells A1 and A2:

After using the Ctrl + ` hotkey, the formulas of both cells are displayed, as you can see in the next screenshot:

An alternative way to accomplish the same task is to go to the Formula Auditing section of the Formulas tab and then click on Show Formulas. Refer to the following screenshot:

This tip allows developers to quickly toggle between worksheets. Being able to quickly right-click and view a menu of all available tabs is faster than scrolling through each tab in order to reach tabs that are not visible. In addition, the ability to use a hotkey to cycle through each tab brings some time-saving benefits to those who are comfortable with using the keyboard to perform all their actions.