SAP BusinessObjects Planning and Consolidation

SAP BusinessObjects Planning and Consolidation
  • SAP BusinessObjects Planning and Consolidation

  • Views 15

  • Downloads 0

  • File size 3MB
  • Author/Uploader: harikishore66

SAP BusinessObjects Planning and Consolidation Planning and Consolidation provides everything you need to meet your bottom-up and top-down financial and operational planning requirements, as well as complete consolidation and reporting through a single application and user interface.

Integration There are two versions of the software: a version for the Microsoft platform and a version for SAP NetWeaver. While this application help focuses on the Microsoft platform version, there may be some notes related to the SAP NetWeaver version. You can ignore these notes. Certain client components are integrated with Microsoft Office (Word, Powerpoint, and Excel). Other client components are based on Windows or Web technologies.

Features The SAP Library documentation for the system includes:  Getting Started — Describes the connection wizard and other logon tasks, the interfaces available (Administration, Excel, Word, PowerPoint, and Web), how to set the overall language for the system, and where to find more information, specifically, technical information.  Administration — Provides instructions for administrators about setting up and maintaining Planning and Consolidation applications.  Data Management — Provides instructions about moving data into and out of the system, and about copying and moving data within and across applications.  Business Process Flows — Provides information about using Business Process Flows (BPF). For information about setting up BPFs, see the Administration section.  Reports and Input Schedules — Provides instructions about building, distributing, and publishing reports, and about collecting data and submitting it to the database using input schedules.  Journals — Provides information about creating and posting journal entries to adjust data in the database.  Functions Reference — Provides reference information on using the Planning and Consolidation functions in your reports and input schedules.

More Information More Information

Getting Started You can start Planning and Consolidation from the Windows Start menu.

Prerequisites To access the Web or Server Manager interfaces, you have installed the Planning and Consolidation server components. To access the Excel, Word, or PowerPoint interfaces, you have installed the Planning and Consolidation for Office client. To access the Administration interface, you have installed the Planning and Consolidation Administration client.

Activities To start the program, select SAP BusinessObjects Consolidation from the Windows Start menu.

Planning and Consolidation

Planning and

More Information Interface for Office Interface for Word or PowerPoint Interface for the Web Interface for Administration

Interface for Office The Interface for Office client includes interfaces for Microsoft Excel, Word, and PowerPoint. These interfaces leverage your familiarity with the Microsoft Office applications of Excel, Word, and PowerPoint, enabling you to collect, analyze, and store financial data and efficiently distribute enterprise performance management reports. The Interface for Excel is the primary interface you use, complemented by features of the Interface for Word and Interface for PowerPoint.

Features With Interface for Office, you can perform the following tasks:  Utilize the powerful and flexible formulas and functions within reports and input schedules to retrieve, display, and submit data for a real-time view of the financial position of your organization.  Use predefined report and input schedule templates that you can customize to meet your specific business requirements.  Instantly change the information you see in a report or the entities, accounts, time period, and so on, of input data simply by changing your current view.  Display accurate, live data from the database within Microsoft Excel worksheets, Microsoft Word documents, and Microsoft PowerPoint slides.  Analyze data in reports, perform data entry in input schedules, and distribute information based on user access rights when you are completely offline from the system.  Submit budgets with a wide range of supporting attachments in the form of spreadsheets, documents, and presentations.  Create hypothetical scenarios of future outcomes using powerful modeling functions.  Post journal entries to carefully track changes to your data.  Schedule and run Data Manager packages for loading, transforming, and manipulating financial data.

Activities To start one of the interfaces, select one of them from the launch page, or from another module (such as Interface for the Web or Administration), expand Available Interfaces in the action pane and make your selection. NOTE If you see a prompt to update files, select Yes.

More Information Reports and Input Schedules Interface for Word and PowerPoint Journals Data Management for the NetWeaver platform or Data Management for the Microsoft platform

With Planning and Consolidation reporting, you can retrieve requested data from the database into the Excel interface. You can analyze your multidimensional data using the current view, which is available on the action pane. The system provides a variety of report templates to help you design your own reports. Input schedules allow you to send data directly from your spreadsheet to the databases. Input schedules contain all of the same formatting and functionality as reports. The difference is that reports are used for analysis purposes, and input schedules are used for writing data to the database. Data can be written to base-level members to which you have write access.

More Information Creating Reports and Input Schedules Common Report and Input Schedule Features Dynamic Report and Input Schedule Templates EvDRE Builder Drag and Drop Reporting Live Reporting Data Submission

Creating Reports and Input Schedules When you create new reports and input schedules, you choose from the following methods:  Opening a dynamic template that Planning and Consolidation provides. See Dynamic Report and Input Schedule Templates.  Using the EvDRE Builder. See EvDRE Builder.  Using the drag and drop interface. See Drag and Drop Reporting.  Starting from a blank workbook. See Functions Reference.

Activities Saving Reports Locally You can save a report locally to the My Reports or My Schedules folder by choosing Save to My Reports folder in the Report State action pane. Saving Input Schedules Locally You can save an input schedule locally to the My Schedules folder by choosing Save to My Schedules folder in the Schedule Options action pane. Saving Reports and Input Schedules as Templates You can save a report or schedule as a template by selecting eTools Save Template Library . (You must have authorization for this task). After a template is saved, you can make it available for downloading to other users’ machines by updating the template version number.

More Information See Setting Template Version in Application Set Management.

Dynamic Report and Input Schedule Templates The system provides several templates within the sample application set, ApShell. You can choose the template you want to use, and then customize it to meet your needs.

Features You can save reports locally, or save them as a template on the server (if you have administrator rights). If you modify an existing report template or create a custom report, you can save it as a template for

future use. Only administrators with the appropriate rights can save templates on the server. However, you can modify a template and save it locally for your own use. You can edit the template title and description in the language you require. NOTE If you make a change to an existing template and save it to the server, you should increment the template version number so that the new template can be downloaded to all client machines that access the server. See Application Set Management. NOTE To retrieve information on the Server name, User name, AppSet name and Application name for the current report, you can add the appropriate EvFunction. The following tables describe the provided templates. Since administrators can save any report as a template, your application set may contain a different set.

Dynamic reports in Planning application Template name

Description

Subtotals by Accounts

This custom report contains Account dimension with subtotals in rows and any dimension across in columns.

Consolidating

This custom report contains any dimension in rows and Entity type members across in columns with hierarchy.

Variance

This custom report contains any dimension in rows and Categories comparison in columns. It also features Variances calculation (both amount and percentage). With both YTD and Periodic boxes.

Comparative X

This custom report contains any dimension in rows and Categories comparison in columns and also retrieves the Last Year same period data. With both YTD and Periodic boxes.

Comparison with 3– year

This custom report contains Year over Year comparisons.

Drill In Place

This custom report performs drill in place.

Sort on Values or Sort on Members

This custom report contains data sorting by total value.

Nested Row

This custom report allows any two dimensions to be nested in the rows and a third dimension in the column.

Drill Across Dimensions

This custom report allows you to drill from one dimension to another in the rows.

Dynamic schedules in Planning application Template name Description Account-Trend or Entity-Trend

This schedule contains Account type members in Rows and Time periods in columns, using EVDRE based functions.

Entity-Trend

This schedule contains Entity type members in Rows and Time periods in columns, using EVDRE based functions.

Consolidating

This schedule contains Account type members in Rows and Entity type members in Columns, using EVDRE based functions.

Nested Row

This schedule provides the ability to choose the dimensions and members on the Row, Column, and Page. Two Row dimensions can be selected and they will be nested. It uses EVDRE based functions.

Comparative

This schedule contains Account type members in Rows and Category type members in columns with a variance column, using EVDRE based functions.

Dynamic reports in Rate application Template name

Description

Nested Row

This custom report allows any two dimensions to be nested in the rows and a third dimension in the column.

Any by Any

This custom report contains any dimension in the rows and any dimension in the columns.

Dynamic schedules in Rate application Template name

Description

Nested Row

This schedule provides the ability to choose the dimensions and members on the Row, Column, and Page. Two Row dimensions can be selected and they will be nested.

Any by Any

This schedule provides the ability to choose which dimensions are on the Row and Column.

Activities Open a report template From Interface for Excel, select Reporting & Analysis from the Getting Started action pane. Select Build a report using a template, then select the desired template and choose Open.

Save a report locally We recommend that you save all report copies locally, so that you have instant access to the reports you need if you do not have access to the server. From the report you want to save, select Save to My Reports folder. At the Lock down prompt, select Yes to lock down the current view, or select No if you want the report to continue to show live data. If you select Yes, then select a Park N Go option. For more information, see Park N Go. Enter a name for the report, then choose Save. The default folder is the Report folder on your local drive.

Save a report as a template To save a report as a template, from the report you want to save, select eTools > Save Template Library. Enter a name for the report template, then select Save. To make the template available for downloading to the Planning and Consolidation client on user machines, increment the template version number. You

can change the version number in the Admin Console, or from the Set application parameters page in the TemplateVersion field. See for more information.

Edit the template title and description To edit the title and description of the template in the language you require, open the Description.txt file located in the WebFolders directory. In the Description.txt file, the title and description are grouped by language. Between each group is a blank row. The languages must be enclosed in square brackets. After you modify the file, save it with Unicode encoding and close it. If the application cannot find the entry in Description.txt for the language in which the application is installed, it displays the template title and description in the first available language in the Description.txt file. NOTE Only Administrators can modify the Description.txt file.

Retrieve information on current report To retrieve information (Server name, User name, AppSet name, Application name) for the current report, add the appropriate EvFunction. See the EvAPP, EvAST, EvSVR, and EvUSR functions for more information.

Application Set Management An application set consists of one or more applications. An application set stores all the data from each application. Each application contains the master data that controls the data in the application set. Applications can share one or more dimensions with other applications within the set.

Features Adding New Application Sets Administrators create new application sets by copying information from the ApShell application set provided with Planning and Consolidation. See New Application Sets and Application Set Status.

Viewing Application Set Statistics (applicable only to the Microsoft version of the system) You can view the following application set statistics by selecting Show application set statistics in the Manage Application Sets action pane:  Real time, short term, and long term application storage count The storage count comes from the row count of each the table in the Microsoft SQL Server Management Studio.  Cube processing status  Current application optimization status  Dimension processing status

Setting the Application Set Status The application set status determines when and how users can log on and interact with the data in Interface for Office. See Application Set Status.

Setting the Template Version If you have made changes to any of the dynamic templates for reports or schedules, you can force an update of template files by changing the template version. Therefore, clients that log on to the application set receive the new templates. You can set the template version by selecting an application set in the

Admin Console, choosing Set template version from the Manage Application Sets action pane, incrementing the version number by 1, then choosing Update Template Version.

Refreshing Client-side Dimension Files You can use the Refresh client-side dimension files option to ensure the properties of your dimension files are current.

Viewing User Activity (Applicable only to the NetWeaver version of the system) Planning and Consolidation logs user and administrator behavior by recording information about each remote function call made from .NET to ABAP. You can view activity logs from the Manage Application Sets action pane.

Using E-Mail Notifications Administrators can use the e-mail notification service from the Administration Console and the Getting Started page of Interface for the Web. The e-mail notification service allows administrators to send emails to users or teams defined in the system. The appropriate SMTP parameters must be set up correctly. See Application Set Parameters.

Deleting application sets You can delete application sets in the Admin Console by selecting the application set name at the top of the tree, selecting one or more application sets in the Delete application sets action pane, then choosing Delete Selected Application Sets. CAUTION You can delete an application set when it contains data. You cannot, however, delete a dimension member when there is data associated to that member in an application.

Additional Information (applicable only for the NetWeaver platform) The following information is important when managing application sets on the NetWeaver platform of the system:  An application set is equivalent to an InfoArea within NetWeaver with nearly all unique objects within this InfoArea.  No objects are shared across application sets, except delivered properties such as scaling, formula, and so on. An InfoArea is like a folder, where in the case of an application set, it is much more delineated.  You can transport application set changes between development and production environments. For more information, see the operations guide on SAP Service Marketplace at http://service.sap.com/instguidesEPM-BPC.

New Application Sets You add new application sets to the system by copying information from the ApShell application set.

Features You can copy database records, Content Library data, Live Reports, and journals from the source application set. When using the Microsoft version of Planning and Consolidation, you can also copy business process flows. After the copying is complete, you can begin to modify the default applications, add new ones, and assign users to them. When you create a new application set, Planning and Consolidation does the following:  Copies the application set WebFolders/data within File Service

 Creates a copy of all NetWeaver BI objects (in the NetWeaver system only)  Copies all transactional and master data from the source application set to the new application set  Copies security and Planning and Consolidation metadata If the system encounters an error when copying an application set, it rolls back to clear the objects created during the failed copy. You can modify the descriptive text associated with an application set. For example, you might want to change the description if you have made a significant change to the application set, such as adding a new application that performs new functions for your business. You change application set descriptions for a selected application set in the Administration main screen by choosing Application Set Tasks Add a new application set , then entering the new application set description.

Activities You add a application set by choosing Add a new application set in the Manage Application Sets action pane and entering the required data.

More Information ApShell Sample Application Set

ApShell Sample Application Set You use the ApShell application set to build your own application sets. Because it is a shell, it needs to be populated with publications and reports to become a fully functioning application set. ApShell does not contain any data except Time dimension information, and has only a limited amount of master data in the form of dimension members. The dimension master data is limited to default members in most dimensions so the application set works when an administrative task is run. When you install a new version of Planning and Consolidation, ApShell and its components are overwritten. CAUTION You must not modify ApShell.

Features ApShell contains the following components needed to build a functioning application set:  Two sample applications — Planning and Rate — that contain most of the functionality you need to start building your own application set (see Planning Sample Application and Rate Sample Application). If you need to build more complex application sets, for example, for legal reporting, consolidation, or ownership, you can build an application using the components included within ApShell.  A set of dynamic EvDRE-based report and input schedule templates (see Dynamic Report and Input Schedule Templates)  Data Manager packages  Blank Content Library and Live reports  Required administration setup parameters  All required dimensions with the properties needed to create a basic planning and consolidation application set

More Information New Application Sets Application Set Management

Planning Sample Application The Planning application is a multi-currency financial application. The Planning application is designed, by default, to accommodate currency translations. This means that the Rate application, which stores the currency rates, is assigned to it as a supporting application. The combination of the two applications allows for the calculation of currency conversions.

Features The Planning application within ApShell contains the following dimensions:  P_DataSrc: the source of data, such as manual input or uploaded  Category: (category type) version of your data; secured dimension  P_Activity: (user type) enriches the activity types by adding appropriate members  P_ACCT: (account type) used for a list of planning accounts, account members, and member formula  P_CC: (entity type) holds the cost entities in a cost center planning scenario; should reference the input currency dimension  RptCurrency: (rate type) currency used for reporting  Time: (time type) used for storing time periods NOTE Planning is a financial-type application, so a currency-type dimension is required. Default Application Parameters The following are the default parameters and values within the sample Planning application: Web Admin Parameter

Default Value

DIMSFORFACTTBLINDEX

N/A

JNR_ACCDETAIL_DIM

N/A

JRN_BALANCE

N/A

JRN_CLOSING_CODE

N/A

JRN_DESC_MODE

0

JRN_IS_STAT_APP

N/A

JRN_MAXCOUNT

N/A

JRN_OPENING_CODE

N/A

JRN_POST_OVERWRITE

N/A

JRN_REOPEN

N/A

JRN_REVSIGN_CODE

N/A

JRN_VALIDATION_SP

N/A

Web Admin Parameter

Default Value

TOPDOWN

N/A

WORKSTATUSVALIDATE

N/A

YTDINPUT

No

YTDInputTimeHir

H1

Rate Sample Application The Rate application contains currency translation rates.

Features The Rate application within ApShell contains the following dimensions:  R_ACCT: used for a list of planning accounts  R_ENTITY: defines your organizational structure; secured dimension  CATEGORY: version of your data; secured dimension  INPUTCURRENCY: currency used for planning  TIME: used for storing time periods Default Application Parameters The following are the default parameters and values within the sample Rate application: Web Admin Parameter

Default Value

CALCULATION

0

COMMENT

Off

DIMSFORFACTTBLINDEX (for Microsoft version only)

N/A

FXTRANS

0

INTCOBOOKINGS

0

OPENINGBALANCE

0

USELIM

0

WORKSTATUSVALIDATIONS

N/A

YTDINPUT

No

YTDInputTimeHir (for Microsoft version only)

H1

Advanced rules The ApShell Rate application has standard default advanced rules that calculate, divide, or multiply rates for currency translation, and requires the following:

The currency-type property must have a property named MD. The whole value is either M or D, which stands for Multiply currency and Divide currency. In the ApShell Rate application, the currency-type dimension is namedInputCurrency.   

Dynamic Report and Input Schedule Templates

 

Features

 

NOTE If you make a change to an existing template and save it to the server, you should increment the template version number so that the new template can be downloaded to all client machines that access the server. See Application Set Management.

 

NOTE To retrieve information on the Server name, User name, AppSet name and Application name for the current report, you can add the appropriate EvFunction. The following tables describe the provided templates. Since administrators can save any report as a template, your application set may contain a different set.

 

The system provides several templates within the sample application set, ApShell. You can choose the template you want to use, and then customize it to meet your needs. You can save reports locally, or save them as a template on the server (if you have administrator rights). If you modify an existing report template or create a custom report, you can save it as a template for future use. Only administrators with the appropriate rights can save templates on the server. However, you can modify a template and save it locally for your own use. You can edit the template title and description in the language you require.

Dynamic reports in Planning application

Template name

Description

Subtotals by Accounts

This custom report contains Account dimension with subtotals in rows and any dimension across in columns.

Consolidating

This custom report contains any dimension in rows and Entity type members across in columns with hierarchy.

Variance

This custom report contains any dimension in rows and Categories comparison in columns. It also features Variances calculation (both amount and percentage). With both YTD and Periodic boxes.

Comparative X

This custom report contains any dimension in rows and Categories comparison in columns and also retrieves the Last Year same period data. With both YTD and Periodic boxes.

Comparison with 3– year

This custom report contains Year over Year comparisons.

Drill In Place

This custom report performs drill in place.

Sort on Values or Sort on Members

This custom report contains data sorting by total value.

Nested Row

This custom report allows any two dimensions to be nested in the rows and a third dimension in the column.

Template name

Description

Drill Across Dimensions

This custom report allows you to drill from one dimension to another in the rows.

Dynamic schedules in Planning application

Template name Description Account-Trend or Entity-Trend

This schedule contains Account type members in Rows and Time periods in columns, using EVDRE based functions.

Entity-Trend

This schedule contains Entity type members in Rows and Time periods in columns, using EVDRE based functions.

Consolidating

This schedule contains Account type members in Rows and Entity type members in Columns, using EVDRE based functions.

Nested Row

This schedule provides the ability to choose the dimensions and members on the Row, Column, and Page. Two Row dimensions can be selected and they will be nested. It uses EVDRE based functions.

Comparative

This schedule contains Account type members in Rows and Category type members in columns with a variance column, using EVDRE based functions.

Dynamic reports in Rate application

Template name

Description

Nested Row

This custom report allows any two dimensions to be nested in the rows and a third dimension in the column.

Any by Any

This custom report contains any dimension in the rows and any dimension in the columns.

Dynamic schedules in Rate application

Template name

Description

Nested Row

This schedule provides the ability to choose the dimensions and members on the Row, Column, and Page. Two Row dimensions can be selected and they will be nested.

Any by Any

This schedule provides the ability to choose which dimensions are on the Row and Column.

  

Activities

Save a report locally

Open a report template From Interface for Excel, select Reporting & Analysis from the Getting Started action pane. Select Build a report using a template, then select the desired template and choose Open.

 

We recommend that you save all report copies locally, so that you have instant access to the reports you need if you do not have access to the server. From the report you want to save, select Save to My Reports folder. At the Lock down prompt, select Yes to lock down the current view, or select No if you want the report to continue to show live data. If you select Yes, then select a Park N Go option. For more information, see Park N Go. Enter a name for the report, then choose Save. The default folder is the Report folder on your local drive.

 

Save a report as a template

 

Edit the template title and description

   

To save a report as a template, from the report you want to save, select eTools > Save Template Library. Enter a name for the report template, then selectSave. To make the template available for downloading to the Planning and Consolidation client on user machines, increment the template version number. You can change the version number in the Admin Console, or from the Set application parameters page in the TemplateVersion field. See for more information. To edit the title and description of the template in the language you require, open the Description.txt file located in the WebFolders directory. In theDescription.txt file, the title and description are grouped by language. Between each group is a blank row. The languages must be enclosed in square brackets. After you modify the file, save it with Unicode encoding and close it. If the application cannot find the entry in Description.txt for the language in which the application is installed, it displays the template title and description in the first available language in the Description.txt file. NOTE Only Administrators can modify the Description.txt file.

Retrieve information on current report To retrieve information (Server name, User name, AppSet name, Application name) for the current report, add the appropriate EvFunction. See the EvAPP,EvAST, EvSVR, and EvUSR functions for more information.

Park N Go You use this function to lock the current view in a report or input schedule, or both the current view and data, or to take the workbook offline.

Features The data and current view in a report or input schedule is live until you use one of these features from the Park N Go action pane:  You can lock the current view by choosing Set to live data and static current view. Locking the current view allows you to store the file on the server and share it with others who are authorized.

NOTE The term ‘static’ indicates that a refresh does not update the current view or the data. You can lock the current view and data by choosing Set to static data and static current view. You lock the current view and data when you want to protect the data in the report or input schedule from being refreshed. In addition, you can use this function to take a report or input schedule completely offline and bring it back online. You can make changes to the report or input schedule when you are disconnected from the server, then bring it back online later. You can take reports and input schedules offline by choosing Set to offline (no connectivity). When you want to send a report or input schedule to remote users, you can take the workbook offline. Remote users can look at published information and enter data in an offline workbook, but do not have authorization to update or view unpublished data. These users do not have access to

Interface for Excel. After a workbook has been taken offline, you can bring it back to its live state when you want to work with it in Interface for Excel (as long as there are no significant changes to the format). NOTE When you set reports and input schedules to offline, the system changes the Ev functions so that you can open the workbook in native Excel. If you want to set the template to be live and send the data, do not change the format or formulas.

Activities  

To choose a Park N Go option, choose Park N Go from the Report State action pane, then select one of the options described above. To restore a locked report or input schedule, choose Park N Go Set to Live to restore data and live current view.

More Information Working Offline

Working Offline You use this function to work with a report or input schedule in a disconnected state.

Prerequisites Before you can work with a workbook offline, the current view and data must be locked using the Park N Go feature. See Park N Go.

Features When you work on the offline client, you can perform all your Excel tasks while disconnected from the system server, then bring the workbook back to its live state when you reestablish connectivity.

Activities To work on a report or input schedule offline, start Interface for Excel. From the Login page, select the Work Offline button. Open the desired report or input schedule, and modify the workbook as desired. When you are finished, save it.

EvAST The EvAST (Application set) function returns the name of the current application set. SYNTAX 1. EvAST() EXAMPLE EvAST( ) returns the name of the current application set.

EvAST The EvAST (Application set) function returns the name of the current application set. SYNTAX 1. EvAST()

EXAMPLE EvAST( ) returns the name of the current application set. The EvAST (Application set) function returns the name of the current application set. SYNTAX 1. EvAST() EXAMPLE EvAST( ) returns the name of the current application set. The EvAST (Application set) function returns the name of the current application set. SYNTAX 1. EvAST() EXAMPLE EvAST( ) returns the name of the current application set.

EvSVR The EvSVR (Interface on the Web Server) function returns the name of the web server to which the user is connected. SYNTAX EvSVR() EXAMPLE The following example returns Http://CorpServ01 if the current server is CorpServ01. EvSVR()

EvUSR The EvUSR (User ID) function returns the name of the user who is logged on to the system. SYNTAX EvUSR EXAMPLE The following example returns JSmith, if Joe Smith is currently logged on to the system. EvUSR()

EvAPP The EvAPP (Application) function returns the current application name. SYNTAX 1. EvAPP() EXAMPLE EVAPP() returns Financial Consolidation, if the current view application is Financial Consolidation.

Application Set Management An application set consists of one or more applications. An application set stores all the data from each application. Each application contains the master data that controls the data in the application set. Applications can share one or more dimensions with other applications within the set.

Features Adding New Application Sets Administrators create new application sets by copying information from the ApShell application set provided with Planning and Consolidation. See New Application Sets and Application Set Status.

Viewing Application Set Statistics (applicable only to the Microsoft version of the system) You can view the following application set statistics by selecting Show application set statistics in the Manage Application Sets action pane:  Real time, short term, and long term application storage count The storage count comes from the row count of each the table in the Microsoft SQL Server Management Studio.  Cube processing status  Current application optimization status  Dimension processing status

Setting the Application Set Status The application set status determines when and how users can log on and interact with the data in Interface for Office. See Application Set Status.

Setting the Template Version If you have made changes to any of the dynamic templates for reports or schedules, you can force an update of template files by changing the template version. Therefore, clients that log on to the application set receive the new templates. You can set the template version by selecting an application set in the Admin Console, choosing Set template version from the Manage Application Sets action pane, incrementing the version number by 1, then choosing Update Template Version.

Refreshing Client-side Dimension Files You can use the Refresh client-side dimension files option to ensure the properties of your dimension files are current.

Viewing User Activity (Applicable only to the NetWeaver version of the system) Planning and Consolidation logs user and administrator behavior by recording information about each remote function call made from .NET to ABAP. You can view activity logs from the Manage Application Sets action pane.

Using E-Mail Notifications Administrators can use the e-mail notification service from the Administration Console and the Getting Started page of Interface for the Web. The e-mail notification service allows administrators to send emails to users or teams defined in the system. The appropriate SMTP parameters must be set up correctly. See Application Set Parameters.

Deleting application sets You can delete application sets in the Admin Console by selecting the application set name at the top of the tree, selecting one or more application sets in the Delete application sets action pane, then choosing Delete Selected Application Sets. CAUTION You can delete an application set when it contains data. You cannot, however, delete a dimension member when there is data associated to that member in an application.

Additional Information (applicable only for the NetWeaver platform) The following information is important when managing application sets on the NetWeaver platform of the system:  An application set is equivalent to an InfoArea within NetWeaver with nearly all unique objects within this InfoArea.  No objects are shared across application sets, except delivered properties such as scaling, formula, and so on. An InfoArea is like a folder, where in the case of an application set, it is much more delineated.  You can transport application set changes between development and production environments. For more information, see the operations guide on SAP Service Marketplace athttp://service.sap.com/instguidesEPM-BPC.

Application Set Status Application sets have a status of either Available or Not available. They have an available status until an administrator takes it offline, or a system function automatically takes it offline. If an application set is not available (or offline), users may be restricted from performing certain data retrieval and export tasks.

Features If using the Microsoft version of the system: The following conditions determine who and when users can perform certain tasks when the application set is set to Not available: Users have Offline Access task security:  When users attempt to log on to Interface for Office, the following prompt is displayed ―Application set is unavailable. Do you want to continue?‖ A response of Yes allows them to log on. A response of No does not.  If RETRIEVE_ON_OFFLINE is set to 1 and users have the OfflineAccess task security assigned to them, the prompt ―Application set is unavailable. Do you want to continue?‖ is displayed when they try to retrieve data. A response of Yes allows them to see data. A response of No does not allow them to see data. When users do not have Offline Access task security:  When users attempt to log on to Interface for Office, the following prompt is displayed ―The application is not available at this time. Contact your administrator.‖ Users cannot log on.  If the user is logged on when the administrator takes the application set offline and RETRIEVE_ON_OFFLINE is set to 1, the user cannot retrieve data. NOTE If RETRIEVE_ON_OFFLINE is set to 0, users cannot retrieve data while the system is offline, regardless of task security. For information about tasks that open the prompt, see the RETRIEVE_ON_OFFLINE parameter in Application Set Parameters.

If using the NetWeaver version of the system: When you attempt to log on to an application set that is unavailable, you receive a warning and can open the application set to work offline on parked or locked documents. You can also query the application information, but the data may not be completely accurate.

Activities You can manually set the status of an application set by doing one of the following: 

Set to Not Available — Choose Application Set Status .

Set application set status

Not Available

Update

You can enter informative text to display to users who try to perform a restricted task while the application set is unavailable. We recommend including the current date and time so that users can see that the message is current, and to give an estimated time when they can log back on again. 

Set to Available — Choose

Application Set Status 

Set application set status

Available

Update

.

Change the AVAILABLE_FLAG on the Application Set Parameters page to 1 for Available and 2 for Unavailable.

More Information Application Set Management Application Set Parameters

Application Set Parameters Application set parameters allow you to customize your application sets.

Prerequisites You have Appset task security rights to view and change application set parameters.

Features The following table describes the parameters you can set from the Administration Configuration action pane. The parameters that require a value include (R) in the Type column. Parameters that do not require a value include (O) in the Type column. If an application set parameter is required, you can leave it blank to accept the default, but if you delete the parameter, the system may not work correctly. If an application set parameter is optional, you can leave it blank or delete it. Type

Key ID

(O) for the ALLOW_FILE_SIZE Microsoft version (R) for the NetWeaver version

Description The maximum file size the system permits users to upload. A warning message appears if the file size exceeds the parameter value when you upload a file. The default value is 100 MB. This parameter is used in:  Interface for the Web – when posting documents in Content library menu  Interface for Office – sending data, journal posts, Data Manager imports, e-mail notification attachments

Type

Key ID

Description

(O)

APPROVALSTATUSMAIL

Defines whether owners and managers receive an e-mail when there is a change to an assigned work status. Has the following possible values:  Yes: Send e-mail  No: Do not send e-mail In the Microsoft version of Planning and Consolidation, the following SMTP application set parameters must be set up as well to use e-mail notifications in the system: SMTPAUTH, SMTPPASSWORD, SMTPPORT, SMTPSERVER, and SMTPUSER. In the NetWeaver version of the system, the ABAP SMTP service is used for sending e-mails.

(O)

APPROVALSTATUSMSG

Allows you to define a custom e-mail message that is sent to the owners and managers of a work status when a work status code is changed. The message is applicable to all applications in the application set. You can customize the message using the following variables:  %USER% — Name of user who changed this status 

%STA% — Work status

%OWNER% — Entity owner (Microsoft version only)

%TIME% — Time of change

%REGION% – Lock region (NetWeaver version only) — For example: ENTITY: CG1, TIME: 2009.Q1, DATASRC: DC_1, PROJECT: PRJ_1, PRODUCT: PRO_1

EXAMPLE You can create a message such as, This is to inform you that %USER% has updated the work status for %REGION% on %TIME%. The text of the e-mail can be a maximum of 255 characters. There is no need for quotes or brackets around parameters. (R)

AVAILABLEFLAG

Controls whether the system is offline or not. Yes means the system is online and available for sending data to the database. You can take the system offline by changing the value to No. This parameter is used in Interface for Office – Send Data, Journal Posts, DM Imports

Type

Key ID

Description

(R)

AVAILABLEMSG

The message that displays to users who try to access an application that is offline (AVAILABLEFLAG = No). The default value is ―Current Appset status is available‖ EXAMPLE The message could be ―The system is temporarily unavailable due to scheduled maintenance. Try again later.‖ This parameter is used in Interface for Office – Send Data, Journal-post, DM-Import.

(R)

AVAILABLEURL (In NetWeaver version only)

(O)

The URL of the warning page that displays when a user attempts to access an application that is offline.

BPFSTEP_COMPLETE_MSG Determines the content of the message that is displayed when a (In Microsoft version only) Business Process Flow step is completed. The default message is: [%BPF_STEP_NAME%] step of [%BPF_NAME%] has been completed by %USER_NAME%. This parameter is used in Admin console – Manage Business Process Flows.

(O)

CLR_COMMENTS_HISTORY Deletes the comment history except for the last comment entered. Possible values for this parameter are:  0: This is the default value and saves the history of comments.  1: The historical comments are deleted and only the latest comments remain.

(O)

COMMENT_MAX_LENGTH (In Microsoft version only)

Customizes the length of comments. The default value is 256 and the maximum length is 4,000 characters. NOTE If you set a smaller value than current value, the newly entered value does not apply to comment length and the previous value still applies even though the setting is updated successfully in AppSet parameters page. Therefore, the new value should be larger than the previous value.

Type

Key ID

Description

(O)

COMPANY_LOGO

Use this parameter to add your corporate logo to the default templates in the interface for Excel. Enter the file name for the logo image that you want to display. This image must be stored in the Application Set directory, which is “[Server Install]\Data\WebFolders\[Appset]”.

(In Microsoft version only)

EXAMPLE C:\Program Files\SAP BusinessObjects\PC_MS\Data\WebFolders\[Appset] If the COMPANY_LOGO does not exist or the file name is invalid, the default globe image displays. Allows the bmp, gif, and jpg image file types. (O)

DEFAULT_EXTENSIONS

The system allows the default file type even if you do not set this parameter. The default file extensions are hard-coded internally. The file extensions the system allows users to upload by default: .XLS, XLT, .DOC, .DOT, .PPT, .POT, .XML, .MHT, .MHTML, .HTM, .HTML, .XLSX, .XLSM, .XLSB, .ZIP, .PDF, .PPTX, .PPTM, .POTX, .POTM, .DOCX, .DOCM, .DOTX, .DOTM, .CDM, .TDM, .PNG, .GIF, .JPG, .CSS, .MRC. See ALLOWEXTENSIONS above. The user can change the value. This parameter is used in:  Interface for the Web – When posting documents in the Content library menu  Interface for Office – Send Data, Journal-post, DM-Import

(O)

DTSSTATUSCHECK (In Microsoft version only)

Use this parameter to hide or show the Refresh Status Every checkbox in the Data Manager Status View. If the checkbox is enabled and large records exist, the performance when refreshing the status degrades. By hiding the checkbox, the refresh is performed whenever a user calls the refresh button manually. Possible values for this parameter are:  0: Hides the checkbox  1: Show the checkbox

Type

Key ID

Description

(O)

EVDRE_STYLES

Use this parameter to specify a report or input template containing the default style sheet. If no style template is specified in the EVDRE builder in the Formatting section, then the default stylesheet is applied from the template specified in this parameter. This allows users to customize their formatting and save the customization as the default style template. They do not have to manually reapply the template for each new report. Users can overwrite the default formatting for a given report or input template by using existing formatting options. The priority of formatting for a new EVDRE report or input template is:  If a template is supplied in the Allow Formatting section, it is used.  Otherwise, the style template specified in the EVDRE_STYLES parameter is used.  Otherwise, the default EVDRE colors are used. By default, this parameter is empty. Style templates that are used for EVDRE formatting can be stored in any folder in the file service. The full file service path must be specified in the parameter, starting with //root/ of the file service, so that one template can be used globally, regardless of the AppSet. Changes to the style template specified in this parameter do not affect reports and schedules previously created from this template.

(In Microsoft version only)

(O)

EXECUTECUSTOMQUERY (In Microsoft version only)

Set to Y to query the database using predefined SQL statements stored in the tblCustomQuery table.

Type

Key ID

Description

(R)

FILESFOLDERDELIMITER When you create Web-ready files in Excel, the system creates (In Microsoft version only) subfolders based on the native Excel Save as HTML function. Since the naming rule of the subfolder is different for each Microsoft Office language, this parameter allows the system to find the subfolder having the defined delimiter when selecting Web-ready files in Interface for the Web. (Required) The default value is _:.:-. You must define a value for this parameter if the value is empty, or does not contain the required delimiter for your Microsoft Office version. Separate multiple delimiters with a colon; for example, :,:_. EXAMPLE For example, under a folder named ‘Report.htm’, the system creates the following subfolders:  Chinese: report.file  English: report_files  French: report_fichiers  German: report-Dateien  Italian: report-file  Japanese: report.files  Korean: report.files The user can define this delimiter depending on their country. Use ―:‖ to separate multiple values. This parameter is used in Interface for Office – book publishing.

(O)

JREPORTZOOM

This parameter allows you to set the default zoom magnification value on HTML journal reports. We recommend that you set the value to 75%.

(O)

LANDINGPAGEITEM

This parameter allows you to set up a web page as your home page for the Web interface. Once you set this, this page displays for all users of this application set. The page must first exist as a Web page in Interface for the Web. (See Creating a Web Page.) The default value is 1. or you can enter the number that represents the web page you want to make your home page.

(O)

LIMITOFDIFFERENCE

The least value for which the system processes logic. If a data value is smaller than this value, they system does not process the logic. For example, when you set 0.001, smaller data values, such as 0.0009, are not processed by logic. The default value is .00000001, so if the parameter is not set, logic ignores less than 0.00000001. This parameter is used in Script Logic – Processing of script logic.

(In Microsoft version only)

Type

Key ID

Description

(O)

LOGLEVEL

Controls the minimum level of ABAP messages to store in the log in terms of severity, which you view by the transaction SLG1.

(In NetWeaver version only)

(O)

LOGLEVEL (In Microsoft version only)

The default value for this parameter is Info. The possible values for this parameter are:  Donothing  Info  Warning  Error  Fatal This parameter controls the severity level of messages to store in the log file. The default value for this parameter is Info. The possible values for this parameter are:  None  Info  Warning  Error  Fatal

(O)

LOPTZ_AVAILABLE

This parameter takes the system offline during a minimal optimization of an application. The default value for this parameter is: 0. The possible values for this parameter are:  0 – do not change to offline  1 – change to offline This parameter is used in Admin – Lite optimize.

(O)

MAXLRCOLUMNS

The maximum number of columns to display in a live report in Interface for the Web. The value includes header and data columns. The default value is 30. EXAMPLE If you specify a value of 5, one heading column and four data columns are displayed.

(O)

MAXLRROWS

The maximum number of rows to display in a live report in Interface for the Web. The default value is 50. and the value includes both header and data rows. EXAMPLE If you specify a value of 5, one heading row and four data rows are displayed.

Type

Key ID

Description

(O)

MSNIMPASSWORD

The password that the system uses to operate Instant Message alerts in Insight. The default value is blank. This parameter is used in Insight – Alert.

(O)

MSNIMUSER

The user name that the system uses to operate Instant Message alerts in Insight. The default value is blank. This parameter is used in Insight — Alert.

(O)

MULTIBYTE_FORMULA

Use this parameter to support dimension formulas with member IDs that contain double-byte characters, such as those in Japanese, Chinese, Korean, and Russian. The value defines the dimension formula column type as Nvarchar or Varchar. The default value is blank. Possible values are:  1 – Dimension formula column is nvarchar instead of varchar to support double-byte characters.  N/A or blank or 0 – Dimension formula column is varchar.

(In Microsoft version only)

This parameter is used in Admin – Process dimension. (O)

RETRIEVE_ON_OFFLINE (In Microsoft version only)

Use this parameter to restrict data retrieval and export while an application set is unavailable (system offline). The options are:  0: Does not allow retrieving or exporting data while an application set is unavailable regardless of OfflineAccess task security. 

1 (default): Allows retrieving or exporting data while an application set is unavailable (system offline) if the user has OfflineAccess task security.

: The following tasks are exceptions to rule, and can be executed when the value is 0:  Execute logic (script, business rules) from DM packages  Run Export from fact table package 

Run Append into Fact Table package

 Add new comments  Save data through DHE (Dynamic Hierarchy Editor) This parameter is used in all functions that retrieve or export data. For example: Refresh data in Interface for Office and Interface for the Web, Export DM package, Book Publish, and so on.

Type

Key ID

Description

(O)

RUNPKGBYEXE

Allows for the use of an .EXE file type wrapper for running a package out of process from COM+. The options are:  0: Run in process  1 (default): Run out of process

(In Microsoft version only)

(O)

SESSIONTIME

Defines the session time (in minutes) for the Who’s Online feature. The default value is 3000 minutes.

(R)

SMTPAUTH

The authentication method of the SMTP server. This setting does not change the method on the SMTP server, but must match the type of authentication enabled on it. Failure to set this appropriately can result in errors from the e-mail server. The default value is: 1. Possible values for this parameter are:  0 = Anonymous  1 = Basic  2 = NTLM This parameter is used in:  Interface for the Web – When posting documents, Work status, Alert  Interface for Office – Offline distributor

(In Microsoft version only)

(O)

SMTPPASSWORD

The password for the user name defined as the SMTPUSER.

(In Microsoft version only)

This parameter is Optional except when SMTPAUTH is ―1‖ (basic), this parameter is Required The default value is blank. The user can input

Creating a Web Page Procedure 1. Select Add a New Web Page from the Content Library Options action pane. 2. Enter a page name, select a template layout from the list, and select the object type you want to add. 3. Enter a title for the object and the objects pixel height. 4. If the object is a web site, the only setting required is the name of the URL. Otherwise, choose the desired options for your chosen object type. 5. Continue adding more objects to the page, or select one of the options:  Modify the object — You can make additional changes to the page.  Set Team Access to the page — You can determine the teams or users who can access a page. Select the teams and/or users to which to grant access. To view the finished web page, choose Save.

 

Preview the output — Display the page. Save and view the output — Save and display the page.

Result Once the page is open, you can add and view comments, modify the contents of the page, or delete the page.

EvDRE Builder This function allows you to quickly create a report by selecting the dimensions you want in the columns and rows, as well as a few more report details.

Features A default report contains one expansion on the columns using the Time dimension, one expansion on the rows using the Account dimension, and the MemberSet Dep. You can use the following options for modifying the default report: Screen Element

Description

Available dimensions

Lists the dimensions that are available in the active application. You can select one or more dimensions and place them on the In columns and In rows fields using the arrow icons.

In columns

Lists the dimensions that are placed in the column axis of the report. Use the up and down arrows to change the nesting order.

In rows

Lists the dimensions that are placed in the row axis of the report. Use the up and down arrows to change the nesting order.

Spread across worksheets

Lists the dimension that has its members enumerated across the worksheets. See Expansion Across Sheets.

Member selection

Displays the memberset for the dimensions. See Edit Membersets in Drag and Drop Reporting.

Enable Expand Select to generate an Expand range parameter. Range See Expand Range Parameter. Enable Options Select to generate an Options range. Range An Option Range is a range of cells in which you can define your EvDRE options. Alternatively, you can enumerate the options in a comma-delimited list in the Options field. See EvDRE Options for the Microsoft version or EvDRE Options for the SAP NetWeaver version.

Screen Element

Description

Enable Formatting Range

Select to generate a Formatting range. If not selected, you can still define a format range, but you must do it manually. If selected, you can keep the default format (the standard report colors or the style template defined by the administrator in the EvDRE_STYLES application set parameter), or import the style from a predefined workbook (either local or serverbased). See Style Imports and Format Ranges.

Enable Sorting Parameter

Select to generate a Sorting parameter. You can have the system automatically insert a break total into the worksheet, and choose to display the values in descending order. See EvDRE Sorting Range.

More Information Style Imports

Expansion Across Sheets When creating an EvDRE report using the EvDRE Builder, you can choose to expand a member set across sheets.

Features 

 

 

The expansion replicates the sheet defining the expansion in several sheets (one sheet per expanded member), generating a book of reports for the desired set of members. The generated sheets are named after the member being expanded in the sheet. The page key defining the page member of the sheet dimension also contains the hard coded ID of the current member. Each of the resulting tabs contains a replica of the starting EVDRE function, where the chosen dimension’s member specified in the PAGEKEY is the (hard-coded) member ID associated with the current sheet. The starting sheet defining the expansion is the first sheet of the expanded set. The starting sheet also has the chosen dimension’s ID hard-coded in the page key, even if it initially contained a reference to the current view (by the use of EvCVW, for example). The member set defining the sheet expansion cannot be specified as relative to the content of the page key (because it becomes hard-coded). Otherwise, the workbook is not reusable for further expansions on different current views (of the dimension expanded in the sheets). The member set of the sheet expansion can be either self-defined or you can relate it to what is defined in the current view bar by pointing to another cell in the sheet. When you perform an expansion across sheets, the following restrictions apply:  There must be only one EvDRE function in the sheet defining the expansion  You can expand only one dimension across sheets (no nested sheet expansions)  The BeforeRange and the AfterRange parameters for the sheet expansion are ignored  The system ignores the Insert parameter for the sheet expansion.

Activities To expand a memberset across sheets, use the EvDRE Builder to create a report. From the Spread across worksheets field, select the desired dimension.

Example The following is the result of a 3D expansion performed on Entity SalesEurope: App: FINANCE ACCOUNT

NetIncome

Net Income

CATEGORY

ACTUAL

Actual from GL

DATASRC

Input

Input

ENTITY

SALESEUROPE Sales Europe

INTCO

Non_InterCo

Non-Intercompany

MEASURES

PERIODIC

Periodic

RPTCURRENCY EUR

EURO

TIME

2008.Q1

2008.Q1

2008.Q1

2008.JAN

2008.FEB

2008.MAR

2008.Q1

2008.JAN

2008.FEB

2008.MAR

743,805,521.18

249,706,192.55 257,354,085.87 236,745,242.75

PRETAXINCOME Pretax Income

831,842,995.34

277,819,708.17 285,889,304.22 268,133,982.95

TAXES

88,037,474.18

28,113,515.63

NETINCOME

Net income

Taxes

28,535,218.36

31,388,740.20

Drag and Drop Reporting Drag and drop reporting provides an alternative method for creating reports. You use the action pane to design the report structures and contents. The resulting report is based on EvDRE, so after you create the report you can modify it using the drag and drop reporting interface or the EvDRE control panel. NOTE We recommend that you use EvDRE Builder to use more advanced features, such as the following:  Options and Format ranges  Advanced sorting options  Expand by sheet function  Multiple EvDRE tables per sheet By default, drag and drop reporting does not support these features.

Features You can use the following screen elements to design your report: Screen element

Description

Start Designing

Select this screen element to choose the desired dimensions, members, and other options for the columns and rows.

Dimensions list in Design Options action pane

Drag any available dimension name to either the column or row area. The system nests the dimension if one already exists on the column or row.

Include description

Select this element to include the dimension description in the grid.

Edit MemberSets

Choose the dimension for which you want to define members, then select options from the following list:  Add Member Lists To define the members for the dimension, use Member Lookup.  Add Member Selection Rules Choose from the following expansion options:  SELF: only the selected members  ALL: the selected members and dependents  BAS: base level of only selected members  DEP: only dependents of the selected members  BASEMEMBERS: all base members for the dimension  MEMBERS: all dimension members  NOEXPAND: only the selected members with no expansion options  Clear MemberSet Clears the members and starts over.

Set Options

Allows you to apply special formatting options to this data grid. For more information, see one of the following: For the Microsoft version, see EvDRE Options. For the SAP NetWeaver version, see EvDRE Options

Activities To create a report using drag and drop, follow these steps: 1. Select Reporting & Analysis from the Getting Started action pane in Interface for Excel. 2. Select Build a report using drag & drop. 3. Drag the Data Grid object to a cell in the top-left corner of an EvDRE data grid, then use the screen elements listed above to define the report. 4. Click the check box until you return to the Report State action pane, then select Save to My Reports folder. To remove a dimension, choose Edit report using drag and drop – Start designing in the action pane. Then, select the dimension cell and drag it out of the key range.

More Information Editing Reports and Input Schedules

EvDRE EvDRE Builder Member Lookup

EvDRE Options You can define any of the following options in the key range parameter to customize your EvDRE data.

Features You can use the following methods for entering EvDRE options:  You can enter the appropriate keywords in the Options cell of the KeyRange. 

The Options cell (which can also be named OptionRange), may contain an EvRNG function pointing to a range of cells listing valid options. This range is the default, if you select the Allow options box in EvDRE Builder. The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Y value, or with a numeric value. A list of options is below with a description of each; the keywords are NOT case sensitive. Option

Description

ApplyColumnAbove

Applies the colors and formulas in the Before/AfterRange to the column.

AutofitCol

Automatically adjusts the size of the columns containing the EvDRE ranges to fit the content after refreshing data.

Bottom n

Shows only the specified number (n) of the lowest values in the entire data range.

DumpDataCache

The content of the data cache is written in the log file EvDre_log.txt.

ExpandOnly

Disables the refresh action and performs only an expansion, when requested. The system does not retrieve data from the database.

GroupExpansion

When you expand a report using the Groups dimension, which is used to identify consolidation sequences.

HideColKeys and HideRowKeys

Hides the corresponding key ranges.

NoRefresh

Prevents the system from refreshing data from the database

NoSend

Prevents the system from sending data to the database.

PctInput

Enforces a different percentage of input data to trigger SQL queries (default is 20%)

QueryEngine

Manual (or blank for Automatic)

QueryType

NEXJ,TUPLE (or blank for Automatic)

Option

Description

QueryViewName

Use a user-defined view for querying SQL data

ShowComments

Add an Excel comment in any DataRange cell with a formula, if the value retrieved from the database differs from the one displayed by the formula.

ShowNullAsZero

All empty cells in the data range are filled with zeros.

SortCol

Sorts a given column.

SQLOnly

Forces the query engine to only issue SQL queries

SumParent

Inserts new rows with subtotals.

SuppressDataRow andSuppressDataCol Performs a suppression on the defined data range directly in Excel. SuppressNodata

Prevent the suppression of zero values. Only missing (no data) values are suppressed. Otherwise, both zeros and missing data are suppressed.

Top n

Shows only the specified number (n) of highest values in the entire data range.

Activities To define options, follow these steps: 1. Open an EvDRE report or input schedule. 2. In the Options cell of the Key range parameter table, enter the desired options. When you enter more than one, use a comma as the delimiter. For example, you can enter SumParent, AutoFitCol, NoSend. 3. Choose the Expand All button and save the report or input schedule. NOTE The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Yes value, or with a numeric value, where appropriate.

EvDRE Options You can define any of the following options in the key range parameter to customize your EvDRE data.

Features You can use the following methods for entering EvDRE options:  You can enter the appropriate keywords in the Options cell of the KeyRange. 

The Options cell (which can also be named OptionRange), may contain an EvRNG function pointing to a range of cells listing valid options. This range is the default, if you select the Allow options box in EvDRE Builder. The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Y value, or with a numeric value. A list of options is below with a description of each; the keywords are NOT case sensitive.

Option

Description

AutofitCol

Automatically adjusts the size of the columns containing the EvDRE ranges to fit the content after refreshing data.

Bottom

Shows only the specified number (n) of the lowest values in the entire data range.

DumpDataCache

The content of the data cache is written in the log file EvDre_log.txt.

ExpandOnly

Disables the refresh action and performs only an expansion, when requested. The system does not retrieve data from the database.

HideColKeys and HideRowKeys

Hides the corresponding key ranges.

NoRefresh

Prevents the system from refreshing data from the database

NoSend

Prevents the system from sending data to the database.

ShowComments

Add an Excel comment in any DataRange cell with a formula, if the value retrieved from the database differs from the one displayed by the formula.

ShowNullAsZero

All empty cells in the data range are filled with zeros.

SortCol

Sorts a given column.

SumParent

Inserts new rows with subtotals.

SuppressDataRow andSuppressDataCol Performs a suppression on the defined data range directly in Excel. SuppressNodata

Prevent the suppression of zero values. Only missing (no data) values are suppressed. Otherwise, both zeros and missing data are suppressed.

Top

Shows only the specified number (n) of highest values in the entire data range.

Activities To define options, follow these steps: 1. Open an EvDRE report or input schedule. 2. In the Options cell of the Key range parameter table, enter the desired option(s). When you enter more than one, use a comma as the delimiter. For example, you can enter SumParent, AutoFitCol, NoSend. 3. Choose the Expand All button and save the report or input schedule. NOTE

The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Yes value, or with a numeric value, where appropriate.

Editing Reports and Input Schedules Procedure To make changes to an existing report or input schedule, take the following steps: 1. Select Reporting & Analysis (to edit a report) or Data Input (to edit an input schedule) from the Getting Started – Interface for Excel action pane. 2. To edit a report stored on the server, choose Open an existing report (schedule) or for a local report, choose Open an existing report (schedule) from My Reports (Schedules) folder. 3. Select the name of the report or input schedule from the dialog box, then choose Open. 4. Select Edit report using drag & drop from the Report Tasks section of the action pane. 5. If there are multiple EvDRE grids on the report, select Search from the Drag & Drop Options action pane, and double-click the sheet or address for the EvDRE report you want to modify, then choose the Check pushbutton. 6. To modify the report or input schedule, select Start designing in the Drag & Drop Options action pane. See Drag and Drop Reporting.

EvDRE You use the EvDRE (Data Range Exchange) function to generate Planning and Consolidation reports and input schedules.

Features The flexible EvDRE function provides the following features: 

 

You can use EvDRE to create large reports or input schedules with optimal performance. By accepting cell ranges as parameters, EvDRE workbooks are faster to download and upload because there are no send or retrieve functions in the data cells. EvDRE is bidirectional; it can both send and retrieve data. It combines and extends the functionality of other Planning and Consolidation functions such asEvGET and EvSND. EvDRE is faster than these functions because it does not evaluate each cell. You can use EvDRE to build static workbooks (without expansions) as well as dynamic workbooks (with expansions), or workbooks where some dimensions are defined using static members while others dynamically expand. You can define one or more expansions on the rows, columns, or both simultaneously. Workbook options determine how EvDRE sends data. If the workbook option Type is set to Report, you cannot send data; if the option is set to Input Schedule, the template can retrieve and send data. See Workbook Options. For the Microsoft version only, you can create a new style template from an active EvDRE workbook. The following commands are available from the eToolsmenu:  Open EvDRE default Styles. You select this menu option to open the default style template. You can edit it, and then use Save EvDRE Default Stylesto save it.  Save EvDRE default Styles. You select this menu option to save the current formatting as a style template. After defining the template, you can go to Application Set Parameters to define it as the default template for all users creating EvDRE workbooks. After a default template is defined there,

changes saved to that template are automatically applied to newly created EvDRE workbooks (that is, there is no need to reset the application set parameter). Changes to a style template do not affect workbooks previously created from the same template. You can define one default style template for each application set. For more information, see Application Set Parameters.

Activities To create an EvDRE report, use EvDRE Builder or the Drag and Drop Reporting feature.

Workbook Options You use this function to set behavior options in an active report or input schedule.

Features The system determines default workbook option settings depending on whether a report or input schedule is active. You can change any of the following options: Option

Description

Type

Options are Report and Input Schedule. The refresh options provide default selections to optimize the workbook performance. After setting the type, you can further customize the refresh options to suit your needs.

Refresh and expansion

When you refresh a workbook, the system goes to the server and returns data values for the cells whose retrieval formulas are affected. When you expand a workbook, the expansion functions dynamically expand dimensional data. All refresh and expansion options for both reports and input schedules are enabled by default except for Refresh by sheet.  Refresh workbook on worksheet update: The system automatically performs a refresh when a change is made to an affected data cell.  Refresh after data send: The system automatically performs a refresh after data is sent to the database. If not selected, you can perform a manual refresh by selecting eTools Refresh .  Expand on workbook open: The system automatically expands the expansion functions when the workbook opens. If you do not select it, you can perform a manual expansion by selecting eTools Expand All .  Expand on CurrentView change: The system automatically expands the expansion functions when the current view is changed. (SeeDynamic Expansion.) If not selected, you can perform a manual expansion by selecting eTools Expand All .  Refresh by sheet: When you tab from one worksheet to another in an EvDRE report, select this option to refresh each worksheet individually. Unopened worksheets are not refreshed.

Allow users to change options

If you select this, users, who are not administrators, can set workbook options on a workbook. If you leave it blank, only administrators can change these options. By default, this check box is selected.

Option

Description

Drill-down

This option controls member expansion behavior in the active spreadsheet. You can use Expand by Overwriting Rows or Expand by Inserting Rows. When you select Expand by Overwriting Rows, the expanded members display in the rows below the expanded member, clearing existing members. When you select Expand by Inserting Rows, any rows below the expanded member shift down, and new rows are inserted to accommodate the newly displayed members.

Set maximum expansion

For performance purposes, there is a maximum number of rows and columns to return when the row or column in the report or input schedule dynamically expands. For Microsoft Excel, the maximum number of rows is 65,535 and the maximum number of columns is 255. However, for a file format of Excel 12 (Excel 2007) html, xml workbook or template, the maximum number of rows is 1,048,575 and the maximum number of columns is 16,383.

Override current view settings

When you open a Planning and Consolidation report or input schedule in Interface for Office or Interface for the Web, the data changes based on your current view. If you want an active workbook to always open to specific members, you can override one or more of the current view members. To override current view settings, enter some data in two columns (for member names and values) of an empty cell on the workbook. Enter the names and values of the members you want to hard code. Select eTools Workbook Options , then select the two-column range of cells that represents the current view settings, and then choose Add. Choose the Remove button to remove the range. NOTE The Override applies only to non EvDRE templates. It points to the control panel to control non-specified EvGET/EvGTSdefinitions. If you define a row/column, you can override only the dimensions for the page keys because the EvGET refers specifically to the row/column member ID.

Save the session CV with the workbook

This option allows you to save the active current view with the workbook when you have multiple reports open for which you want to see different current views. Rather than use the active session current view for all three reports, you select, then save the desired current view with each workbook. When you switch between the reports, a unique current view is used for each.

Set worksheet password

You can set a Planning and Consolidation password on the workbook. A Planning and Consolidation password is required when the report or input schedule is distributed or collected using the Offline Distribution Wizard. The password is different from native Excel’s workbook password. If you use the Excel password to secure a workbook, Planning and Consolidation expansions may not work properly. To set the password, select eTools Workbook Options from the Excel interface.

Lock status

The Workbook Options dialog shows whether the current workbook is locked or unlocked. You can lock the report or input schedule using thePark N Go feature.

Read options for comment

This field is used for cell-based comments. If an EvCOM function returns a specific comment value, you can have it displayed as text in the cell or in a Microsoft Excel comment dialog box.

Activities To set workbook options, open the workbook for which you want to set the options and choose Set workbook options from the action pane.

More Information Expansion Functions VBA Workbook Options

Dynamic Expansion You use this function to dynamically change the members displayed in a report, based on any number of criteria. Dynamic expansion utilizes Planning and Consolidation functions. The expansion functions are:  EvEXP 

EvNXP

Features Dynamic expansion has the following features:  The system detects when a new dimension member is added to the application. When you use expansion formulas in your reports and input schedules, the new member is automatically displayed when you select a dynamic expansion formula that calls for the member (provided you have updated your dimensions from the server; for more information, see Client Options).  You can set dynamic expansion to automatically expand each time the current view is changed in a report or input schedule.

Activities To set dynamic expansion, open the report or input schedule for which you want to enable automatic dynamic expansion and from the action pane choose Set workbook options Expand on CurrentView change .

Example With dynamic expansion you can do much of your analysis in one report. By just changing the current view of the expansion member you can yield a new set of data. For example, say you have a report with Accounts in the rows. The expansion in this report is based on the AccountType property of the current Account member. The current account is Revenue, which is an income account; therefore, the system returns all accounts with theAccountType INC in the report body. If you change the current account member to Salaries, which is an Expense account, the members displayed in the report body change to expense account.

More Information Expansion Functions

Client Options Client options are available for maintenance purposes.

Features You can perform the following tasks for client maintenance: Reset the Current View Bar

You can reset the current view bar so that for each dimension, the top hierarchical level member displays, which also clears the list of recently accessed members. You reset the current view bar within Interface for Excel by choosing eTools Client Options Clear current view bar . Clear Local Application Information You can clear the files associated with an application from your client. After you perform this procedure, the next time you log on to this Interface for Excel application, you are prompted to complete the Connection Wizard, which allows you to select a server and any authorized application sets. You can clear the files associated with an application from your client by choosing within Interface for Excel eTools Client Options Clear Local Application Information . Refresh Dimensions When you log on to an application set whose structure has changed (there are changes to dimensions and templates stored on the server), the system automatically updates your client information. You can also initiate this procedure manually, which you should do only when instructed by your administrator. You can refresh the dimension information on your client with the dimension and member information saved on the server by choosing within Interface for Excel eTools Refresh dimension members . Refreshing Templates You can update the wizard templates on your client with the templates saved on the server by choosing within Interface for Excel eTools Client Options Refresh Dynamic Templates . Set Local Folder for Clients You can set a user-defined working folder on each client machine by choosing within Interface for Excel eTools Client Options Set local folder for Planning and Consolidation . The default working folder is the My Documents folder of a user. Member Lookup You can set options that control behavior on the Member Lookup dialog box (see Member Lookup).

Member Lookup You use this function to select specific members to use in reports, input schedules, journals, member security definitions, and data management packages. In addition, from Interface for Excel, you can use it to copy and paste member lists into spreadsheets.

Features You can use the following features when using Member Lookup, some of which are available only when performing certain tasks within the system: Feature

Description

Feature

Description

Select members

You can select members at any time to display in your report or input schedule. You can select only the dimensions and members to which you have access. To select a member, do one of the following:  Highlight that member and click OK.  Right-click the member and choose an option:  Select sibling members of the current member  Select all children of the current member  Unselect sibling members of the current member  Unselect all children of the current member  Populate all children of the current member If a section is disabled, all members in the dimension are selected, and all members are returned to the report or input schedule upon clicking OK. You can select the checkbox next to one or more members to copy them to the active spreadsheet.

Feature

Description

Filter members You can filter the member list based on member properties as well as hierarchy. You select a property, an operator, and a value, then click Refresh to apply your changes to the current list. You can specify a filter on one or more properties. If you define a filter, the members can only be displayed in Table data view (not hierarchical). When filtering by properties, the following options are available:  = Equal  Not equal  LIKE — A pattern match that supports using an asterisk ( * ) before or after the value  BT — A range between a low and high value If you set selections with the same property, the logic operator between each selection is OR. If you set selections with different properties, the logic operator between each selection is AND. For example, when you set the following selections: Attr1 = 1 Attr1 bt 3 to 7 Attr2 10 The filter is ((Attr1 = 1 or (Attr1 >=3 and Attr1 =3 and Attr1 Not equal  LIKE — A pattern match that supports using an asterisk ( * ) before or after the value  BT — A range between a low and high value If you set selections with the same property, the logic operator between each selection is OR. If you set selections with different properties, the logic operator between each selection is AND. For example, when you set the following selections: Attr1 = 1 Attr1 bt 3 to 7 Attr2 10 The filter is ((Attr1 = 1 or (Attr1 >=3 and Attr1 =3 and Attr1