Wednesday, August 25, 2010

Hyperion Reports - Axis Labels on Charts

I found several other limitations with axis labels when working with Hyperion Reports and charts.  Fortunately, there are work arounds, some better than others.

One issue I found when using charts in Hyperion reports is that if you have more than one dimension on a column or row, both are used in the axis labels on the chart.  There is a simple solution for this.

For example, in a rolling monthly report there will likely be both Fiscal Year and Accounting Period on the columns, but for the chart only months should be displayed. 

To accommodate this, the solution is to put a custom heading that is blank on each of the Fiscal Year cells within the grid.  This will exclude the heading on the axis label in the grid.  If both the grid and graph are to be shown and the year still needs to appear within the grid, a custom heading can be used on the Accounting Period.  Using text functions in the custom heading, the Fiscal Year and Accounting Period can be referenced in the same heading accomplishing the intended results.

Another issue is that in some instances the last Y axis label is being cut off.  To work around this, add a secondary axis.  This defaults to 0 at the bottom and 100 at the top, with no other steps in between.  Formatting the font on the secondary access to be white or whatever the report background color is hides the secondary axis, but gives the last Y axis label a little more room.

Finally, the last issue I had is with the chart axis label being in whole numbers and not decimals.  From all of my research I found that the axis label should follow the formatting of the grid, but it is a known issue that it does not.  There were several posts on the forum about a non-Oracle approved solution and to use at your own risk.  Since this one wasn't my solution, nor is it Oracle supported, I will simply post the link to the forum post and say it did work.  I do however find it amazing the only work around is to adjust the XML.

Hyperion Reports - Combo Chart - Part2

There is one update to my prior post on Combo Charts in Hyperion Reports.  For the work around to fuction, the Suppress Repeating Labels must be set to either Inherit Settings from Grid or Off on the Legend tab of the Format Chart window.  If the Suppress Repeating Lables is set to On, then the phantom row still appears in the legend.

Friday, August 20, 2010

Hyperion Reports - Combo Chart

When working in Hyperion Financial Reporting Studio with charts there is an option for a Combo Chart, which is a Bar-Line Combination Chart. The functionality isn’t as robust as Excel, which is to be expected. I did however find a quirky item or two that had to be worked around to get the graph to render correctly.

The chart is built off a grid within the report for this example and the graph should represent Sales on the bars and Marketing, Payroll, and Misc on the lines. After building the grid, insert a chart and select the Combo Chart Type for your grid. Select which Row(s) and Column(s) your bar data should represent and which Row(s) and Column(s) your lines should represent in the Chart Properties box. The set up looks like this:
The resulting report appears as follows, with the Sales bar and Marketing line being the same color. When trying to adjust the color of one, both were adjusted. This is due to the fact that both are considered DataSet1. Sales is DataSet1 for the bar and Marketing is DataSet1 for the lines.

The next attempt was to try to select both Rows for the line data range.  In the resulting report, the graph now appears as expected, but the legend shows Sales as both a bar and a line (both of which are the same color). There is no sales line on the report, but the legend is confusing.
After some further tinkering I found no use in adding a text row between Sales and Marketing, but did find success with adding a formula row between the Sales and Marketing rows in the grid. The formula row was set to Hide Always so it will not appear in the grid and was selected as one of the rows in the Line Data Range. The new setup looks like this:

Finally, the report and chart appear as expected with different colors for each bar and line and without duplication in the chart legend.

I doubt this was the intended functionality of this feature, but at least there is a work around that gets to the correct end result.