1 / 43

Everest Reporting Training Session 2

Everest Reporting Training Session 2. Notice:

ryder
Télécharger la présentation

Everest Reporting Training Session 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Everest Reporting Training Session 2

  2. Notice: Information in this document is subject to change without notice.  This document reflects no commitment on the part of OutlookSoft Corporation.  The software described in this document is provided under license by OutlookSoft Corporation and is subject to the license and all other agreements provided with the software. No part of this document may be copied or reproduced, electronically or manually, or stored in any electronic data retrieval system without the written permission of OutlookSoft Corporation. Questions: Please call OutlookSoft Corporation at 203-964-3100

  3. Agenda • The EvDRE Function • Creating Expansions and Drill Downs • Creating Nested Expansions • Sorting and Ranking Reports • Creating a Data Validation List • Creating Custom Everest Buttons • Publishing Non-Everest Reports • Everest Hot Analysis Reports • Graphical Drill-Downs

  4. The EVDRE Function (Data Range Exchange) • Objective • To optimize the performance of a report or schedule query • A new function to build either a report or schedule • Supports • “Static” reports/schedules • Dynamic reports/schedules • Row Expansion • Col Expansion • N-Level Nested Expansions • Cell based exceptions

  5. The EVDRE Function • Report Assistant building block – EvDRE() • Syntax - EvDRE(AppName, KeyRange, ExpandRange) • Key Range - defines basic report layout • Expand Range - defines dynamic report behavior

  6. The EVDRE Function • Defines Workbook/Worksheet level CV • New EvRNG – allows for easy editing of range instead of typing in the range you can select or edit the existing range with the mouse

  7. EVDRE Sample

  8. APPSHARE PLACEHOLDER

  9. Dynamic Expansions • Dynamic expansions add intelligence to your reports and input schedules. • By simply adding a one line formula to your report, WebExcel will automatically retrieve dimension members, based on criteria specified in the formula, and expand the report to include the new members while maintaining the report structure.

  10. Dynamic Expansions • Additionally, Dynamic Expansions knows when a new dimension member has been added to the application and automatically adds the new member to any report. • By using Dynamic Expansions, you reduce the need for maintenance because they intelligently expand to include additional members with the click of a button.

  11. Dynamic Expansion EvEXP(Appname,Member,KeyRange,DataRange,ExpandDown,IncludeFlag,LevelDown,ParentBefore,Filter,RepeatBlock) • Appname – current application name • Member – member on which expansion is based • KeyRange – specifies an area (boarder) for expansion. Must be a minimum of two rows/columns • DataRange – specifies a range of formulas and formatting to be copied throughout the expansion • ExpandDown – Optional. Defines column or row expansion. True = Row, False = Column. Default is True. • IncludeFlag – Optional. Specifies which members to include in expansion. Default is SELF. • LevelDown – Optional. Used with MDX flags to specify what level in a hierarchy to display. • ParentBefore – Optional. Puts parent before or after children. True = Before, False = After. • Filter – Optional. Can be used to filter members based on member properties. Uses conditional MDX statements • RepeatBlock – Optional. Specifies a block of cells to be copied for each member in the expansion. Similar to DataRange.

  12. Drill Downs • Drill Down lets you drill down into the data within your dimensions. • Drill Down is available whenever a Dynamic Expansion is enabled on a report or input schedule. • You can drill down on any member with at least one dependant. • The spreadsheet will automatically display the immediate dependants of the member drilled upon.

  13. Drill In Place • Drill in place • Drill in place – user can select in the Workbook Options

  14. Nested Expansions • By using another Everest function, EVNXP, in conjunction with EVEXP, you can perform nested expansions. • Nested expansions let you expand one or more dimensions by each other. • You must have a minimum of two expansions in order to perform a nested expansion.

  15. APPSHARE PLACEHOLDER

  16. Sorting and Ranking using EVENE Function: EVENE – stands for enhanced expansion. • The EVENE function is an enhanced version of EVEXP. It allows you more flexibility in building your expansion formulas and supports sorting and ranking functions, while EVEXP does not. • Unlike EVEXP, you must supply your own MDX Set expression defining the dynamic list of members. • Fully qualified dimensions must be referenced, the Current View is not considered in the resulting values. Example: =EVENE("Finance",A31:A60,B31:G60,EVSET("CONREGION", "SELF_AND_BEFORE",99,TRUE,EVPXR("ENTITY","CURRENCY")& "=""USD"))

  17. APPSHARE PLACEHOLDER

  18. QUIZ PLACEHOLDER

  19. Creating a Data Validation List • You can create a list box that contains entries from which a user must choose. Use the Validation command on the Data menu to create the list.

  20. Creating a Data Validation List Procedures: • On the same worksheet, type the list of valid entries across a single column or down a single row. Do not include blank cells in the list. • Select the cells that you want to restrict. • On the Data menu, click Validation, and then click the Settings tab. • In the Allow box, click List. • In the Source box, enter a reference to your list of valid data.

  21. Creating Custom Everest Buttons • You can add custom buttons to your reports and schedules for many of the Everest menu tasks. • Adding a button a worksheet provides one click access to commonly used tasks. • For example, you will notice that many of the reports and schedules provided with Everest contain a button that recalculates/refreshes reports.

  22. Creating Custom Everest Buttons Example Task commands:Description: MNU_eANALYZE_REFRESH Refreshes Reports MNU_eSUBMIT_REFRESHINPUTCELL Refreshes EvSND cells MNU_ePUBLISH_PUBLISHBOOK_NEW Creates a new book MNU_eTOOLS_EXPAND Expands all for dynamic expansions Note: A full list of Task commands is located in the online help.

  23. Creating Custom Everest Buttons Procedures: • Open the workbook to which you want to add a button. • Click on the button icon on the Forms toolbar (View/Toolbars/Forms). • Draw the button, in the location you want to add it, on the worksheet. • The Assign Macro dialog box appears. • In the Macro Name: field enter the command you want to use and click OK. • You can now change the text of the button by right clicking on the button and selecting Edit Text.

  24. APPSHARE PLACEHOLDER

  25. Publishing Non-Everest Reports • You can import books from other systems into Everest. • Using Everest advanced mapping features gives one the ability to control the viewing of these reports with the CurrentView. Everest security will also be applied to these reports. • The files to be imported must be arranged in a package. The package must consist of: • an index file, this contains the instructions for importing the reports. It is read at run time by the Portal. • Files to be imported.

  26. Publishing Non-Everest Reports • The index file and package folder must be placed in the following directory on the Everest server: C:/Everest/Webfolders/[AppSet]/[Application]/REPORTIMPORT • The package folder and the index file must both have the same name.

  27. Publishing Non-Everest Reports Mapping to Everest - • You map the reports to be imported to Everest dimensions based on the report's file name. • Depending on the format of the file name you may have to either rename the files manually or use the TRANS feature. • You rename the file to a name Everest can understand. For example, a report from an external system maps to the Entity and Time dimensions in Everest and you want this report to be viewed by Entity and/ or Time. You will need to rename the file name for the Entity member and time member. • For example, CONREGION,2005.JAN.HTML. With the file name in this format Everest will be able to import the report and link it to the Entity and Time dimension.

  28. Publishing Non-Everest Reports The Index File – • Everest gets its instructions from the index file based on the sections in the file. • The sections must be present in the index in a specific order. • There must be one blank line between sections but not more than one. • The section headings must be in all CAPs and in brackets [].

  29. Publishing Non-Everest Reports Index File Sections – • OPTION - contains user definable options. They are: • Delimiter - specifies the delimiter used in the file name. This field can be blank (no delimiter). • Usetrans - specifies whether to translate member names (i.e. external to internal). Accepts YES or NO value. • Report - the name of the report - any name you want to use. Can not contain any special characters. • Section - the name of the section in the book. Can not contain any special characters. • Book - the name of the book. Can not contain any special characters.

  30. Publishing Non-Everest Reports Index File Sections – • MAP - specifies the name of the dimension(s) that are represented in the file name. Remember that the file name is made up of member names. If there is no delimiter in the file name you can use the Start Point, Length feature. For example the file name for a report is CONREGION2005.JAN. You can specify the mapping for this as follows. [MAP] ENTITY 1,9 TIME 10,8 The first number is the start point of the member name and the second number is the length of the name.

  31. Publishing Non-Everest Reports Index File Sections – • FIXED - any dimension specified here is fixed and can not be changed by changing the CurrentView • TRANS - used to translate member names from an external name to an internal name. You can have more than one TRANS statement. For example: [TRANS:TIME] 20050100=2005.JAN

  32. Publishing Non-Everest Reports Example Index File

  33. Publishing Non-Everest Reports Creating a Package Procedures: • Create a folder named for the package in the C:/Everest/Webfolders/[AppSet]/[Application]/REPORTIMPORT directory. • Copy the reports to be published into this folder • Rename the report files if necessary. • Create an Index file. Note: This file should reside in the REPORTIMPORT folder but outside the package folder.

  34. Publishing Non-Everest Reports Running a Package Procedures: • In the Portal, click on Administration. • Click on Publish Non-Everest Reports. • Click on the package you want to run. • Click on Process.

  35. APPSHARE PLACEHOLDER

  36. Everest Hot Analysis EvHOT(AppName,ReportName,DisplayName,            Member1,Member2,Member3,Member4,Member5) • EvHot is a hot link function enabling powerful click interaction with any populated cell within a template. • It is a powerful authoring tool to create hot spot analysis to guide a user through adhoc analysis. • EvHot sets up a hot link to another report. • Example: EvHot("Finance","Report1","2005 Quarter 1","2005.Q1") • Result: Creates a hot spot labeled "2005 Quarter 1" that the user can click to drill down.

  37. APPSHARE PLACEHOLDER

  38. Graphical Drill Downs • The Everest Drill Down feature is available on Excel charts. • With graphical drill downs, you can take the visual view one step further by making the chart come alive with the simple click of a mouse. • Graphical drill downs require some set up: • The source report must have a hierarchy based dimension in the row and/or column. • There must be an expansion created on the source data worksheet using the EVEXP function. • The report must be set to Type: Report in the Workbook Options. • The chart must be on its own worksheet within the workbook.

  39. Graphical Drill Downs • To drill down on a graph in a report, click on the part of the graph representing the member on which you want to drill down. Click Here!

  40. APPSHARE PLACEHOLDER

  41. QUIZ PLACEHOLDER

  42. EVALUATION PLACEHOLDER

  43. End of Session 2 Thank You! Have a nice day…

More Related