In order to take advantage of the full range of features and include various techniques that could be implemented while working through these chapters, we'll discuss two examples of commonly used dashboards. This approach will help you streamline some of the actions that you have been undertaking.

The following are real-world dashboard examples explained in the form of recipes:

What-if scenario – Mortgage Calculator

In this recipe, we will create a what-if scenario dashboard. The purpose of the dashboard is to calculate and show the monthly payments and the total cost of a mortgage, based on a set of adjustable variables.

We will use techniques from the following chapters and recipes:

• Chapter 1, Staying in Control
• The Using sliders to create a what-if scenario recipe from Chapter 3, From a Static to an Interactive Dashboard
• The Selecting your data from a list recipe from Chapter 3, From a Static to an Interactive Dashboard
• The Illustrating single values recipe from Chapter 2, Data Visualization
• The Using a pie chart recipe from Chapter 2, Data Visualization
• Chapter 7, Dashboard Look and Feel

Getting ready

As we are starting from scratch, you only have to open a new SAP BusinessObjects Dashboard file.

How to do it...

1. The dashboard will contain four variables: Mortgage amount, Mortgage term in years, Yearly interest rate, and a variable that states whether the mortgage will be paid off by equal monthly payments (annuity) or just at the end of the mortgage term, which is the Monthly interest rate.
2. First, set up the spreadsheet. Make sure your spreadsheet looks like the following screenshot:
   
3. To calculate the monthly and total payments, we need the mortgage term in months, which is the number of years multiplied by 12. Add this Excel formula to cell B3: =B2*12.
4. To calculate the monthly interest rate, we need the formula =(1+B4)^(1/12)-1. Enter it into cell B5.
   

   Note

   Instead of using the ^ operator, you can also use the POWER Excel formula: =POWER(1+B4,1/12)-1.

5. Now drag three Horizontal Slider components to the canvas.
6. Bind the Data field of the first Horizontal Slider component to cell B1. Also set the Maximum Limit to 1,000,000. Enter Mortgage as the Title.
7. Select the second Horizontal Slider component and bind its Data field to cell B4. Set the Maximum Limit to 0.1. As we are dealing with percentages, the maximum limit is now 10% due to this setting. Enter Interest rate as the Title.
8. Go to the Behavior tab, and in the Slider Movement section, change the Increment to 0.001.
9. Select the third Horizontal Slider component and bind the Data field of this one to cell B2. Set the Maximum Limit to 30 and enter Period (years) as the Title.
   
10. Now we need to add some more logic to our spreadsheet to calculate the monthly payments. Adjust your spreadsheet as shown in the following screenshot:
    
11. Enter the following formula in cell B8 to calculate the monthly annuity:

    =B1*(B5/(1-(1+B5)^(-B3)))

12. Enter the formula =B3*B8 in cell B9 to calculate the total amount.
13. Enter the formula =B9-B1 in cell B10 to calculate the total interest amount.
14. In cell C8, enter the formula =B1*(B4^1/12) to calculate the monthly amount, which is only the interest.
15. Enter formula =B3*C8 in cell C10 and enter formula =B1+C10 in cell C9.
    
16. Add a Combo Box component to the canvas. We will use this component to determine whether the mortgage will be paid off in monthly installments. Bind the Labels field to cells B7 and C7. Go to the Behavior tab and set Item to Label 1.
17. Return to the General tab, and in the Data Insertion section, set the Insertion Type to Column. Bind the Source Data field to cell range B8:C10. Bind the Destination field to cell range D8:D10.
18. Finally, enter Monthly pay off? as the Title.
19. Go back to the spreadsheet and enter the formula =B1 into cell D11.
    
20. Now that the spreadsheet and all the selectors are set up, it is time to show some data in the dashboard. Add a Gauge component to the canvas.
21. Bind its By Range field to cell D8 and set the Maximum Limit field to 5000. Enter Monthly amount as the Title.
22. Add another Gauge component to the canvas and bind its By Range field to cell D9. Set its Maximum Limit field to 10,000,000. Enter Total amount as the Title.
23. Drag a Pie Chart component to the canvas. Bind its Values field to cells D10 and D11. Next, bind the Labels field to cells A10 and A11. Enter Division total amount as the Title.
24. Go to the Appearance tab and deselect Show Chart Background. Set the position of the legend to Bottom.
25. All right! The what-if section of the dashboard is now in place and ready to be tested. Preview the dashboard and play around with the sliders and selectors to see if everything works.
    
26. Leave the Preview mode. We will now adjust the layout of the dashboard so it looks a bit smoother.
27. First select the Phase theme from the Theme selector in the Format menu.
28. Use the Alignment options from the Format menu to adjust the placement of the three sliders and the selector.

    Tip

    You can also use the Grid to help with alignment and positioning. You can activate the Grid in Preferences in the File menu.

    
29. Add a Rectangular component and resize it so it will fit over the sliders and selector. Change the Border Color into a lighter color; for example, gray.
30. Add a Label component to the canvas and enter Mortgage Calculator in the Enter Text field. Select the Appearance tab and go to the Text sub-tab. Select Bold and set the Text Size to 28. Make sure you resize the Label component if the text doesn't fit anymore.
    
31. Select the Pie Chart and the Gauge components. Align them by Middle and Space Evenly Across.
32. As you can see, the title of the Pie Chart is placed a bit higher than the titles of the Gauge components. Select both Gauge components. Go to the Appearance tab and select the Text sub-tab. Now adjust the Y Offset so all titles will have the same height.
    
33. Select Value. In the Format Selected Text section, select Bold and adjust the Y Offset so the values of the Gauge components will be at the same height as the legend of the Pie Chart component.
34. Go to the Behavior tab and deselect the Enable Interaction option.
35. Add another Rectangular component to the canvas and place it over the Gauge components and Pie Chart.
    
36. Select Fit the Canvas to Components from the Canvas Sizing options in the View menu. You can also use the buttons from the Standard Toolbar. Select the Increase Canvas option twice.
    
37. Your what-if dashboard is complete!

How it works...

• In steps 1-4, 10-15, and 19, we utilized what we learned in recipes from Chapter 1, Staying in Control, to properly set up the spreadsheet
• In steps 5-9, we set up the sliders like we did in the Using sliders to create a what-if scenario recipe from Chapter 3, From a Static to an Interactive Dashboard
• In steps 16-18, we used the Selecting your data from a list recipe from Chapter 3, From a Static to an Interactive Dashboard, to define the Combo Box component to determine whether the mortgage is paid off or not
• Steps 20-24 used recipes Illustrating single values and Using a pie chart from Chapter 2, Data Visualization, to show the data in two gauges and a pie chart
• In the final steps, we used what we have learned from recipes in Chapter 7, Dashboard Look and Feel, to implement a different dashboard theme and fine-tune the look of the dashboard