One of the most common financial planning scenarios is Cost Center Planning. Once the top- level budgets are distributed to the respective cost centers (or cost center groups), the individual cost center managers, planners, or department heads split this budget into a more granular level of cost elements and other planning objects. These detailed planning layouts are specific to that cost center (or department). A predefined EvDRE ([Everest] Data Range Exchange) template is an ideal starting point for the planners. They can reuse an existing template and make adjustments and enhancements to suit their specific cost center needs. This saves considerable effort in learning and development 

EvDRE transfers a range of data at a time, thereby optimizing the network bandwidth. This makes it a preferred choice over cell-specific functions. Such optimization is critical to a global business planning scenario, which covers a range of bandwidth situations. 

SAP BusinessObjects Planning and Consolidation offers dynamic report and input schedule templates with predefined formats. You can use these dynamic templates as a baseline for creating custom reports or input schedules. The EvDRE function generates a control panel automatically as part of a template for reporting requirements. The control panel contains built-in expansion functions that automatically create the correct MDX queries to run against the database. Thus, the control panel is helpful in customizing the format and content of the template.

Why EvDRE Is Preferred Over Other Query Functions

EvDRE offers superior performance and scalability over other (legacy) query functions, such as EvGET, EvGTS, and EvSND because it supports building both reports and input schedules. Furthermore, EvDRE unites and extends the functionality of EvGET and EvSND. The workbook options in the control panel determine how the EvDRE function exchanges data between the report and the underlying database. In addition, multiple EvDRE functions used with the PageKey range as a reference resolve the problem of inconsistent data in multiple applications.

Legacy functions execute as individual queries for each cell, whereas EvDRE works with an array of data. EvDRE executes as a single query for sending and retrieving data for the entire data range. For example, if you have a 5x6 datarange, you only have one query to populate the cells with EvDRE and 30 queries (one for each cell) to populate with the other functions. EvDRE provides optimum performance and consumes less memory with large reports or input schedules.

Using EvDRE, you can build static (without expansions) and dynamic (with expansions) workbooks, or workbooks with both static and dynamic dimensions members. You can define one or more expansions for the rows and columns, or you can define expansions for both simultaneously.

EvDRE also allows a multitude of formatting options, even though the data is returned as an array. The formatting options include different formatting for default records and calculated members and criteria-based color coding. EvDRE also supports a number of conditional formatting options that you can apply to the data, such as zero suppression, sorting, and hiding of blank records or columns. The EvDRE option supersedes the workbook option because it is more specific to the datarange. This resolves any data and formatting conflicts during expansion. EvDRE also resolves data conflicts when multiple concurrent users try to modify and save data to the database.

The sequence of events while executing an EvDRE expansion is fine-tuned in SAP BusinessObjects Planning and Consolidation 7.0 to ensure that formatting instructions for the format range do not override the before and after formats of the ranges. If some part of the data comes as preformatted, the format instructions do not alter predefined formats. In addition, EvDRE can query either the OLAP InfoCube or relational database depending on which approach is most efficient for the given request. Legacy functions can only access the OLAP database.

The EvDRE datarange contains only actual functions and values that are populated by the query. This allows for more flexibility when using Excel functions within the range. Furthermore, you don’t run into data overlapping issues.

Highlights of EvDRE in Version 7.0

EvDRE helps you create highly formatted templates and reports for analysis purposes. Version 7.0 offers significant improvement in the way EvDRE generates templates compared to version 5.0 (Table 1).

Let’s take a look at the features in version 7.0 in detail.

• EvDRE Builder enhanced UI: Version 7.0 includes a new interface for building reports and input templates with EvDRE Builder. When you enter =EvDRE( ) in the top left cell in a blank SAP BusinessObjects Planning and Consolidation worksheet and click the refresh icon, the EvDRE interface opens as shown in Figure 1. From this UI, you can initialize all the ranges for EvDRE. We explain how to use this screen in the “Create an EvDRE Report with EvDRE Builder” section later.

Figure 1

EvDRE Builder dialog box for designing the EvDRE template

• Custom style sheets: You can use custom style sheets as reusable templates in version 7.0. Generally, business users face difficulty in building formatted templates from scratch for their planning and analysis purposes. Using EvDRE, planners can reuse the generic planning templates that the IT teams have created for them. You can now build a set of reports or input schedules using an organization-specific style sheet. You can apply a personalized style sheet to another report by selecting the appropriate import style option in Figure 1.
• New EvDRE options: You can use EvDRE options to set preferences for refreshing, recalculating, and expanding data in the report or input template. In the KeyRange Options row, enter the desired options (e.g., SumParent, AutoFitCol, or NoSend) with a comma as a separator. The system sets the selected options in the option range.  

When you select the Allow options check-box in the EvDRE Builder (Figure 1), you can access the new option range for setting up the new EvDRE options for the template. In the option range screen, enter a Y for the options you want to activate (Figure 2).

Figure 2

EvDRE option range that you use to set options in the template

• New formatting range options: By using the format range, you can select a set of cells that contain a desired format and apply that format to the EvDRE datarange. The formatting range also supports empty members in row and column definitions to allow for flexible formatting. Select the Allow formatting check box in the EvDRE Builder in Figure 1 to access the formatting range.

You can create different formats for different dataranges in the report by using the formatting range. For example, in a formatted financial statement, certain headings and subtotals have different formats to differentiate them from each other. You can use the formatting options to represent those headings and subtotals. The format range represents the list of parameters that collectively define the formatting instructions. As shown in Figure 3, you can define a set of formatting instructions that apply different formats to different components in the template such as the heading, key, and calculated data cells in the report. When multiple formatting instructions are present, EvDRE executes them in sequence from top to bottom.

Figure 3

The formatting range in which you define the template formatting

• 3D expansions or expansion across the sheet: In 3D expansions, the expansion replicates the sheet, thereby defining the expansion in several sheets, one for each member. For example, if you have a huge dataset that can’t fit into an Excel sheet or one that needs to be represented as a book of reports, then you should use the 3D expansion. Each worksheet contains a replica of the starting EvDRE function, in which the selected dimension member is indicated in the PageKey range (hard-coded), which is the member ID for that sheet.

When you use 3D expansion across the sheet, the following restrictions exist: 

• There must be only one EvDRE function in the report or input schedule
• Only one dimension can be expanded across sheets
• If the BeforeRange and AfterRange parameters coexist with the 3D expansion, the 3D expansion takes precedence and overrides the BeforeRange and AfterRange parameters
• The Insert parameter for the sheet expansion in Excel is ignored because the EvDRE expansion decides various sheets in the report

Figure 4 shows the result of a 3D expansion performed on Entity SalesUS and its child members. The expansion basically replicated the sheet defining the expansion (sheet for SalesUS) in several sheets (one sheet per expanded member). In the PageKey range, the entity dimension value is hard-coded for each member.

Figure 4

A 3D expansion performed on the entity SalesUS

• Level-based MemberSets: In version 7.0, the level-based keywords (e.g., LBAS, LDEP, and LMEMBERS) can support a third parameter Y indicator. When the Y parameter is set, only the list of members for a selected level are expanded without displaying intermediate members. This feature is mainly used in scenarios in which hierarchical expansion for various group accounts is displayed, such as sales expenses or commissions.

Figure 5 displays an example in which MemberSet has the level-based keyword LDEP set with the Y indicator. In the resultset in Figure 6, the expansion displays all the level 2 members (e.g., SALESUSNEAST and SALESUSNCENTRAL) without displaying the level 1 members (SalesUS).

Figure 5

The Y indicator set for MemberSet

Figure 6

The expansion shows only the level 2 members of Sales US

• New sorting key range (BeforeRange and AfterRange): If you select the Allow sorting option in Figure 1, the key range contains SortRange, which points to a range of cells (four rows and four columns). Using SortRange, you can define how you want the system to sort the report data. The first parameter in SortRange indicates the dimension or column that you want to sort and the second parameter indicates sorting order (ascending or descending). In SortRange, you can set BeforeRange and AfterRange parameters, which define a range of cells that should be inserted in the report above (BeforeRange) or below (AfterRange) each change of value in the sorting criteria (Figure 7).

Figure 7

SortRange with the Column and Order defined

• EvSUM and EvSUB functions: These functions are executed along with the related BeforeRange and AfterRange parameters. At the time of EvDRE expansion, these functions turn into Excel formulas that perform the specific calculation of related members. For example, if you want a new column that calculates the sum of the column data values, add an EvSUM formula.

In version 7.0, a new variation called EvSUB was added, which you use in a similar manner to EvSUM. Using EvSUB, a developer can perform mathematical operations such as AVERAGE, COUNT, and SUM on the datarange. For example, if you require a new column to calculate the average of the column data values, use EvSUB.

When you use EvSUB (Func_no) in an SAP BusinessObjects Planning and Consolidation report, it is converted into the following Excel formula format:

= SUBTOTAL (Func_no, datarange)

In this formula, Func_no is the mathematical operation that the system performs on the datarange. Table 2 shows several functions for this formula.

• Insert member functionality: Version 7.0 offers a new UI option that allows you to insert a member before the current row/column of the EvDRE report. As shown in Figure 8, in the expansion range, enter Y in any row or column to perform a runtime insertion of a suppressed row/column after the expansion. You can insert any suppressed row or column by right-clicking the desired key cell and selecting EvDRE: Insert member.  

Figure 8

The Y indicator in the Insert row

Create an EvDRE Report with EvDRE Builder

The EvDRE reporting function has three arguments: two required parameters (AppName and Key range) and an optional parameter (Expand range).

• AppName: The name of the application from which you retrieve or where you send data
• Key range: The cell range that displays the dimension IDs that are either static or dynamic from the current view
• Expand range: The definition of the expansion to perform

To create an EvDRE report, you can use EvDRE Builder or drag-and-drop reporting functionality. The EvDRE Builder tool allows you to create reports and input schedules by selecting dimensions in rows and columns, as well as specifying report details. It allows you to design the report layout to meet your business needs by using Options, Format Range, and Sort Range settings in the control panel.

Some of the key features of the EvDRE Builder are:

• Advanced formatting
• Advanced sorting options
• Multiple EvDRE tables per sheet
• Expand by sheet function
• Expansion across sheets possibility

In a blank SAP BusinessObjects Planning and Consolidation Excel worksheet, enter =EvDRE( ) in the top left cell (Figure 9). Click the refresh icon on the toolbar (circled). EvDRE Builder opens a dialog box and allows you to select the dimensions in rows and columns (Figure 1). You can also enter formatting, sorting, and options settings for the report design. After making the necessary adjustments, click the OK button.

Figure 9

Building template using EvDRE Builder

The default settings in the EvDRE Builder dialog box contain one time dimension column and one account dimension row. In addition, the member selection includes SELF and DEP for displaying self and dependant members in the generated report (Figure 1). After clicking OK, EvDRE Builder automatically generates a report formatted with real-time data.

For Grid Design, you have the following options available in EvDRE Builder:

• Allow expansions: If this check box is set, it automatically triggers an expansion in the EvDRE Excel sheet
• Allow options: This check box automatically generates an OptionRange
• Allow formatting: This is an optional setting in the EvDRE Builder that you can use to select a default formatting style that is generated automatically or to import the style from a user-defined workbook (either local or server-based)
• Allow sorting: This check box allows data-range sorting depending on a specified dimension

You can modify a report created using EvDRE Builder by using the drag-and-drop method or by using the EvDRE control panel (Figure 10). Report modifications can include modifying dimensions, report expansion, report range, report formatting, and report sorting.

Figure 10

EvDRE control panel for template modification

Alternates to EvDRE Builder

As an alternate to EvDRE Builder, you can build an input template with only one function call (where you set all the options using comma-delimited instructions) or with a cell range listing all possible options. You can turn these options on or off independently in the EvDRE control panel. This is a manual method of building EvDRE templates. Some developers prefer a manual method which is similar to using SAP BusinessObjects Planning and Consolidation version 5.0.

Additionally, the older method of passing the number of expansions as parameters of the function is also supported in SAP BusinessObjects Planning and Consolidation 7.0. When more than one expansion on either axis is needed, you can use the EvDRE function with an alternative syntax:

=EvDRE(“c x r”) where           

• c is the number of expansions in columns
• r is the number of expansions in rows

In the example in Figure 11, the EvDRE function creates an input template with two expansions in columns and three expansions in rows.

Figure 11

Building an EvDRE template with more than one expansion in rows and columns

Drag-and-Drop Reporting

Drag-and-drop reporting is another method for creating reports and input schedules. This alternate method for building EvDRE templates was available in the earlier versions of SAP BusinessObjects Planning and Consolidation. The SAP BusinessObjects Planning and Consolidation Action pane includes an option for designing the report structures and contents via drag and drop. The generated report is based on the EvDRE function, so you can modify it by using the drag-and-drop interface or the EvDRE control panel.

To create a report using drag and drop, select Reporting & Analysis from the Action Pane in Excel. Click Build a report using drag & drop (Figure 12).

Figure 12

Opt to build an EvDRE template using the drag-and-drop method

Drag the data grid object to a cell in the top left corner of the EvDRE data grid. Select the dimension name for the row or column (Figure 13) and then use the screen elements to define the report (Figure 14). You can preview your report to see what the final layout looks like (the preview option is unique to the drag-and drop method).

Figure 13

Options available while designing the report via the drag-and-drop method

Figure 14

Edit MemberSets allows you to change the dimensions and MemberSet properties

Things to Do (and Not Do) with EvDRE

Here are few suggestions to help you avoid errors and improve performance with EvDRE.

Do

• Use more than one EvDRE function in a worksheet for faster data retrieval and better performance
• Use fewer rows and columns (no more than 1 x 2) when you have multiple EvDREs in one report
• Use EvDRE when the report contains only base members in ColKeyRange, RowKeyRange, and PageKeyRange. Otherwise, use a SQL query.
• Use the options in EvDRE Builder for better performance
• Select the Expand on workbook open or the Refresh on workbook open options in the workbook options from Action Pane because it refreshes the data and formatting per the EvDRE settings
• Reuse formatted EvDRE templates to ensure consistency and decrease development efforts
• Create a custom refresh button with VBA code when your report contains multiple EvDRE worksheets
• Select members from a validation list instead of selecting them from the current view. This improves the EvDRE template performance.
• Use EvSUB in the Sorting KeyRange, and not in expansion range because the EvSUB option does not work in BeforeRange or AfterRange

Don’t

• Don’t use overlapping ranges in reports with multiple EvDREs because they can cause the last executed EvDRE to overwrite the data of the previously executed EvDRE functions
• Don’t use conditional EvDRE in a worksheet because it decreases the report performance. You can carry out conditional execution by embedding EvDRE in an Excel formula or by using VBA code.
• Don’t use the CellKeyRange option for complex reporting needs when you need to define individual members for each cell in the EvDRE datarange. Using CellKeyRange slows down the data exchange.
• Don’t use more than one SAP BusinessObjects Planning and Consolidation function unless absolutely necessary. Apart from poor performance, this could lead to problems with the order of execution.
• Don’t retrieve members that contain many dimensions on one axis. This slows down the response.
• Don’t use EvDRE for simple calculations (such as summation), which you can perform easily by using Excel formulas in PageKeyRange
• Don’t use multiple EvDREs to retrieve large amounts of similar data. Instead, use a worksheet for the data dump and use Excel formulas to segregate the data among different tabs.

Smruti Pattani

Smruti Pattani is an SAP NetWeaver BW and SAP BusinessObjects Planning and Consolidation solution specialist with The Principal Consulting. She has more than six years of business analysis and SAP application development experience. She has worked on multiple implementations building solutions using SAP tools such as SAP NetWeaver BW, SAP BusinessObjects Planning and Consolidation, and SAP BusinessObjects. Her strong analysis and technical skills have helped clients map their business requirements to SAP technology.

You may contact the author at smruti.pattani@tpcus.com.

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

Anupam Gupta

Anupam Gupta works as a practice manager at The Principal Consulting (TPC), managing its enterprise performance management (EPM) practice. He has more than 10 years of development and global consulting experience with SAP solutions in the areas of financial planning, reporting, and consolidation. He has significant experience advising clients on IT strategy and technology road maps. He is responsible for the development of EPM competencies, knowledge management assets, and TPC-developed solutions spanning a range of SAP products such as SAP NetWeaver BW, SAP BusinessObjects, SAP Business Planning and Consolidation, and SAP Strategic Enterprise Management.

You may contact the author at anupam.gupta@tpcus.com.

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