FDMEE Custom Reports: Query Definition

When creating a new custom report one first needs data and the Query Definition is the starting point in the process.  As I wrote in the first post of this series the report engine for FMDEE Reports is Oracle BI Publisher.  The query engine within BI Publisher generates and XML file and that XML file is joined up with the Layout Template and Translation Template to produce the Report output.

As with most of the ERP/EPM systems on the market the database supporting the
application can be quite intimidating and the vast number of tables confusing.  Fortunately most custom reports are derivatives of existing reports and therefore using the existing Query Definitions as a starting point can be beneficial in learning the tables used and their purpose.

For this exercise I am interested in a report to list Locations and the Data Load Rules associated with that Location. 

In the Query Definition section there is a Query that looks similar to what I need.  I will copy the SQL from the 3 text boxes.  The three different boxes allow for the use of inline parameters from prompts in the WHERE clause.

Since my FDMEE Repository is on Oracle Database I will paste the SQL into SQL Developer.  Using [Ctrl] [F7] keys I can view the SQL in a nicely formatted layout.

Since I like to work in a Graphical view of my queries I will switch to the Query Builder view. This allows me to see the tables and joins easier.

The following image show some of the more frequently used tables within the FDMEE database/schema and what information they contain.

So now that I have a better understanding of the tables.

The report I want to produce is Data Load Rules per location and since the Data Load Rules are tied to Category I would like to know that as well.

Since my report only needs 3 columns I will create a new query in SQL Developer and drag the AIF_BALANCE_RULES, TPOVPARTITION and TPOVCATEGORY tables into the Query Builder and Join them as shown below.  Then I will select the columns I am interested in from the tables and finally I will set the sort order.  One tip with Reports it is faster to sort the data on query rather than within the report layout.

I will need to switch back to the Worksheet view in order to collect the SQL for my query.

In FDMEE I will create a new query definition named Location Data Load Rules and paste into the 2 text areas; the SELECT statement up to the ORDER BY into the Select Clause and paste the ORDER BY statement into the Group By/Order By Clause.

Save the Definition and then click the Validate Query to make sure that FDMEE does not have any issues with the query.

In order to work with the BIPublisher MSWord Template Builder I will need a sample data file. The Query Definition interface has a Generate XML button. Clicking this button allows me to create the sample XML data file. Typically it will return 25 rows of data.

When I open the file I can see the data structure of the query. In the case the result of my simple query there is the Location name, the Category name and the Data Load Rule name for each row of data. 

Now that have a sample data set I can create the layout for my report. But that is the topic for another post.