Oracle Enterprise Repository Reports and International Number Formats

(Author: Marcelo Parisi, Staff Consultant, Oracle Brazil)

Oracle Enterprise Repository (OER) provides several enterprises, across the globe, the ability to manage their service lifeycles, perform advanced registration workflows, publish their services catalogue, monitor metrics and analytics, implement security policies, while providing extended metadata management and IDE plugins

One of the most useful features of OER is its ability to generate reports which not only help you manage your runtime but also provide high level metrics which give you visibility into your performance around Service Level Agreements (SLA), Return on Investments (ROI) and more.

As an Oracle consultant, I have installed and configured OER in several customer environments in Portuguese (in Brazil). This article aims to document my experience in making OER reports more useful to non-English speaking environments in international environments.

Number Formats – A Comma Can Change Everything

Number formatting has a small but crucial difference across some major languages. For example, 1234.56 written in English, would be written as 1234,56 in Portuguese (and many Latin American and European languages). The comma and dot in a number can have totally opposite meanings in two languages. For example, a comma is treated as a unit separator in English whereas it signifies a decimal place in Portuguese, Spanish, Dutch, German and several other languages. It’s easy to see how this can be misleading.

Since the client side of OER typically runs in the local language (based on browser setting), numbers are stored in OER in their native format.

When you try to then run an out-of-the-box OER report, say, Consumer Reported Asset Value (by Asset), you might hit this:

This happens due to the difference in numbering format. Taking a closer look at the report you can see that it is running this:

After taking a look at that SQL we can find that the value that is giving us problems, in this case, is from here:

If you run that SQL directly in the database, the following result is shown:

This happens because the value is stored in a string format in the database, and there are some functions in the Reports that are used to convert these from string to numeric, in order to perform calculations on it, such as TO_NUMBER, CONVERT and CAST.

Making Numbers More Relevant

There are several ways to make this work – here I have chosen to use a replace function. However, the solution needs to be as per your specific requirement and case.

The solution applied here is to utilise replace() SQL function to replace the comma character with a point. We can do this either by editing the report through BI Publisher interface (OER comes with a restricted use licence for BI), or by editing the .xdo file directly. I prefer editing the .xdo file directly. Here’s a step-by-step description:

1. Open the .xdo file related to the report we want to fix, in this case Consumer Reported Asset Value (by Asset).xdo, and find the locations where it is converting the number. In this particular file, for e.g., that is on lines 46, 62, 65, 78, 79, 177, 190, 193, 290, 311 and 312:

to_number(NVL(axi.stringvalue, 0)) AS PNHS
CASE :p_order_by WHEN 'PNHS' then to_number(NVL(axi.stringvalue, 0)) end desc,
CASE :p_order_by WHEN 'PNHR' then to_number(NVL(axi.stringvalue, 0))  end ]]>
to_number(DECODE(nvl(survey2.value, 0), '-', 0, nvl(survey2.value, 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerGrossSavings,
to_number(DECODE(nvl(survey3.value, 0), '-', 0, nvl(survey3.value, 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerTimeToReuse,
CONVERT(numeric,ISNULL(axi.stringvalue,'0')) AS PNHS
CASE :p_order_by WHEN 'PNHS' then CONVERT(numeric,ISNULL(axi.stringvalue,'0')) end,
CASE :p_order_by WHEN 'PNHR' then CONVERT(numeric,ISNULL(axi.stringvalue,'0')) end DESC]]>
CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) AS PNHS,
CASE ORD5 WHEN 1000 then CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) end,
CASE ORD6 WHEN 2000 then CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) end desc]]>

2. Now, add the replace() function as seen below, so that the commas are replaced with a dot:

to_number(NVL(replace(axi.stringvalue,',','.'), 0)) AS PNHS
CASE :p_order_by WHEN 'PNHS' then to_number(NVL(replace(axi.stringvalue,',','.'), 0)) end desc,
CASE :p_order_by WHEN 'PNHR' then to_number(NVL(replace(axi.stringvalue,',','.'), 0))  end ]]>
to_number(DECODE(nvl(replace(survey2.value,',','.'), 0), '-', 0, nvl(replace(survey2.value,',','.'), 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerGrossSavings,
to_number(DECODE(nvl(replace(survey3.value,',','.'), 0), '-', 0, nvl(replace(survey3.value,',','.'), 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerTimeToReuse,
CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) AS PNHS
CASE :p_order_by WHEN 'PNHS' then CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) end,
CASE :p_order_by WHEN 'PNHR' then CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) end DESC]]>
CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) AS PNHS,
CASE ORD5 WHEN 1000 then CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) end,
CASE ORD6 WHEN 2000 then CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) end desc]]>

3. Save the file, and run your report again. That’s it – success!

I have used the same approach for other out-of-the-box OER Reports such as Asset Portfolio Valuation, Asset Production Detail by Project, Asset Use (by Project) and Submission Status (by Producer) with success.

Hope this goes further in making your SOA Governance and OER initiatives even more productive and rewarding.


Post a Comment:
  • HTML Syntax: NOT allowed

Latest news, updates, best practices and thoughts on Oracle SOA Governance products

Oracle SOA Governance includes Oracle Enterprise Repository, Oracle SOA Management Pack and our API Management Solution


« April 2014