By Thomas Thuresson
April 6th, 2016
FDMEE comes installed with variety of standard reports, just like in FDM. Most of the reports that came with FDM are ported to FDMEE. The big difference is that the embedded engine that renders the reports now is Oracle BI Publisher. The core data tables remain the same, making it easier to convert old custom reports to the new format.
BI Publisher uses rtf (Rich Text Format) templates that can be created and modified using MS Word, with the BI Publisher add-in installed. More on how to install and use this add-in later.
First a few details on how reports are setup in FDMEE, before we start creating new reports.
Reports are setup in FDMEE under the Setup tab, in the Reports section.
Data for a report is retrieved from the FDMEE repository database using a SQL query that is defined in the Query Definition section. For instance, this is how the default query for the Trial balance All Columns report looks like. Notice the three sections for the distinct part of the query.
Also notice the parameters in the Where Clause identified by a leading and trailing tilde character, for instance ~POVPERID~.
Here are a few of the most commonly used tables in the FDMEE repository.
|TDATASEG||Imported and transformed data. Transformed members are stored in <Dim Name>X columns, e.g. ENTITYX, and transformed data in AMOUNTX.
See the FDMEE Admin guide for details on all columns.
|TPOVPARTITION||Contains details on all Locations. Join with data tables using PARTITIONKEY|
|TPOVCATEGORY||Contains details on all Categories. Join with data tables using CATKEY|
|TPOVPERIOD||Contains details on all Periods. Join with data tables using PERIODKEY|
|AIF_BALANCE_RULES||Contains details on all Data Load Rules. Join with data tables using RULE_ID|
|TLOGPROCESS||Workflow process status for a location, category and period.
See the FDMEE Admin guide for details on all columns.
See the Report Query Table Reference section in the FDMEE Admin guide for details on the tables.
A report template is then paired with a matching query using the Report Definition section.
A report needs:
- A Name
- To belong to a Report Group
- A layout template defined using BI Publisher
- A SQL query that returns the correct data from the FDMEE repository.
If the Query is parameterized these parameters are defined in the Report Parameters section.
Reports Groups are used to groups similar reports. New groups can be created and the order the groups appear in changed.
You run reports using the Report Execution task. First select the Report Group to retrieve a list of reports in a group, select the report and click Execute.
If the report has any parameters defined you will get prompted for them before the report runs.
You can also select between a few different output formats, such as PDF, HTML or Excel.
The Execution mode determines if the report should be run in the foreground (online) or executed offline to be viewed later, using the Process Details task.
Creating and Modifying Report Templates
Now that we understand how reports and queries work in FDMEE, let’s go ahead and modify a report and even create a new report from scratch.
The first thing we need to do is to download and install the Oracle BI Publisher Desktop Office Add-in.
The add-in can be downloaded from Oracle. http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html
When the add-in is installed a new ribbon is added in Word.
Note that if you only want to modify the look and feel of an existing template, such as adding a logo you don’t even need to have the add-in installed.
Modifying an existing report template.
The templates are stored on the FDMEE server in the <Hyperion_Home>\products\FinancialDataQuality\templates directory.
But don’t worry if you don’t have server access, this directory can be accessed from FDMEE using the download and upload functionality. Just go to the Report Definitions, select a report and click on the ‘Select’ button next to the Template field.
This brings you straight to the Template directory on the server where you can use the Download and Upload buttons to download and upload template files.
Let’s download the TB All Columns file and have a look.
The areas with a grey background are form fields that are populated with data from the xml file generated when the report is executed.
Say we wanted to make a copy of this template and modify it to fit our 4 custom dimension HFM application and display the names of the dimensions instead of the generic names.
We would need to:
- Save a copy of the original template file
- Add a set of columns for the Custom4 dimension
We might also want to remove the CURKEY column as it is not populated and change the layout to landscape to fit all the columns.
The first thing we need to do is to generate an XML file with a sample set of the data for the report. We could do this modification without the sample data, if we know the column names, but having the file makes it easier.
The XML file is generated from the Query Definition using the Generate XML function.
This functionality only uses the Select Clause portion of the query so the sample data returned might not be the data you want. At least this is the case for the 220.127.116.11 PSU 100 version of FDMEE I’m using.
An easy workaround for this limitation is to create a new query based on the original one and move the Where clause statements into the Select Clause field. You can’t use parameters in this query so we would have to hard-code our filters. Just make sure all the columns you need for the report are in the query.
Execute the XML file generation and save the file. The file contains a data set with a number of rows of sample data.
We can now use this file and upload it in the BI Publisher Add-in.
Click the Sample XML Icon to upload the data xml file.
Notice that more BI Publisher functions are now available.
We can now go ahead and add the new dimension pair by copying an existing column, for instance copying the Custom3 pair (UD3 and UD3X), pasting them next to the original ones and renaming them.
Double-click on the grey fields that need to be changed to open the BI Publisher Properties dialog and change the Data Filed and Text to Display field.
Also change the headings and page layout to landscape while you’re at it.
Use the Preview functionality to test that the report works, such as the PDF preview.
Before we upload the modified template to the FDMEE server we also need to generate a translation file as localization was introduced in 18.104.22.168 of FDMEE. Click on the Extract Text link to generate a translation (XLIFF) file. If there is no matching xlf file for a template the report will not generate when executed.
Save the .xlf file as TemplateName_<language code>.xlf, such as:
When we’re done with the modification we need to upload the modified template to the server and connect it to the report definition (or create a new report definition).
Let’s create a new report definition.
In the Report Definition section we could select the original report definition and use the Copy Current Report function, or create a new blank one by using the Add functionality. The Copy Current Report works great for us in this case since we’re just modifying an existing report and will keep most of the settings the same.
Give the report a new name and upload and select our modified template.
Also, let’s not forget to upload the translation file into the correct language folder under the templates directory.
Finally click the save button to save the new report definition.
We now have a new report, based on an original default report.
As a final step let’s test the report using the Report Execution task.
Looks like it is working!
We’ve now covered all the moving parts of a report in FDMEE, from queries and variables to templates and translation files. We’ve also seen how easy it is to modify an existing report, adding new content and changing the look and feel of a template.
In the next installment I’ll show that it’s just as easy to create a new report from scratch.