Friday, December 23, 2016

Hyperion Financial Reporting - PSU 704 and Desktop Client News

With Hyperion Financial Reporting PSU, which was released on December 20, 2016, came multiple updates for the web studio:

Financial Reporting Web Studio enhancements to achievemore parity with the Windows Studio functionality:
•   Conditional formatting and suppression 
•   Auto-Calculation
•   Ability to Save Objects
•   Related Content
•   Row/Column templates

Also, Oracle announced in the patch the planned date for parity with the Windows desktop client and its subsequent removal from the suite.

Oracle is committed to providing a simple and intuitive experience to the users of the service. To achieve this goal, Financial Reporting Web Studio, a component available since the release, will be established as the only tool for designing and building reports. As a result, Financial Reporting Studio desktop client support will be fully transitioned to Financial Reporting Web Studio, tentatively in mid-2017. With a planned mid-2017 update, Financial Reporting Studio desktop client is planned to no longer be distributed.
Starting with the release, Financial Reporting Web Studio will achieve functional parity with most of the capabilities provided with Financial Reporting Studio desktop client. You will be able to create new reports and modify existing reports using Financial Reporting Web Studio.
Oracle recommends that you transition to using Financial Reporting Web Studio at the earliest opportunity.

I've been using the web studio for a while now and for the most part it is familiar to those used to the desktop client. The key thing when working with it is to open the report, and then on the left panel click the object (grid, image, text, chart) on which you wish to work. The additions in the 704 PSU address some of the missing items. There are a few more to go.

Tuesday, December 6, 2016

Creating Intercompany Matching Reports via Script

One of the features that has been in HFM since almost day 1 is intercompany matching reports. HFM's predecessor, Hyperion Enterprise (which was originally called Hyperion) had intercompany matching reports. Enterprise's predecessor, IMRS MicroControl, a DOS product developed in the 1980s, had intercompany matching reports.

In the beginning of HFM, intercompany matching reports were developed in script (using KEdit or Notepad or other text editor), uploaded, and then you ran it to see if it worked, anything need changing, etc. Later on the graphical interface came along and nowadays no one really sees the script anymore.

But, it is still there.

Where working with script comes in handy is the initial build of the matching reports. Just built 45 matching reports today using script. Here are the steps.

Build one report through the graphical interface like normal and then extract it from the Documents screen.

Open the file in Notepad and copy the script into Excel.

In this screenshot, the script is in column L. Cells L2 and L3 and the last cell (AccountPlug) have been edited to use cell level formulas to point to I1 and I2. Column I is fed from a list in column E and F with all of the plug accounts that need a report.

Paste the script into Excel and save the file. Then move on to the next item in columns E and F. If you have a huge amount, you could write a macro that cycles through the list and writes out the text files.

Last, go into HFM to the Documents screen and load in the script files. Make sure to set the options at the top to the desired folder, document type, and security class before selecting the files.

Having to create the reports via script has gone the way of the green home page, but the utility of working with the script is still there.


Wednesday, October 5, 2016

Charts in HFM Data Grids

Working with HFM and wanted to do a blog post on charts within data grids. This feature has been out a couple of months and I wanted to show how it works.

First, start with a normal data grid. Over on the display options panel on the right, there is now a chart radio button.

Once the chart is turned on, the chart displays in the window, graphing any data showing in the grid.

The type of chart and the properties can be controlled on the display options panel. Note there are a lot of chart types: some trial and error will be needed to select the appropriate type.

Clicking a bar, line, etc. on the chart will focus on that specific row/column, but drill down is not available by double-clicking the chart like other programs do. For drill downs, click on the legend to the right.

Clicking the legend to do the drill down not only changes what is displayed on the chart but also changes the table display (if showing both the chart and the table).

Overall it looks good and works well. But there is still is no printing - still need to do screen shots!

Monday, June 27, 2016

OECD Country by Country Reporting in HFM and HTP

Lately I've been researching the OECD requirements for BEPS / country by country reporting. The reporting requirement will start in 2017 so there is time to sort out the setup and process. I have spoked with a couple HFM clients about it and there are a couple of different ways to do it.

With Hyperion Tax Provisioning version (released April 2016), there are now three forms/reports for country by country reporting built into the application.

But, there are additional fields (more cell text related) that may be needed. Also, if a company is not using HTP now, they may not want to go through all of the HTP implementation and components, at least not right now. And, a company may not be running on the platform or have a license for HTP. So, for these use cases, a regular HFM application - either integrating into a standard consolidation application or a standalone "CBC" application - can easily handle the reporting needs.

Either way, a process to create an XBRL/XML file can be added to extract the data with the appropriate tags for electronic submission.

So, with Oracle EPM, there are multiple ways of handling the need for OECD country by country reporting.

Thursday, April 28, 2016

HFM translations against the grain

When HFM is set up, the application settings have a section for specifying where balance accounts should be translated with PVA or VAL and same happens for flow (income statement) accounts. Normally this works fine. Typically we do VAL for balance and for flow we do PVA if the exchange rate is a monthly average and VAL if the exchange rate is a year-to-date average.

What happens, though, if some of the data works with these settings and some doesn't?

My current client has this situation. For actuals, they use year-to-date exchange rates. But for budget and forecast, which are coming over from Hyperion Planning, they do monthly translations. If the rate doesn't change through the year, it doesn't matter. But if the rate changes, then there are problems.

Fortunately, there's an easy solution. In the translate subroutine, the function DefaultTranslate allows you to override the application settings with the translation methods and exchange rates for balance and flow. So, I did an if/then to apply this function with the appropriate settings for budget and forecast scenarios and we're good to go.

The code looks like this. Note you can't point to the exchange rate accounts - you have to supply the rate. So I used GetRate for balance and flow and included them in the DefaultTranslate function with variables.

ElseIf pov_scenario = "Forecast" or pov_scenario = "Budget" Then

   AvgRt = .GetRate("A#AvgRate")
   EOMRt = .GetRate("A#EOMRate")
   .DefaultTranslate EOMRt, AvgRt, "True", "False"

Thursday, March 31, 2016

Book on Rounding and Scaling in Financial Reporting

Hi all. Started something today that I've thought about for a while and hope to expand. I put together a book on Apple's iTunes bookstore - should eventually be available worldwide as it goes through Apple's process. The topic for this book is how to round and scale data in a Financial Reporting report and also addresses making everything foot. It's short, a very specific topic, and hopefully it's useful.

The difference between the book and the blog is I can put more detailed content, pictures, etc. in the book than I would feel comfortable putting in the blog. 

I'm also going to look into cross publishing via Amazon for all of the Kindle users out there. Let's see how this works and then I'll move on to the next topic. If there's something else that I should write about, leave a comment.

Thursday, March 3, 2016

HFM PSU is available

PSU 200 is out for HFM - four new features!

Rules Profiling

You can use rules profiling to capture in-depth information about rules execution at the major subroutine level. Rules profiles include statistics about time spent calculating, translating, and consolidating each entity during a consolidation task.

Duplicating Applications

You can duplicate of the contents of an application to create a new application. You can duplicate an application in the same environment and rename it, or register the new application in a different project and cluster.

Importing Applications

You can import an application across databases or across EPM systems. Both EPM systems should use Oracle DB or SQL Server.

Viewing Data Grids, Forms, and Process Control as Charts

This release includes the ability to view data grids, data forms, and Process Control information as charts. You can customize the chart display, and also print charts.

And lots of bug fixes!

Fixed bugs:

Defect Number
  1. Xfmdatasouce.exe process crashes after network connection to database reset
In Grids accessing accounts that the user does not have security access to causes instability.
22650693Cannot open WDEFs when POV is empty for dimensions in rows or columns.
22526377Users without admin rights are able to view running tasks executed by other users.
22512862Informational messages logged from the execution of HS.IsValidDest where an invalid member has been passed to the function do not specify which member in the dimension was invalid. 
22497823Assign access control table displays incorrect rights using users/groups filter.
22469303Launching related content from a tasklist does not use selected active POV.
22345011On Solaris applications intermittently fail to open.
22331984Calculation Manager rules are not upgraded correctly.
22305302Long running tasks in Financial Reporting or Smartview cause xfmdatasource.exe to become unstable.
22289439Unable to open application in OEL6.
22287103Error when creating a new journal detail line with no account selected.
22286684Consolidation aborting due to invalid record.
22272652Opening a Japanese named custom document displays underscores in the filename.
22269359Unable to access task audit and data audit in imported application.
22255935In Smartview adhoc queries do not return all ICPs.
22225041Loading data after moving application using LCM causes xfmdatasource.exe to become unstable.
22198791In grids account member properties that contain '<' and '>' are not diplayed properly.
22176031In Smartview function builder using HSCurrency with a top parent results in 'Invalid Currency Combination' error.
22112722Large cube size error message contains meaningless label names.
22097353Copy application fails when creating a new application.
22090169It is not possible to run more than 8 concurrent intercompany reports.
22075930There is no documentation for sessionmanagertimeoutinmssetting.
  1. xfmdatasource.exe 
21974387In grids multi-cell select does not consolidate correct periods.
21958870Cannot cut and paste from the Assign Access Control screen into Excel.
21956659No info when data load has errors like cell for period is locked.
21946994In WDEFs security class access does not work as expected.
21917540For OBP applications active/inactive status updates do not synchronize across servers.
21894737The values for Entity and Custom4 are not correct in journal report.
21888335Documentation for SCALC function needs to be updated.
21882308Unable to manually input metadata members in intercompany module and save transaction.
21881275WDEFs with override on a column cannot be used.
21875189Intercompany reports show empty lines when suppress details is enabled.
21861089When errors occur during consolidation xfmdatasource.exe may hang.
21856687Monitor intercompany cannot be used.
21856670Unable to deploy Calculation Manager rules.
21852281Smartview journals does not work with entities prefixed with zero.
21819351Long running Process Management tasks stop when xfmdatasource shuts down due to inactivity timeout.
21812326Data not synchronized across servers under certain circumstances.
21804549In Process Control validation incorrectly passes when validation account is not zero.
21799371Cannot open application that was created using duplicate application.
21797377Unable to lock period due to erroneous error.
21791057In Smartview if any HsSet function in a sheet fails all of the HsSet functions in that sheet will fail.
21789553Cannot clear ownership using clear data.
21771814The [Default] security class is not selected in new journal page.
21760205Added a warning message when the "Clear All" option is checked when loading security.
Unable to submit cell text in WDEF from smartview.
21696503Conversion rates do not get updated when loading intercompany transactions.
21695535Autojournals report aborts.
21627558It is not possible to duplicate applications across databases.
21618967In WDEFs it is not possible to save changes to journals accessed from entity details.
21568335Consolidations cause xfmdatasource.exe to close.
21566981Poor performance opening some data grids.
21523944Users cannot open an application while a Smartview retrieve is in progress.

On Solaris xfmdatasource exits during heavy multi-user load.

21508716Journals not migrated properly in LCM application snapshot.
21491896POV of related content has different behavior from earlier versions.
21480488Application fails to open after deploying rules from calculation manager.
21479785Relative URLs not working.
21394654Task flows do not run in a multi-server (distributed) environment.
21373263Cannot create a journal report with more than 1000 journals.
21359703On Solaris journal reports fail to run with malformedbytesequenceexception.
21348910On Solaris application fails to open after auto shutdown.
21342557On Solaris opening large grid results in stuck threads.
21341902On Solaris the xfmdatasource process locks up during consolidation and is killed off by the health monitor.
21314907Need to remove the reference to the HFM Rules.xml file from the admin guide.
21313223The web services reports a hanging thread when posting/unposting multi-entity-journals.
21311538Partner amounts are not matching between the Intercompany and 'Match By Account' reports.
21297045Time is shown in UTC on intercompany matching reports.
21263955Security reports in shared services are all center aligned.
21255493Application is created but error occurred while enabling features.
21248973Line item detail descriptions that have an apostrophe do not extract correctly.
21215927During application creation ‘epmhfm-65554: the system was unable to retrieve the app session’ error is generated.
21206631In WDEFs dynamic accounts do not show if suppressnodatarows is true.
21193371Suppress rows with no data with add member functionality, suppressing more than necessary.
21170645On Linux extracted metadata app file does not retain extended ascii characters.
21162581Metadata load does not display an error if security has not been loaded.
21154474In Smartview numeric members are turning into numbers.
21150325On Solaris open application fails intermittently.
21148421EA extract to a database does not include member descriptions.
21128657Intercompany matching report is erroneously listed as a filter in task audit.
21122794Cannot save journal in an application copied using LCM.
21104385Entity details are displayed in the wrong columns in data grids.
21090570Communication error occurs removing cell text label via replace mode in app load.
21086976Smart view does not maintain parent.child for entity.
21075645Reports containing suppression options are not working properly.
21074340Opening a WDEF that contains nodata rows or columns results in 'Invalid Argument' error.
21024595The Manage Journals task in a task list should open within smartview.
20980001Security group with ampersand causes an error when printing security report.
20973690Unable to save security class changes from assign access control with parentheses in the name or description.
20971813Certain incorrect syntax for hs.clear in rules is not recognized as an error.
20930526Error in system messages saving a new security class in assign access control.
20852774In data load replace by security only works for data, not for cell text or line item details.
20826812Performance issue during open application when LDAP users are assigned to large numbers of groups.
20787473Cannot reorder columns in smartview.
20684065In WDEFs force calculate contribution does not work when Entity is not in Parent.Child format.
20647876EPMA duplicate as deployed does not provision users properly in the new application.
20613267Cannot override the MaxNumCubesInRAM at the application or server level.
20585801In Process Control the Validation tab is only visible if Phase 1 is also selected.
20387924Running an FR report with no members on the rows or columns may cause instability.
In smartview an error occurs and you cannot refresh if sheet members are moved.
20086146Intercompany reports do not perform well when consolidation is running.
20017050Incorrect document name when opening a custom document.
19569673When a dot is missing between dimensions in a WDEF script opening the WDEF causes the system to become unstable.
19446759In Manage Taskflows it is not possible to select POV parameters using IE11.
19375860System logs show numerous 'Unable to open file' errors.
19327569In WDEFs users are able to select members that are out of constraint when the hierarchy contains shared members.
18753552Errors occur when opening specific journals.
18537370Poor performance in process control when email alerts are enabled.
18228788Clear invalid records does not produce a log when running for a long time period.
17170275Process control 'Show Active Only' option affects WDEF entity change.
6569808In WDEFs it is not possible to have multiple member lists on columns.

Tuesday, January 26, 2016

Metadata changes - what changes?

Just saw a post today on the Oracle OTN forum regarding metadata changes. I gave a short answer there and I'll give a more detailed one here.

The question is: what happens to data when metadata changes (adds, moves, deletes) are made.

Answer: it depends on the circumstances.

First, adds. Adding new metadata will not affect the data. I say "will not" but keep in mind I can write a rule that says if the account starts with 123 then make it equal to 1 and if I add account 12345 then the rule will run, create a 1, and now my data is changed. I'm ignoring weird stuff like that for this post.

Next, deletes. Deleting metadata will cause data changes (pretty obvious but just including it for completeness). If the data wasn't cleared first, the records will be orphaned and can be cleared by running Delete Invalid Records from the Manage Data screen. 

Next, renaming or changing the label of a metadata item. If the application is Classic, then this is treated as a delete and add, so data will be lost. If this is needed then extract the data first, make the change, and then reload (I'd maybe go a step further and do the add with the new label, reload the data, verify, and then delete the old label). If the application is EPMA, then the member is actually renamed and data is not lost. This is the one feature of EPMA that I like.

Last, moves. Here, there are further conditions...

Account and custom dimensions - parent account and custom members are calculated in memory whenever retrieved, so moving a member will automatically be reflected in the data. This occurs whether the entity is locked, promoted/published, etc. and the behavior cannot be modified.

Entity dimension - if parent entities are locked (I've never recommended doing this but I'm sure someone is out there doing it), then changes to the entity hierarchy will not be reflected. If org by period is in use, then moving an entity must also include updating the [active] system account for when the entity should roll up to the parent. If not using either of these features, then the move will impact the parent entities but only after data is reconsolidated. 

Note: we generally recommend reconsolidating all scenarios/years/periods/entities after making metadata changes. This ensures the data is consistent with the metadata and rules and users don't have the worry about seeing "OK SC" as the consolidation status.