SAP NetWeaver BI 7.0 provides expanded options for formatting and reusing formatting in both the Web and Microsoft Excel (BEx Analyzer) worlds. In this second part of my three-part series, I address a new use for custom collections of styles called workbook themes. I also discuss Excel formatting, including the new formula mode. I did all my testing on release level 70015.

Note
In part one of this series I discussed styles in connection with default workbooks. In part 3 I will discuss the Web and its formatting options.

Themes

Just as styles are collections of formatting options, themes are collections of styles stored in a group, or theme name, on the SAP NetWeaver BI server. Normally administrators or experienced Excel people modify the styles, but instead of connecting the modified styles to a default workbook, you can create a theme for them and save them on the server.

Users allowed to save workbooks can select different themes to target different groups with differing color schemes. This control over styles does not affect the overall design because themes do not control the design elements, such as buttons or logos. These users can retrieve the styles and assign them to any workbook, thus overwriting the settings for any styles on the existing workbook. It doesn’t matter if the workbook started as a corporate default workbook or SAP’s delivered default.

The steps to create and use BEx themes are:

Step 1. Modify SAP-provided styles on a workbook

Step 2. Create a theme

Step 3. Load the styles from the workbook into the theme

Step 4. Apply the new theme to future workbooks

Step 1. Modify SAP-provided styles on a workbook. As I described in part 1 of this series, open the SAPBEXS.xla workbook and change your styles to identify good and bad data exceptions. When you click on a cell, the name of the style appears (Figure 1). Select the cell you want to change, then go to Format>Styles to change the SAP-supplied style.

Figure 1

SAP’s style example workbook (SAPBEXS.xla)

Step 2. Create a theme. After a workbook is active in the system with the styles you want, it is time to create your theme. You do not have to use the SAPBEXS.xla workbook to generate a theme, but using this workbook allows you to see all the style choices in one place without a query containing exceptions and highlighting. From the BEx design tool bar, click on the workbook settings icon and then click on the Themes tab (Figure 2).

Figure 2

Workbook settings in the Themes tab

Click on the New button and create a theme that uses the naming conventions of a letter in the beginning and all caps. In my example, I created the theme VI_THEME, which represents a theme for visually impaired users. Click on the OK button to save your theme.

Step 3. Load the styles from the workbook into the theme. In the Workbook Settings screen, highlight your new theme and click on the Apply button to load the styles from the current workbook to the theme (Figure 3). A pop-up screen appears to let you know the loading was successful. Click on the Save button to save your theme to the SAP NetWeaver BI server so that other users can select and use it. Another pop-up screen appears to let you know that the system saved your themes successfully.

Figure 3

Apply your styles to the new theme

Step 4. Apply the new theme to future workbooks. In this step you update the styles for the future workbooks via a theme, but you do not affect their overall designs. Access the Theme tab for your new workbook as you did in step 2 (Figure 2) and select Reapply Theme Before Rendering. This overwrites all the styles on the existing workbook with the same names as those contained in the theme. For example, say the VI_THEME theme has the SAPBEXexcBad9 style surrounded by a border and the SAPBEXexcGood1 style surrounded with a dot pattern. The border setting quickly changes to reflect the styles in the VI_THEME settings. The Apply New Styles option adds the styles contained in the theme that are not already in the workbook and does not overwrite the ones that are already there.

Excel Formatting

Now let’s move on to more formatting options for BEx Analyzer. This section requires Excel knowledge rather than SAP skills. If you use Excel for formatting, the system does not tag the cells of the analysis item with an SAP- provided style (e.g., SAPBEXexcBad9). Instead you tag the cells with styles or format them using Excel features.

To format in Excel, click on the design mode icon from the BEx Analyzer tool bar (Figure 4). With your cursor in the desired workbook sheet, click on the analysis mode icon to access the item’s properties.

Figure 4

Icons used with Excel formatting in BEx Analyzer

Go to the General tab shown in Figure 5 and uncheck Apply Formatting to disable SAP NetWeaver BI’s control over formatting via SAP-supplied styles. After you do this, the output of the analysis table is simple numbers on a normal Excel style of black on a white background. Now you can use Excel styles, or directly format cells, without SAP’s style overwriting them.

Figure 5

Analysis item properties

Other Excel Features for BEx Analyzer

You can use advanced Excel features, such as conditional formatting. You may also have specialized formatting needs that are not that widespread, and therefore do not require the complexities of a theme. To access the Excel advanced features, go to the Format menu in Excel. Use Microsoft’s Office Assistant (Help>Show the Office Assistant) to find out more about these features.

The only problem you may encounter occurs if you navigate the output using BEx features. The formatting comes exclusively from your workbook, so Excel does not know when you have exchanged a column or a row, and does not have the appropriate SAP styles controlling the formatting. Therefore, the format that you created manually with Excel features remains the same. For this reason, I think this kind of formatting override works best with fixed structures in the rows and columns of the query, or when navigation options are removed from the analysis item and workbook, making this kind of navigation impossible.

New BEx Analyzer Formula Mode

Although it is not a formatting feature, the new BEx Analyzer formula mode works well with the concept of Excel-based formatting. In SAP BW 3.x, and as a default in SAP NetWeaver BI 7.0, the analysis item returns data to Excel from the SAP NetWeaver Application Server (SAP NetWeaver AS) as an entire set. Each query or query view navigation checks with SAP NetWeaver AS and returns the appropriate grid of data.

You have another option in SAP NetWeaver BI. You can use the setting Use Formulas shown in Figure 5 or the corresponding context menu option Convert to Formula on the Excel output. These settings tell SAP NetWeaver BI to send each cell exactly the piece of data it wants. SAP NetWeaver BI then sends the data not as a grid in a collection, but as a cell-specific formula that asks SAP NetWeaver BI for information.

Figure 6 shows an example of the Use Formulas option. In this sheet, each cell has its own formula. The formula for cell F13, =BExGetData(“DP_1”,D$4,$C5), fetches the data from the data provider DP_1 (in this case, a normal SAP NetWeaver BI query). The formula then selects the cell containing Act 2001 (D4), the key figure (D$4), and the Canadian dollar ($C5). The value shown in cell F13 always reflects the same information, no matter where on the workbook you place it.

Figure 6

The formula mode output. Cell F13 reflects the same data as D5 because they both point to the same underlying formula.

One advantage to the formula mode is that each cell has its own formula and stands alone, which means that you can format each cell and organize these cells anywhere in the workbook you desire. The formula for each cell fetches the appropriate data from the server, while your theme and styles control what the workbook looks like.

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

You may contact the author at ned.falk@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.