This chapter describes various ways you can customize the Oracle Communications Billing and Revenue Management (BRM) report templates to meet the needs of your company:
To change the default set of parameters included in a report, see "Redefining Report Parameters".
To convert string dates to integer dates and vice versa, see "Converting Dates".
To read currencies efficiently, see "Reading Different Resources".
To decrypt encrypted data such as credit card numbers before displaying them in a report, see "Decrypting Data".
To customize the reports for other languages, see "Localizing BRM Reports".
To consolidate data from several database schemas into one report, see "Running Reports against Multiple Schemas".
For information about creating brand-specific reports, see "Reporting by Brand".
Important:
To customize the structured query language (SQL) queries used to select data from the BRM database, you should have SQL experience and be familiar with the BRM database schema.
To customize queries in Oracle Business Intelligence (BI) Publisher, you must have design privileges.
Most reports include a set of user-entered parameters. To change this set, see the following sections:
For more information on redefining parameters, see your BI Publisher documentation.
To log on to BI Publisher, do the following:
Navigate to the URL provided by your system administrator.
Select the language you prefer for the user interface.
Enter your credentials to log in to BI Publisher.
Select Accessibility Mode if you wish to render the Reports home page in an accessible tree structure.
Click Sign In.
BRM reports include default parameters. For example, the Product parameter displays the default products shipped with BRM.
To add parameters in BI Publisher:
Log on to BI Publisher.
Open the report you want to modify.
Click Edit.
The report and its properties are displayed.
Select Parameters, and click New.
Enter a name Identifier.
Select a Data Type from the Data Type list.
Enter a Default Value.
Select a Parameter Type from the Parameter Type list.
Enter the Text Setting details, if required.
Click Save.
The item you entered is now displayed under Parameters.
Do one of the following:
If the parameter type is Menu, go to the next step.
If the parameter type is not Menu, do the following:
Select List of Values, and click New.
Enter the required details.
Click Save.
Under Data Model select the report.
In the Data Template field, add the parameter under parameters section.
Modify the SQL query as required.
Click Save.
For more information about adding parameters, see the BI Publisher documentation.
The data types of the Promotion Code and Software Package parameters, described in Table 4-1, do not change:
| Parameter | Description |
|---|---|
|
Promotion Code |
Promotion codes defined by your company to track details of account registration. For example, a promotion code can identify the marketing channel that attracted a new customer. |
|
Software Package |
The name of the package used to register customers with an automatic account creation mechanism. |
Because the BRM system does not require database fields linked to these parameters to be populated when an account is created, the default BRM reports do not contain them. You can, however, add these parameters to a report.
Caution:
Add the Promotion Code and Software Package parameters to your reports only if the AAC_PROMO_CODE (Promotion Code) and AAC_PACKAGE (Software Package) fields are always populated in your BRM database. If they are not, reports containing these parameters might not retrieve all applicable records for a particular report instance.For more information, see "Using Parameters Whose Data Type Does Not Change".
In addition to adding the value to the list, you may need to modify one or more formulas in the report to process the new value correctly.
Consider the following when determining whether to modify formulas in the report:
Parameters whose values are converted to database values
For example, in the Billing Type parameter in the ARDetail report, the parameter value PREPAID is converted to the database value 10000. If you add a value to this parameter called EMPLOYEE, you need to modify the BillingTypeLowerParam and BillingTypeUpperParam formulas in the report to convert EMPLOYEE to the database value 10011.
For instructions on editing a formula, see "Adding Parameters to the Report Template".
Parameters whose database values are then converted back to parameter values
For example, in the Billing Type parameter in the ARDetail report, the database value 10000 is converted back to the parameter value PREPAID after the information is read from the BRM database. If you add a value to this parameter called EMPLOYEE, you then need to modify the BillingType formula in the report to convert 10011 to the parameter value EMPLOYEE.
For instructions on editing a formula, see "Adding Parameters to the Report Template".
Parameters whose string data is converted to a different data type
For example, when users enter dates in MM/DD/YYYY format, the reports must convert that format to UTC (Universal Time Coordinate) before querying the database.
For more information, see "Preprocessing Parameters Whose Data Type Changes".
BRM reports use two types of user-entered parameters to gather data from the BRM database:
Parameters whose string data is converted to a different data type
For example, when users enter dates in MM/DD/YYYY format, the reports must convert that format to UTC before querying the database.
Before adding this type of parameter to a report, see "Preprocessing Parameters Whose Data Type Changes".
Parameters whose string data is not converted to a different data type
For example, when users enter USA in the Country parameter, the reports match USA to values stored in the BRM database.
Before adding this type of parameter to a report, see "Using Parameters Whose Data Type Does Not Change".
Sometimes, parameters accept values in a format that does not match the format of the corresponding data in the BRM database. If you add this type of parameter to a report, you must also add a formula to convert user-entered values to the database format before the report compares the values. For examples, see the SDate and Edate formulas in the SessionPOP report.
The string data in some report parameters does not undergo a type conversion. When such parameters are set to ALL, however, the reports convert ALL to an asterisk ( * ) before querying the BRM database for matches. (Both ALL and * are string data.)
An asterisk matches all non-null fields; it does not match null (empty) fields. Because of this, the query will not retrieve database records that do not have values in the field matched with the asterisk, even when those records satisfy all other report criteria. Thus, relevant records might be excluded, producing inaccurate reports. To avoid this, the only parameters with nonchanging data types included in the default BRM reports are parameters linked to database fields automatically populated by the BRM account creation system.
Caution:
If you add such parameters to your reports, make sure they are linked to fields that are always populated in your BRM database. If they are not, reports containing the parameters might not retrieve all applicable records for a particular report instance.If the report is not open, open it in BI Publisher.
Click the View button.
Select Data from the list beside the report name.
Click the Export button.
Click Save.
Click OK. The file is saved as Report_name.xml file
where Report_name is the report you selected.
Open Microsoft Word.
Open the Report_name.rtf file.
where Report_name is the report you selected earlier.
On the Data toolbar, click Load XML.
Select the Report_name.xml file, and click Open.
On the Insert toolbar, click Field.
Select the field, and click Insert.
Choose File - Save.
Follow the instructions in "Uploading the Template".
You may come across situations where you want to upload a template after modifying a report layout. To upload a template, do the following:
In BI Publisher, click Layouts.
Click the Browse button that is beside the Upload template field.
Select the .rtf file that you modified.
Click Open.
Click the Upload button.
Click OK.
Click Save.
(Optional) To view the report, click View.
To update a formula in a report:
Open the report using Microsoft Word.
Double-click the field you want to edit.
In the Text Form Field Options dialog, click the Add Help Text button.
Click the Status Bar tab.
Select the Type your own button.
Update the formula in the Type your own field.
(Optional) If the formula does not fit in the Type your own field, click the Help Key (F1) tab, and type the formula in the field.
Click OK.
Click OK, and choose File - Save.
Log on to BI Publisher, and select the folder.
Click the report icon.
In the Folder and Report Tasks, click the Download this report link.
Click Edit.
If the parameter type is not Menu, go to the next step.
If the parameter type is Menu, select the parameter under List of Values, and click the Delete button.
Select the parameter, and click the Delete button.
Under Data Model select the report.
In the Data Template field, remove the parameter from the parameters section.
Modify the SQL query as required.
Click Save.
Follow the instructions in "Removing Parameters from the Report Layout".
In BI Publisher, click Layouts.
Click the Download link to download the report file.
Click Save.
Open Microsoft Word.
Open the .rtf file.
Remove the parameter.
Choose File - Save.
Follow the instructions in "Uploading the Template".
Important:
By default, reports will not generate any data if users do not enter a value for the State parameter. Therefore, if your BRM database does not contain state data, you should remove this parameter from your reports. To do so, you must remove all references to the parameter from the report and change the selection criteria so the queries do not select the ACCOUNT_NAMEINFO_T.STATE field.Report designers can add functions to the BI Publisher formula language by using the BIPExtension class. When you install BRM Reports, the BRM-supplied BIPExtension class is added to your system. This class is linked to the BI Publisher data engine and include the following functions.
The BIPExtension class includes these date functions, which you can use to customize reports:
Converts dates in string format to dates in efficient integer format. See "Converting Dates".
Converts dates in efficient integer format to dates in string format. See "Converting Dates".
The BIPExtension class includes these currency functions, which you can use to customize reports:
Reads the currencies and associated names of currencies from the CONFIG_BEID_BALANCES_T table and stores them in memory. See "Reading Different Resources".
After you run the infPutBeidBalanceStr function, returns the currency name for the specified ISO currency value. See "Reading Different Resources".
The BIPExtension class includes these decryption functions, which you can use to customize reports:
Initializes the AES and MD5 encryption scheme used to decrypt data. See "Decrypting Data".
Decrypts the fields containing encrypted data. See "Decrypting Data".
You use the infStrToTimet and infTimetToStr functions to convert and print dates. BRM applications store dates by using an efficient integer representation. Although this improves system performance, dates in this format cannot be printed. To convert string dates from BI Publisher to BRM database dates, use the infStrToTimet function. To convert integer dates from the BRM database to printable strings, use the infTimetToStr function.
To compare BI Publisher dates against time fields in BRM, you need to convert BI Publisher dates to BRM dates. The infStrToTimet function returns an integer (time in BRM database format) based on the user-entered date parameter value.
The infStrToTimet function uses these parameters:
String - A valid date string in MM/DD/YY or MM/DD/YYYY format
Flag - 1, 2, or 3
1 - Uses the current time
2 - Uses 00:00:00 as the current time
3 - Uses 23:59:59 as the current time
Examples
infStrToTimet ("12/1/02", 1) returns the BRM time 1038794120 (conversion performed at 17:55:20 hours).
infStrToTimet ("12/1/02", 2) returns 1038729600.
infStrToTimet ("12/1/02", 3) returns 1038815999.
To display the converted BRM time fields in a report, you need to convert BRM dates to BI Publisher dates. The infTimetToStr function returns a string based on the user-specified format.
The infTimetToStr function uses these parameters:
Int - A valid BRM timestamp
Flag - 1, 2, 3, 4, 5, or 6
1 - Returns a string containing only the date
2 - Returns a string containing only the time
3 - Returns a string containing the date and the time
4 - Returns a string containing only the day of the month (DD)
5 - Returns a string containing only the month (MM)
6 - Returns a string containing only the year (YYYY)
Examples
infTimetToStr (1031554800,1) returns 09/09/2002.
infTimetToStr (72942,2) returns 12:15:42.
infTimetToStr (1031598942,3) returns 09/09/2002 12:15:42.
Important:
Even though the return value for infTimetToStr is formatted as a date string, it cannot be processed as a date field.BRM supports multiple resources. To prevent different resources from being added together, BRM reports group resources values by resource type. The resource types are stored in ISO 8859-1 format (for example, 840 for U.S. dollars). You obtain the resource name by linking to the CONFIG_BEID_BALANCES_T table and include the following query:
INNER JOIN "CONFIG_BEID_BALANCES_T" ON "ACCOUNT_T"."CURRENCY" = "CONFIG_BEID_BALANCES_T"."REC_ID"
Note:
The maximum number of resources supported by BRM Reports is 1000. If your CONFIG_BEID_BALANCES_T table contains more than 1000 values, you will not be able to generate reports that reference that table.You can encrypt confidential data, for example credit card numbers, when you store them in the BRM database. To decrypt the encrypted data before displaying it in a report, you must add the decryption functions to the report. The initialization function you use is specific to the encryption scheme of your data: (Advanced Encryption Standard) AES or (Message-Digest Algorithm) MD5:
If your data is encrypted with the MD5 or AES encryption scheme, use the cryptInit(String aesKeys, String md5Key) function to initialize the encryption scheme that decrypts MD5-encrypted data.
aesKeys - String object to hold the keys (delimited with a space character) used for Encryption using AES scheme.
md5key - String object to hold the key used for Encryption using MD5 scheme.
Once the encryption scheme is initialized, you use the decryptData(String cipherData) decryption function (regardless of the encryption scheme) to decrypt the field values. The parameter for this function is the name of the encrypted field. For example, if the EVENT_BILLING_CHARGE_CC_T.DEBIT_NUM field in the BRM database is encrypted, the report must call the decryptData function as follows:
decryptData (DEBIT_NUM)
where DEBIT_NUM is the alias XML tag for EVENT_BILLING_CHARGE_CC_T.DEBIT_NUM in the BRM database.
If your data is encrypted with the AES encryption scheme, a report must call all the AES and MD5 keys used in BRM database.
To retrieve the values, create a new data template as follows:
Log on to BI Publisher.
Select the report in which you want to display decrypted data.
Click Edit.
Select Data Model, and click the Report_name.
Enter the following text at the end of the SELECT statement in the Data Template field:
bipext.infcryptkeys() as cryptkeys
Click Save.
Click Layouts.
In the Manage Template Files section, click the Report_name.rtf, to download the report.
where Report_name is the name of the report you have opened.
Create a namespace by adding the following text anywhere in the of the report:
<?namespace:cryptext=http://www.oracle.com/XSL/Transform/java/com.portal.pcm.Crypt?>
Enter the following text at the beginning of the report page:
<?cryptext:cryptInit(CRYPTKEYS, ’Abracadabra dabracaabrA')?>
Add the following text in the required location, to decrypt data.
<?cryptext:decryptData(decrypt_parameter)?>
where decrypt_parameter is the parameter.
For example, for the Debit_num field add the following text:
<?if:$PARAM_MASKCCNO=0?><?bipext:infReplicateString(cryptext:decryptData(DEBIT_NUM),'*', 4)?> <?end if?> <?if:$PARAM_MASKCCNO=1?><?cryptext:decryptData(DEBIT_NUM)?> <?end if?>
Choose File - Save.
Follow the instructions in "Uploading the Template", to display both the AES and MD5 decrypted data.
If your data is encrypted with the MD5 encryption scheme, the report must call the MD5 keys used in the DM pin.conf file.A report must call this function before decrypting the data.
To retrieve the values, create a new data template as follows:
Log on to BI Publisher.
Select the report in which you want to display decrypted data.
Click Edit.
Select Data Model, and click the Report_name.
Enter the following text at the end of the SELECT statement in the Data Template field:
bipext.infcryptkeys() as cryptkeys
Click Save.
Click Layouts.
In the Manage Template Files section, click the Report_name.rtf, to download the report.
where Report_name is the name of the report you have opened.
Create a namespace by adding the following text anywhere in the of the report:
<?namespace:cryptext=http://www.oracle.com/XSL/Transform/java/com.portal.pcm.Crypt?>
Enter the following text at the beginning of the report page:
<?cryptext:cryptInit(’', ’Abracadabra dabracaabrA')?>
Add the following text in the required location, to decrypt data.
<?cryptext:decryptData(decrypt_parameter)?>
where decrypt_parameter is the parameter.
For example, for the Debit_num field add the following text:
<?if:$PARAM_MASKCCNO=0?><?bipext:infReplicateString(cryptext:decryptData(DEBIT_NUM),'*', 4)?> <?end if?> <?if:$PARAM_MASKCCNO=1?><?cryptext:decryptData(DEBIT_NUM)?> <?end if?>
Choose File - Save.
Follow the instructions in "Uploading the Template", to display MD5 decrypted data.
For more information on decrypting data, see the discussion about generating an encrypted AES key in BRM Developer's Guide.
You can design a report layout using BI Publisher and Microsoft Word. For more information about designing a report layout, see your BI Publisher documentation.
BRM reports are designed to be used with the English version of BI Publisher. In addition, the reports are designed to accept only English input and to gather, process, and display only English data from BRM databases.
BRM reports cannot consolidate data from multiple BRM database schemas into a single output stream. However, they can be customized to run automatically against several schemas in succession and to list the output from each schema consecutively in the same report.
To run a report automatically against multiple schemas, follow the procedures in "Modifying Reports to Run against Multiple Schemas".
Note:
To examine a report already configured to run against multiple schemas, see the ARSummaryMDB report in the Local_drive:\BIP_Home\xmlp\XMLP\Reports\BRM Reports\MultiDB Reports\ARSummaryMDB.To provide an SQL interface between the BRM reports and your BRM database schemas, you must configure a JDBC data source for each schema you run the reports against.
By default, the reports are configured to run against a JDBC data source named BRM_DATA_SOURCE. If your system already has a BRM_DATA_SOURCE data source, you can use BRM_DATA_SOURCE for your primary schema and use the names BRM_DATA_SOURCE1, BRM_DATA_SOURCE2, and so on, for your secondary schemas.
See "Setting Up Data Sources" in the BI Publisher documentation for information about configuring a JDBC data source for secondary database schemas in your system.
Note:
When the procedure tells you to enter the data source name BRM_DATA_SOURCE, enter the name for a secondary data source instead. For example, enter BRM_DATA_SOURCE1.
Repeat the procedure for each schema you want to run the reports against.
To modify a report to run automatically against multiple database schemas, you must configure the report for every schema you want to run the report against.
Important:
To modify reports to run against multiple schemas, you should have experience designing reports in BI Publisher.Log on to BI Publisher, and select the folder.
Open the ARSummaryMDB report.
Click Report.
Select BRM_DATASOURCE from the Default Data Source list.
Click Save.
Under Data Model, select ARSummaryMDBTemplate.
In the Data Template field, add the following text:
<dataTemplate name="ARSummaryMDB_report" dataSourceRef="BRM_DATA_SOURCE">
Click Save.
Under Data Model, select ARSummaryMDBTemplate2.
In the Data Template field, add the following text:
<dataTemplate name="ARSummaryMDB_report" dataSourceRef="BRM_DATA_SOURCE1">
Click Save.
Click View.
Provide the required parameters.
Click View.