Tag Archive for: reporting

Reports out of Hyperion Planning are typically identified in 2 categories.

  • Standard “canned” reports – These reports are used generically in a global aspect to report data in common formats and standardized views. These are often generated in volume and printed for presentations and executive review.
  • Ad hoc reports – These reports are more flexible, often adjusted to explain current variances and market conditions. These reports are most likely generated by analysts and managers producing unique views to explain variances that exist at a point in time.  The need to alter, change, and customize these reports are essential to identify and explaining current business conditions.

Read more

 

This post looks at the “Allow Expansion” functionality in Financial Reporting Studio. “Allow Expansion” is a great feature, as it gives the user more flexibility when running and displaying reports. However, it can also require more setup work as it makes the report more complex. Two situations that I encountered recently:

  1. Adding a custom heading using conditional formatting
  2. Calculating formulas on expanded members Read more
 

This post looks at the “Allow Expansion” functionality in Financial Reporting Studio. “Allow Expansion” is a great feature, as it gives the user more flexibility when running and displaying reports. However, it can also require more setup work as it makes the report more complex. Two situations that I encountered recently:

  1. Adding a custom heading using conditional formatting
  2. Calculating formulas on expanded members Read more
 

KScope12 is the premier conference for Hyperion techies and up-and-comers. Whether you are looking to brush up on your skills, learn new skills, or see how others overcome challenges, you will want to participate in this event. If your organization values your development and has a budget for these growth opportunities, mark your calendar for June 24th through the 28th. Registration is open, and as more information is available, it will be published at In2Hyperion.

Presenting a topic is a great way to reduce the cost of the trip. It’s a great way to get your name out in the community as well. If you have something that you think would be valuable for other experts to hear about, submit an abstract.

We hope to see you there. Visit www.kscope12.com for all travel details and abstract submissions.

 

There is, what appears to be, a bug in Hyperion Planning that causes business rules that take longer than 5 minutes to re-launch.  The following, published by Oracle, explains the root issue of this problem.  It is not a bug, but a setting in the host web server that causes the request post multiple times.  This explaination from Oracle clearly states that this is ONLY an issue when accessing Hyperion Planning through Hyperion Workspace.  I have seen the same response while accessing Hyperion Planning directly.  Regardless of your entry point, it is a good proctice account for either entry method and should be applied.

This applies to Hyperion Planning, Version: 9.3.1.0.00 to 11.1.1.3.00 and is applicable to all operating systems.

Symptoms

When accessing Planning, Business Rules that normally take more than 5 minutes to complete
run for an unlimited period of time.  By viewing the running Essbase sessions in the EAS console, you can see that the Business Rules “Calculate” sessions are being re-launched every 5 minutes, so that a new instance of the Rule is launched before the first can complete.

This issue only affects Business Rules that normally take more than 5 minutes to complete.

This issue does not affect Business Rules launched directly from Planning (accessing Planning directly on its own URL, bypassing the Workspace).  This issue does not affect Business Rules launched from the EAS console.  This issue only affects systems using Weblogic as a web application server.

Cause

This issue is caused by a default timeout setting of 5 minutes (300 seconds) in the Weblogic HTTP Server Plugin.  This plugin is a set of configuration files in which Weblogic defines how it will interact with the HTTP Server through which Workspace is accessed.  More information on Weblogic Plugins is available here:  http://download.oracle.com/docs/cd/E13222_01/wls/docs92/pdf/plugins.pdf

Solution

Hyperion System 9 and Oracle EPM 11.1.1.x support the use of either Microsoft Internet Information Services (IIS) or Apache as an HTTP server. The steps to increase the timeout depend on which you are using.  The new timeout value should be set to a value larger than the time the longest-running Business Rule takes to execute. The examples below use a setting of 30 minutes (1800 seconds).

Apache HTTP Server

Step 1

Edit %HYPERION_HOME%\common\httpServers\Apache\2.0.52\conf\HYSL-WebLogic.conf

Step 2

Add (or edit, if already present) the following parameters to the two sections for Planning, and also to the two sections for Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.Each section begins with an XML tag.

WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800
  <LocationMatch /HyperionPlanning>
<LocationMatch /HyperionPlanning/*>

Add the new “WLIOTimeoutSecs 1800” and “HungServerRecoverSecs 1800” properties as new lines within the tags.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs 1800” in addition to the “WLIOTimeoutSecs 1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).

PathTrim /
KeepAliveEnabled ON
KeepAliveSecs 20
WLIOTimeoutSecs 1800
HungServerRecoverSecs 1800

Internet Information Services (IIS)

Step 1

There are several copies of the iisproxy.ini file. Oracle recommends you modify the files for Planning, Financial Reporting and Workspace, as the 5 minute timeout issue can cause problems in all three products.

Paths (note that “hr” below stands for Financial Reporting):

%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\hr
%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\HyperionPlanning
%HYPERION_HOME%\deployments\WebLogic9\VirtualHost\workspace

Step 2

For each copy of iisproxy.ini, add the following lines at the end of each file.  If you are using a version of Weblogic prior to 9.x you need to add the second line “HungServerRecoverSecs=1800” in addition to the “WLIOTimeoutSecs=1800” parameter. This second parameter is not necessary for Weblogic 9.x and later (though it will do no harm).

WLIOTimeoutSecs=1800
HungServerRecoverSecs=1800

Step 3

Restart IIS from the IIS Manager and restart the Workspace web application service

Oracle HTTP Server is used

Step 1

Modify the file mod_wl_ohs.conf file under the directory, $EPM_ORACLE_INSTANCE\httpConfig\ohs\config\OHS\ohs_component with the following content:

<LocationMatch ^/HyperionPlanning/>
SetHandler weblogic-handler
WeblogicCluster PlaningServer:8300
WLIOTimeoutSecs -1
WLSocketTimeoutSecs 600
</LocationMatch>

Step 2

Restart the Oracle HTTP server and the Workspace web application services after the modifications are complete.

 

The ability to import font types into Hyperion Financial Reporting is a common request by many companies, typically a request resulting from corporate reporting standards. Not only is this possible, it’s a quick and easy exercise that is detailed out below.

Step 1: Locate the Font Folder.

The font type files (normally identified by a .TTF or .ttf extension) can be found in the “Fonts” folder located in your Windows directory (Likely on your ‘C’ drive). The key here is locating this folder on the server where Hyperion Financial Reporting has been installed.

Step 2: Copying& Pasting the New Font File.

This is as easy as it sounds… just copy and paste the new file into this directory.

Step 3: Creating a Report.

Opening Hyperion Financial Reporting and create/modify a report. When selecting the font type, notice the new Font Type that was just added in Step 2 above. Note that it’s best to close the Financial Reporting client before importing the new Font file onto the server; this will insure that the client will recognize the new file.

 

Reporting solutions often require companies to filter out a top range of Key Performance Indicators; for example, the top 10 expenses related to marketing. Hyperion Financial Reporting makes this type of reporting easy for developers by providing the “Top” properties checkbox. The difficulty arises when a company requires a solution to display the bottom 10 – those 10 expenses that account for a majority of marketing related expense. Hyperion Financial Reporting has nothing built to provide this type of information.

As you might expect – knowing your smaller expenses is important but knowing the largest; those where you can improve margin, is vital. A solution to display the bottom 10 is detailed below; this solution displays the 10 largest negative values vs. displaying the 10 largest positive values.

The high-level solution includes the following functionality:
a.    Inserting a “Rank” column.
b.    Sorting on the “Rank” column.
c.    Adding conditional suppression for bottom 10.

Step 1:

Create a report grid with a formula column as the first column (Column A below).

 

Step 2:

Insert the “Rank” function on the Formula Column. Be sure to choose the “Ascending” property. Adding “Rank” will order the rows from High-to-Low based on the data returned. The example below provides ranking off of Column ‘A’. The ranking is used on Step 4 when adding conditional suppression.

Step 3:

Apply row “Sort” to the grid. You find the “Sort” property by placing focus on the entire grid (left clicking the upper left-most cell). Choose to apply sorting to the “Rows”, Sort by “Column A”, and sort in “Ascending” order. Sorting will determine the order in which the data is displayed, Ascending or Descending. The Sorting is used on Step 4 when adding conditional suppression.

Step 4:

Add Conditional Suppression to the row(s). This logic will determine which data rows are ultimately displayed to the user. To add conditional suppression, highlight the row and click “Advanced Options”. Because the requirement is to show the bottom 10, suppression should hide any row with a “Rank” value greater than 10 (You will also want to suppress rows where “No Data” is returned).

When this report is run, only the bottom 10 will be displayed to the user… those marketing expenses with the largest negative values. The solution above will essentially do what a “Bottom” checkbox would have provided had Hyperion programmed this functionality into the application.

 

 

 

Step 4: Adding ‘Advanced Suppression’ to each of the Year & Period columns.

Step 4 in the development of this report contains a majority of the logic to be setup which will allow a range of periods to be displayed to users. The idea behind the logic in this section is to move the range of periods displayed to users based on the Period selected in the User POV. The “Range Matrix” below will shed some light on what should be displayed based on what is selected.

Just as Conditional Suppression was setup for the trigger columns, Conditional Suppression will need setup for these Year/Period columns. The difference between the “Trigger” section and the “Year/Period” section resides on how columns are chosen to be suppressed. As the name suggests, the “Trigger” section added in steps 1 & 2 will drive the conditional logic, and thus the range of Periods displayed to users. The examples below display a high-level subset of the column logic.

Example 1:

  • User selects “Jan” as the Period.
    • Which Periods will be displayed?
    • Sep (Prior Year)
    • Oct (Prior Year)
    • Nov (Prior Year)
    • Dec (Prior Year)
    • Jan (Current Year)
    • Which Periods will be hidden (suppressed)?
    • Feb-Dec (Current Year)

 

Example 2:

  • User selects “Sep” as the Period.
    • Which Periods will be displayed?
    • May (Current Year)
    • Jun (Current Year)
    • Jul (Current Year)
    • Aug (Current Year)
    • Sep (Current Year)
    • Which Periods will be hidden (suppressed)?
    • Sep-Dec (Prior Year)
    • Jan-Apr (Current Year)
    • Oct-Dec (Current Year)

 

When adding columns to a report, each column will be tagged with an alphanumeric value that identifies the column number. Staying true to the rolling 5-month solution, columns “A” through “L” of your report identify the “Trigger” section (Jan equals “A”, Feb equals “B”… Dec equals “L”). The “Year & Period” section is identified by columns “M” through “AB” of your report (Sep of Prior Year equals “M”, Oct of prior year equals “N”… Dec of current year equals “AB”). When setting up the “Year & Period” Conditional Suppression, it is imperative that you know and understand which Periods correlate to which column numbers.

“Trigger” Section:

“Year & Period” Section:

The Conditional Suppression will need added to all “Year & Period” section columns (columns “M” through “AB” in the above images). Column “M” (which correlates to “Sep” of prior year) will need displayed to the user ONLY when the user selects “Jan” for the current POV of the Period dimension. By selecting “Jan”, the user is requesting to see data for Sep-Dec of the Prior Year, and Jan of the current year (as shown above in the “Range Matrix”). A subset of the Hyperion Reporting logic is shown in the image below. Similar logic is required for the remaining columns of the “Year & Period” section (columns “N” through “AB”) with the only difference being the suppressed “Trigger” columns selected.

Hyperion Reporting – Conditional Suppression Logic:

 

Year & Period Suppression Logic:

 

As stated before, the “Trigger” section of the report drives what is ultimately displayed to the user, and this is based on what the user selects in the User POV for Period. If a report requirement exists for something other than a 5-month rolling view, the number of “Year & Period” section columns would need adjusted, as would the Conditional Suppression logic, but the “Trigger” section will not need adjusted. The overall idea of how to implement this solution remains intact. Please feel free to contact me directly with any questions on implementing a solution such as this, I’m happy to assist when possible.

 

 

Step 2: Adding ‘Advanced Suppression’ to each of the 12 Trigger columns.

The Conditional Suppression set on each of these columns (see Step 1) will suppress the column that correlates to the Period selected. If the end-user selects Jan, then the column representing Jan will be suppressed. This is used later in step 4 of the report development.

Keys:

  • The Advanced (Conditional) Suppression for each column relates to the 12 Periods added in Step 1.
  • The logic for Jan is as follows:
    • Suppress Column If:
      • “Member Name” “Period” “equals” “Jan”.
      • “Jan” is the actual member name.
  • The same logic in place for Jan will be required for the Feb-Dec columns, Thus…
    • Suppress Column If:
      • “Member Name” “Period” “equals” “Feb”.
      • “Feb” is the actual member name.
      • Etc…
  • Once steps 1 & 2 are complete, development of the trigger section has been finished.

 

 

If you have worked with Hyperion Financial Reporting, odds are good that you’ve come across report requirements for Rolling Years and Periods, that is to say a report that spans periods from multiple years. This is an easy build if your data source includes a single “rolling” dimension for ‘Year’ and ‘Period’ – you utilize the “RelativeMember” function to move up or down the hierarchy. The difficulty arises when programming this same logic into a report with separate ‘Year’ and ‘Period’ dimensions. A solution utilizing “Trigger” columns is shown below.

The use of what I call trigger columns allows for Hyperion Financial Reporting to display rolling Years and Periods, whether your requirement is for a 2 or 12 month rolling report. The Trigger section of the report requires both the ‘Year’ and ‘Period’ dimension to be columns on the report, while any dimension; such as Account or Product, can be included on the rows. The steps below detail a rolling 5-month solution.

Step 1:  Add data columns for all 12 periods (Jan-Dec).

These columns will function as the Trigger section, essentially telling the other columns what the end-user selected for Period. This is important because later sections of the report require knowledge of the selected Period to ultimately decide what is displayed.

Keys:

  • These 12 columns MUST be Data columns.
  • These 12 columns MUST be hidden.
  • The Period member selection MUST be set to “Current Point of View for Period”.
  • The Year member can be any member – not relevant to the trigger.

  • Optional: Overwrite the Period POV cell with the 12 periods (Jan-Dec) as shown below.