SAP BusinessObjects Dashboards 4.0 Cookbook

By David Lai , Xavier Hacking
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Free Chapter
    Staying in Control
About this book

Xcelsius 2008 was recently included in SAP’s BusinessObjects 4.0 family, rebranding “Xcelsius Enterprise” as “SAP BusinessObjects Dashboards 4.0”. With features like flexible design and what-if scenarios, the powerful dashboarding software allows enterprises to make business decisions at a glance, and this book allows you to go far beyond the basics of these techniques.

This cookbook full of practical and applicable recipes will enable you to use the full latest capabilities of Dashboard Design to visually transform your business data. A wide range of recipes will equip you with the knowledge and confidence to perform tasks like configuring charts, creating drill- downs, making component colors dynamic, using alerts in maps, building pop-up screens, setting up What-If scenarios, and many more.

The recipes begin by covering best practices for using the Dashboard Design spreadsheet, the data-model, and the connection with the components on the canvas, later moving on to some from-the-trenches tricks for using Excel within Dashboard Design. The book then guides you through the exploration of various data visualization components and dashboard interactivity, as well as offering recipes on using alerts, dashboard connectivity, and making the most of the aesthetics of the dashboard. Finally, the recipes conclude by considering the most important add-ons available for Dashboard Design and enabling you to perform relevant and useful tasks straight away.

Publication date:
May 2011


Chapter 1. Staying in Control

In this chapter, we will cover:

  • Making the spreadsheet more readable with colors

  • Making the spreadsheet more readable with comments

  • Making the spreadsheet more readable with borders

  • Using Named Ranges

  • Selecting all worksheet cells with one click

  • Copying the format of one cell to another cell or range

  • Debugging the spreadsheet

  • Navigating between worksheets

  • Grouping canvas components



During the development of a typical Dashboard Design dashboard, the number of components on the canvas increases steadily and the exact type of used components may change over time. Also, several interactions between components are added and maybe one or more connections with external data sources are created. All those canvas components are bound to the Excel data model that has been defined in the spreadsheet area.

To prevent us from getting lost in an unmanageable chaos of components, interactions, bindings, and several different Excel functionalities, a structured approach should be followed right from the start of the dashboard development. Also, we should use the advantages Excel gives us to build an optimal data model that is easy to read and maintain.


Making the spreadsheet more readable with colors

To make clear what the exact purpose of a cell is we need a set of guidelines to follow.

Getting ready

You need a basic Dashboard Design dashboard containing a few components in the canvas with some bindings to the data model in the spreadsheet.

How to do it...

  1. Go to your data model in the spreadsheet.

  2. Select the cell(s) you want to color.

  3. Click on the Fill Color button and select the desired color. You can find this button in the Font section of the Home tab.

  4. Color the cells that have dynamic visibility values in orange.

  5. Color the cells with input values from canvas components yellow. In the following screenshot, row A3:N3 is used as the destination range for a drill down from a chart.

  6. Color the cells that will be filled with data from an external data source in blue.

  7. Color the cells with Excel formulas in green.

How it works...

A cell in an Excel spreadsheet can have several different roles. It can contain a fixed value, it may show the result of a (complex) formula, and it can be used by formulas in other cells. Within Dashboard Design, an additional role can be recognized—the insertion role. In this type of cell, an interaction from an Dashboard Design component results in a certain value being inserted into this cell.

There's more...

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 dashboards.

Make sure that you add another worksheet to your spreadsheet to place this legend in. You can also use this overall summary worksheet to include the information such as project name, description, uses, version (history), and so on.


Making the spreadsheet more readable with comments

Sometimes cells need additional information to explain how they are used. Of course you can write this text as a label in another spreadsheet cell, but a better solution is to use comments.

Getting ready

You can use the same basic dashboard as for the previous recipe.

How to do it...

  1. Right-click the cell to which you want to add the extra information.

  2. Choose Insert Comment.

  3. Add your text. A small red rectangle will appear in the right upper corner of the cell.

  4. Now hover your mouse over the cell and the comment you just entered will appear.

How it works...

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

A good case to use this is when you are building a dashboard with multiple layers in combination with dynamic visibility. You might use numbers to identify each layer and use a cell to contain the number of the active layer. You can use a comment to describe the meaning of these cell values.

A little remark about the usage of comments is that they do increase the size of the Dashboard Design file a bit.


Making the spreadsheet more readable using borders

To separate cells from each other and create different areas within a spreadsheet, you can use cell borders.

Getting ready

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

How to do it...

  1. Select the cell(s) you want to add a border to and right-click.

  2. Now select Format cells....

  3. Go to the Border tab.

  4. Select the desired style of the border line.

  5. Select on which side(s) of the cell(s) the border should appear.

  6. Click on OK.

There's more...

Toolbar border button

Instead of right-clicking 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 Border 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.

Use multiple worksheets

You can use borders to split data within a spreadsheet. But if your Dashboard Design dashboard contains data from a lot of different (functional) areas, it is recommended that you split your spreadsheet in several sheets. 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 areas, 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.

Place your logic wisely

Another general guideline is to place as many cells with logic and Dashboard Design interactivity functionality at the top left of the spreadsheet. This place is easy to reach without a lot of annoying scrolling and searching. Even more importantly, your data set may grow (vertically and/or horizontally) over time. This can especially be a risk when you are using an external data connection, and you don't want your logic to be overwritten.


Using named ranges

With named ranges , it is possible to define a worksheet cell or a range of cells with a logical name.

How to do it...

  1. Select a range of cells (for example B1:B12).

  2. Put a description (for example Total_Sales) for this range in the Name Box in the upper left-hand side of the worksheet.

  3. Now this named range can be used in formulas in other worksheet cells. Type the formula =SUM(Total_Sales) in cell B13.

How it works...

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

There is more...

Defined named ranges

By clicking on the little triangle in the Name Box, it will show a list of all your defined named ranges in all your worksheets.

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 Formulas tab or by using the shortcut Ctrl-F3.


Selecting all worksheet cells with one click

With a click of the mouse button, we can select everything on a worksheet.

How to do it...

If you want to select an entire worksheet without having to drag your mouse to select everything, you can just click on the half triangle on the top left corner of the worksheet.

How it works...

Clicking on the half triangle button will allow you to select the entire worksheet with just one click. Here are some reasons why we would want to select an entire worksheet:

  • With an entire worksheet selected, you can easily apply formatting or change attributes to all cells.

  • You can copy the selection from one workbook and paste it onto a worksheet from another workbook. A common use would be copying data elements from an external spreadsheet and pasting it onto the internal Dashboard Design spreadsheet.

  • You can easily perform row height or column width formatting that will apply to all rows or columns in that spreadsheet.

There's more...

Another way of selecting everything on a worksheet is using the Ctrl+A shortcut.


Copying the formatting of one cell to another cell or range

This recipe shows you how to copy the formatting of one cell to another cell or range. For example, we can copy a yellow background and Calibri font from cell A1 to cell A2.

How to do it...

  1. Click on the source cell that you want to copy the formatting from.

  2. Click on the Format Painter icon which you can find on the Clipboard section of the Home tab.

  3. Click on the cell or range that you want to copy the source cell's formatting to.

How it works...

The Format Painter 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 every time.

For Dashboard Design developers, it happens very often where we have to copy colored cells that represent different types of logic such as dynamic visibility cells, insertion cells, and so on.

There's more...

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


Debugging your spreadsheets

It is common that Dashboard Design developers may accidently put in the incorrect formula when developing logic on their spreadsheets. Using the Ctrl+` hotkey will make things much easier.

How to do it...

  1. Select the worksheet you want to see formulas for.

  2. Hit the Ctrl key and ` (grave accent) key together.

  3. You will see the value in the cell change to the formula.

How it works...

The hotkey Ctrl +` works by showing the underlying formula of a cell. This is extremely useful if you are comparing formulas from multiple cells, the reason being that 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 quickly.

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:

There's more...

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


Navigation between worksheets

It is often the case that when we are developing Dashboard Design dashboards, we run out of room for our worksheet tabs. We then have to scroll through each tab in order to get to the one that we want; this is kind of a pain. To access tabs that are not visible, we are used to pressing the arrow keys to move to the desired tab.

How to do it...

  1. To have all tabs displayed in one menu, right-click on the tab at bottom, left-hand side navigation area. You will then see the list of tabs that you can choose from.

  2. You can also use Ctrl + PageUp, which will move to the previous sheet in your workbook.

  3. Ctrl + PageDown will activate the next sheet in the workbook.

How it works...

The methods explained work by allowing developers to move between worksheets more quickly. Being able to quickly right-click and show 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 savings benefits for those who are comfortable with using the keyboard to perform all their actions.


Grouping the canvas components

Canvas components can be grouped with one or more other components.

Getting ready

Drag several components to the canvas.

How to do it...

  1. Select the components that you want to group by either selecting them by dragging the mouse over the components, or clicking the components one-by-one while holding the Ctrl button on your keyboard.

  2. Right-click anywhere on the canvas and select Group from the context menu. You can also use the shortcut Ctrl + G to group these components. As you can see, the components are now a group with a common border (Dynamic Visibility and Entry Effect):

    And some shared preferences (Dynamic Visibility and Entry Effect):

  3. If you create a lot of groups of components, we advise that you name these groups to prevent you from getting lost and confused during the dashboard development. First go to the Object Browser.

  4. Select the group you want to rename.

  5. Double-click the group or right-click and select Rename from the context menu.

  6. Type in the new name for this group.

How it works...

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.

There's more...

Besides browsing through your (grouped) components, the Object Browser has two additional options which come in very handy during the development of a complex dashboard.

Hiding components

Firstly, you can hide components and/or groups of components, which will make your life easier if you are using a lot of overlaying components. By checking Hide for some components, you won't be bothered by these components and you can work with the components that are unhidden.


There is one thing you should keep in mind: If you hide a component that is part of a group and the group itself is unhidden, the complete group will still be movable and its properties will be changeable.

Locking components

Secondly, the Object Browser gives us the possibility to lock one or more components and/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 it.

Hiding or locking components is easy. In the Object Browser you will see two symbols with two columns of dots beneath them. The first column is the 'hide' column; the second one defines which components are locked. To hide or lock a component, you just have to click on the correct dot in the row of the component. The dot will be replaced with a checkmark. In the following screenshot, you can see that the Budget group is hidden while the Actual group is locked:

About the Authors
  • David Lai

    David Lai is a SAP BusinessObjects consultant and specializes in data visualization and data warehousing. He graduated with a degree in computer engineering from the University of Toronto. He has a passion for providing organizations with smart Business Intelligence solutions that encompass best practices and techniques. In addition, he is an active contributor to the community and provides his knowledge in best practices and solutions.

    He started his Business Intelligence blog ( in 2008, where he provides tips, tricks, and best practices for Xcelsius and BusinessObjects-related material. He is a bronze-level contributor to the SAP community network, has presented at SAP InsideTrack, and provides BusinessObjects training to students.

    Aside from work, David enjoys physical activities such as weight training, basketball, volleyball, and skiing. He also has a strong passion for Latin dance.

    David is the owner of Xinfinity Solutions, where he provides consulting services as a SAP BusinessObjects consultant. He has worked for a long list of satisfied clients in various industries.

    Browse publications by this author
  • Xavier Hacking

    Xavier Hacking is an SAP BI specialist from Eindhoven, the Netherlands, and works as a consultant for Interdobs. He has a master's degree in industrial engineering and management science from the Eindhoven University of Technology. He has worked with a wide range of products from the SAP Business Intelligence portfolio, including SAP BW, SAP BusinessObjects Dashboards, Design Studio, Web Intelligence, Crystal Reports, and the SAP BusinessObjects BI Platform. His main focus is dashboard development within the various SAP environments.

    Xavier is the coauthor of the first edition of this book and the book Getting Started with SAP BusinessObjects Design Studio, SAP Press. He writes for the SAP Experts BI Hub and has his own blog related to Business Intelligence at ( You can follow Xavier on Twitter (

    Browse publications by this author
SAP BusinessObjects Dashboards 4.0 Cookbook
Unlock this book and the full library FREE for 7 days
Start now