I was going through a question posted in LinkedIn Oracle Hyperion Financial Reports API?
The person who raised the question was looking for a way to bypass the not so user friendly Financial Reporting Studio.
I always wish that FR Studio had
- Undo
- Copy (works many times, but buggy)
- Cut and Insert
So I thought of using the very friendly EXCEL to create FR reports (part of this research was to answer the query of creating a report with more than 400 rows).
It is a tedious process to create report in FR if you have lot of rows. So the tool helps you use all EXCEL functionality of Cut, Copy, Paste and with the click of a button it’ll generate the required XML of the FR report.
Prerequisites
- Create a dummy report with columns required for the original report. (The tool right now is designed to create rows only).
- Export the report and save the .des file.
- Make a note of the following – You can find these information from the 3rd line of .des file
- DATASOURCE_NAME
- SERVER – Essbase server name
- APPNAME
- DBNAME
- TYPE – Essbase/Planning
- DATASOURCE_ID
- Click Create FR XML
- How many rows are required?
- How many columns are present in the report?
- Whether the current row is a Data/Text/Formula row
- Member name and Selection method (Children, Parent,….)
- Copy the generated xml and paste the information into .des file (from line 7 <GRIDOBJECT ALIASTABLE=”” till <GRIDAXIS AXISNAME=”Column”)
- Save .des file and import the report into Workspace
Example
I created a dummy report with a single row and 3 columns (these are my required columns)
Sample FR .des file (before change)
I exported the report and filled in all required information in EXCEL.
After the XML file was generated, I copied the xml file and replaced the contents of my dummy report (from line 7 <GRIDOBJECT ALIASTABLE=”” till <GRIDAXIS AXISNAME=”Column”)
Save .des file and import the file into Workspace. Open the report in FR studio and make the cosmetic changes (like formatting)
You can download the tool from here. This is tested on 11.1.2.1 version
fantastic job!
thanks dude
The attachment is no more available for downloading (link seems to be invalid).
Could you please check? The tool seems very useful !
Best regards
Thank you Osman.
Can you check your firewall settings? If you are using an official network, there could be a restriction on file storage sites.
Celvin,
Can you also make it available in XLS-2003, I am interested to get it to work in verion 9.
Thank in advance
I'll be working on a new version which will help you create an entire report (without headers and footers) with minimal adjustments to .des file and you can expect and 2003 version.
Hello Celvin:
I am trying to customize an HFM report to print rows first and then columns. Any idea if this is something that can be done just by opening the DES file?
Thanks in advance.
I don't think you can customize the print settings (except for the layout and margins) in DES file.
Why would you print rows first and then columns? That would be creating a nonsensical report
Thanks for the response. We have Entities in the rows and Accounts in the columns. We want to be able to print a set of accounts for all entities before moving on to the next set of accounts. We were told that this could be done by modifying the xml tags. In Excel this would be the "Down then Over" option which is the default.
Can you try a page break in columns and see whether that helps?
I have a prompt for Accounts in the column and hence cannot page break.
Did you try raising an SR with support?
Will do that. Thanks!
I must say, this is a pretty easier way to add hundreds of members into the FR report with minor tweaks here and there, saving a lot of time from studio. Thanks for the post, it was very informative.
Hi Celivin, do we have a way to get all the details from FR report including the formulas into excel?
-Thanks in advance!
Hi There,
Celvin Kattokaran,
Your blogs are very interesting and helpful. I wanted to speak to you in person. I am a hyperion professional too. Please email me at mrchotemiya@yahoo.com
pls provide the pre-requisite steps clearly.. i am not clear as to where to create .des file..
Can you clarify your question? You don't need to create the .des file
1. Create a dummy report with columns required for the original report. (The tool right now is designed to create rows only).
2. Export the report and save the .des file.
When you export the dummy report, it gets saved as a file with .des extension
Calvin,
Its Very informative, but i have question if i have multiple rows with multiple dimesnions . is it possible to create report.
Thanks,
Reddy
@Reddy this one is only when one dimension is used. I'm trying to create something where you can do multiple rows.
Hi Celvin
When i run the report i got that error
Error executing query: null
can you please help me to find the solution of this problem
Thanks
Faizan
I am getting error while running the report after import the .des file.Error executing query.
Can you please help me.it it very urgent.
Hi Celvin, Is the updated version available yet which can create reports with multiple dimensions?
Thanks,
Clinton H
@clinton
I didn't work on it. Was busy working on NUMSys. I'll update the blog once I create the new version
Hi Celvin,
have you ever tried running financial report from command line? because when i tried running the report using schedulebatch.cmd from command prompt we are ending with an error. please help
Do you have a version of the excel that would work for Hyperion FRS within Oracle Cloud Fusion? I’ve tried this one, but after the export, the report just looks the same. Any help would be appreciated!
I don’t have and I don’t think I’ll be making one.
Hi
I am getting error while running report after .des file import.
error executing query.
Can anyone help me to resolve the the error.I need this fix ASAP.
Thanks
Thiru