Running BRM Reports

3 Running BRM Reports

This document contains general information about running Oracle Communications Billing and Revenue Management (BRM) reports that are implemented with Oracle Analytics Publisher.

Topics in this document:

Selecting Oracle Analytics Publisher

You can run BRM reports from the following Oracle Analytics Publisher components:

Oracle Analytics Publisher Enterprise

To run BRM reports from Oracle Analytics Publisher Enterprise, you need scheduling privileges.

See the discussion of scheduling a report in the Oracle Analytics Publisher User's Guide for information about scheduling a report.
Oracle Analytics Publisher Desktop

To view BRM reports from Oracle Analytics Publisher Desktop, you need Oracle Analytics Publisher design privileges.

To view reports with this component, see the discussion of using Oracle Analytics Publisher Desktop to schedule reports in the Oracle Analytics Publisher User's Guide.

Understanding the Standard Parameters

Most reports contain parameters whose values are set by users before they run the reports. These parameters determine what data is extracted from the database and how that data is displayed in the reports. Table 3-1 describes the most commonly used parameters.

Note:

The default values for these parameters vary from report to report.

Table 3-1 Standard Report Parameters

Parameter	Description	Valid Values
Account Details	The account number, customer name, and other pertinent account details. Select YES to display this information.	YES or NO.
Account Status	The current account status.	Select one of the following: ALL specifies all accounts regardless of current status. ACTIVE specifies currently active accounts. INACTIVE specifies currently inactive accounts. CLOSED specifies currently closed accounts.
Billing Type	The type of payment for an account.	Select one of the following payment types: ALL CHECK CREDIT CARD DIRECT DEBIT INVOICE SUBORDINATE (nonpaying) CASH FAILED INTERBANK TRANSFER POSTAL ORDER PREPAID UNDEFINED VOUCHER WIRE-TRANSFER
Country	The country in the customer's billing address.	Select ALL, or select the name of one country. If your BRM system lets CSRs enter multiple abbreviations for the same country (for example, U.S. and USA), select ALL to ensure that all records for all the countries are included in the report.
End Date	The end date of the report. In some reports, you can also specify an end time. Important User-specified end dates are not inclusive. User-specified end date-times are inclusive. Default end dates are not inclusive. For example, to show data for the period 1/1/02 00:00:00 through 1/30/02 23:59:59, do one of the following: Enter the Start Date value 1/1/02 and the End Date value 1/31/02. Enter the Start Date value 1/1/02 00:00:00 and the End Date value 1/30/02 23:59:59. For the GLSummary, GLDetail, GLSummaryB, and GLDetailB reports, another second is added to the time. For example, if the specified Start Date value is 1/1/02 00:00:00, the End Date value is 1/31/02 00:00:00. For information about the Start Date parameter, see "Start Date".	Any valid date or date-time specification in one of the following formats: MM/DD/YY (or YYYY) TIMEZONE MM/DD/YY (or YYYY) HH:MI:SS TIMEZONE You can enter one-digit day, month, and year specifications. Reports use the current date and time as the default end date and time.
Include Chart	A graphical representation of report data. Some reports produce pie charts, some produce bar charts, and some do not produce charts. Select YES to display the chart. The data displayed is up to two decimal points. A chart is displayed even if the value of a field is zero.	YES or NO.
Start Date	The start date of the report. In some reports, you can also specify a start time. User-specified and default start dates and date-times are inclusive. For example, to show the start date 1/1/02 00:00:00, enter the Start Date value 1/1/02 or 1/1/02 00:00:00. For information about the End Date parameter, see "End Date".	Any valid date or date-time specification in one of the following formats: MM/DD/YY (or YYYY) TIMEZONE MM/DD/YY (or YYYY) HH:MI:SS TIMEZONE You can enter one-digit day, month, and year specifications. Reports use the current date and time as default start date and time.
State	The state in the customer's billing address.	Select ALL, or enter the name of one state.

Note:

The dates displayed in the reports reflect the local time and time zone of the Oracle Analytics Publisher server. For example, if the date is 12/11/2003, reports display the time as 12/12/2003 after 18:30:00 on a Oracle Analytics Publisher server in the GMT+0530 time zone, whereas reports display the time as 12/12/2003 07:00 on an Oracle Analytics Publisher server in the GMT-0700 time zone. This is the expected behavior of Oracle Analytics Publisher.

Reports in XML data format display all the records even if you select a specific value for a parameter.

Specifying Time Ranges

You often enter minute, hour, and day ranges in response to report parameter prompts. This section explains how BRM reports handle time ranges:

Minute Ranges

Minute ranges are used to group events by duration. Table 3-2 shows an example set of minute-range parameter values and the column headings generated by the values.

Note:

Offset values might or might not be present, depending on the report.

Table 3-2 Example Minute-Range Parameter Values and Column Headings

Values Entered by the User	Headings Displayed in the Report
Offset Mins: 5 Period 1 Mins: 15 Period 2 Mins: 15 Period 3 Mins: 15	Column 1: "5 - 20 Minutes" Column 2: "20 - 35 Minutes" Column 3: "35 - 50 Minutes" Column 4: "50 + Minutes"

Values Entered by the User

Headings Displayed in the Report

Offset Mins: 5

Period 1 Mins: 15

Period 2 Mins: 15

Period 3 Mins: 15

Column 1: "5 - 20 Minutes"

Column 2: "20 - 35 Minutes"

Column 3: "35 - 50 Minutes"

Column 4: "50 + Minutes"

This example uses the Offset Mins value of 5 to skip events that last less than 5 minutes. The 15-minute values are added to the offset and to each preceding time range. The beginning of a minute range is inclusive, but the end is not. For example, the 20–35 minute range includes events lasting from 20:00 minutes through 34:59 minutes. Events lasting 35:00 minutes appear in the 35–50 minute range.

Hour Ranges

Hour ranges are used to group events by the time of day in which they occur. Table 3-3 shows an example set of hour-range parameter values and the column headings generated by the values.

Table 3-3 Hour Ranges

Values Entered by the User	Headings Displayed in the Report
Period 1: 8:00:00AM Period 2: 5:00:00PM Period 3: 11:00:00PM	Column 1: "08:00:00 - 17:00:00" Column 2: "17:00:00 - 23:00:00" Column 3: "23:00:00 - 08:00:00"

Values Entered by the User

Headings Displayed in the Report

Period 1: 8:00:00AM

Period 2: 5:00:00PM

Period 3: 11:00:00PM

Column 1: "08:00:00 - 17:00:00"

Column 2: "17:00:00 - 23:00:00"

Column 3: "23:00:00 - 08:00:00"

Hour-range start times are inclusive, but their end times are not. For example, the 17:00:00–23:00:00 range includes events beginning anywhere from 5:00:00 p.m. (17:00:00 hours) through 10:59:59 p.m. It does not include events that begin at 11:00:00 p.m. (23:00:00 hours).

Day Ranges

Day ranges are used for two purposes:

To group accounts by age. For an example, see the "New Account Lifetime Report".
To group payments within the time periods in which they are due. For an example, see "Accounts Receivable Detail Report".

Table 3-4 shows an example set of day-range parameter values and the column headings generated by the values.

Note:

Offset values might or might not be present, depending on the report.

Table 3-4 Day Ranges

Values Entered by the User	Headings Displayed in the Report
Offset Days: 5 Period 1 Day: 30 Period 2 Days: 30 Period 3 Days: 30	Column 1: "5 - 35 Days" Column 2: "35 - 65 Days" Column 3: "65 - 95 Days" Column 4: "95 + Days"

Values Entered by the User

Headings Displayed in the Report

Offset Days: 5

Period 1 Day: 30

Period 2 Days: 30

Period 3 Days: 30

Column 1: "5 - 35 Days"

Column 2: "35 - 65 Days"

Column 3: "65 - 95 Days"

Column 4: "95 + Days"

This example uses the Offset Days value of 5 to skip accounts that are less than 5 days old. The 30-day values are added to the offset and to each preceding period. Each time period is measured backward from the current date or the end date, depending on the report. All start days except the first are exclusive; all end days are inclusive.

For example, "5 ‐ 35 Days" includes all accounts that are 5 through 35 days old, and "35 ‐ 65 Days" includes all accounts that are 36 through 65 days old. Days are measured from 00:00:00 (midnight) to 23:59:59.

Specifying Dates

When entering dates in response to report prompts, note the following restrictions and formatting conventions:

Year Specifications

When specifying a year in the reports, you can enter a one-digit, two-digit, or four-digit value:

If you enter a one-digit value, the prefix "200" is assumed.
If you enter a two-digit value between 70 and 99, the prefix "19" is assumed: 1970–1999.
If you enter a two-digit value between 00 and 37, the prefix "20" is assumed: 2000–2037.

Note:

Do not enter a two-digit year value between 30 and 69 or a four-digit year value before 1970 or after 2037. If you do, Oracle Analytics Publisher displays an error message.

Start and End Date Specifications

When you specify report start and end dates, follow these rules:

Day specifications must be less than or equal to the number of days in the month.

For example, day specifications for September must be less than or equal to 30, and day specifications for October must be less than or equal to 31.
Month specifications must be less than or equal to 12.
Day and month specifications must not exceed two digits.
Year specifications must be one, two, or four digits. For more information, see "Year Specifications".
Do not use letters instead of numbers.

Current Date Specifications

Follow these rules for current date:

Do not modify the Current date parameter value.
To modify the current date, change the system date.
If you modify the Current date parameter, logout from Oracle Analytics Publisher, restart the Oracle Analytics Publisher server, log in to Oracle Analytics Publisher, and open the report again.

Using the SQL Queries

Note:

To customize report queries, you should have SQL experience and be familiar with the BRM database schema.
To customize queries in Oracle Analytics Publisher, you must have edit privileges.

For more information on customizing queries, see "Removing Parameters in Oracle Analytics Publisher".

Identifying Report Versions

Each BRM report displays a version number in its footer. The version number has the following syntax:

ORACLE BRM Release_No - Report_Name - Report_Version_No (O)

where

Release_No is the BRM release number.
Report_Name is the report name.
Report_Version_No is the report version number.
O means the report is designed for a BRM Oracle database.

For example, the first Oracle version of the CCChgDecl report released with BRM 7.5 displays the following version number:

BRM 7.5 - CCChgDecl - 001 (O)

This number is embedded in the report template. Use BRM_Release_No (7.5 in the preceding example) to identify which BRM database release a report is designed to support. Use Report_Version_No (001 in the example) to identify which BRM Reports update or patch a report was released with.

Loading Stored Procedures

To run some reports, you must load a stored procedure into the BRM database you plan to run the reports against.

Note:

Reports that require a stored procedure will not run against databases that do not contain the stored procedure.

Note:

Because fields used for grouping in Oracle Analytics Publisher are limited to 86 (UTF8) or 255 (ASCII) characters, report queries can return only the first 86 or 255 characters from such fields. To return all the characters from such fields, queries use a stored procedure.

Caution:

When you load a stored procedure into a database, any stored procedure with the same name is dropped or replaced. Therefore, if a database contains a customized version of a BRM Reports stored procedure, back up the custom stored procedure before loading another procedure with the same name. If you do not, you will lose the custom version.

To load stored procedures, you need the appropriate privileges. For more information, see your database administrator.

Note:

You can customize any of the stored procedures.

To load stored procedures:

At the DOS command prompt, enter:

Local_drive:\OAP_home\xmlp\XMLP\Reports\BRM Reports\Report_name\StoredProcedures

where Report_name is the name of the report.
At the prompt, enter the following command:
```
sqlplus user_name/password@Database_Name
```
where:
- user_name is the user name.
- password is the password.
- Database_Name is the service name or database alias of the Oracle database.
At the SQL prompt, enter the following query:
```
SELECT value FROM sys.nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
```
The query returns the name of the character set in which data is stored in your BRM database.

If the character set is not UTF8, do the following:

Open the Stored_Procedure.source file in a text editor:

where Stored_Procedure is the stored procedure.

Find UTF8 in the procedure's SELECT clause.

SELECT
CONVERT(SUBSTR(EVENT_ACT_WAP_INTERACTIVE_T.URL,0,254),'US7ASCII', 'UTF8' ) URL,
EVENT_BAL_IMPACTS_T.AMOUNT,
EVENT_T.ACCOUNT_OBJ_ID0,
(EVENT_ACT_WAP_INTERACTIVE_T.BYTES_IN/1048576) MegaBytes_In,
((EVENT_T.TIMEZONE_ADJ_END_T - EVENT_T.TIMEZONE_ADJ_START_T)/3600) "DURATION",
EVENT_T.POID_ID0,
ACCOUNT_NAMEINFO_T.COUNTRY,
ACCOUNT_NAMEINFO_T.STATE,
EVENT_BAL_IMPACTS_T.RESOURCE_ID

Replace UTF8 with the name of your database character set.

Note:

Make sure the name is enclosed in single quotation marks ( ' ).
Save your changes and quit the editor.

In SQL*Plus, enter this command at the SQL prompt:
```
@Stored_Procedure.source
```
where Stored_Procedure is the file name of the stored procedure.

The stored procedure is loaded into the specified database.
Quit SQL*Plus and close the DOS window.

Loading a List of Values

In Oracle Analytics Publisher, if you want to generate a report for a parameter that is not in the list of values, you must add the parameter to the list of values.

To add parameters to the list of values, you can query the BRM database using SQL statements for the parameters or you can add the parameters manually using the fixed data method.

To run the following reports, you must use the fixed data method to add the parameters to the list of values:

BillingProvider
ContentProvider
DroppedSession
TaxAllSummary
TaxExempt
TaxJurDetail
TaxJurSummary

The fixed data method allows you to hard code the report parameters in the list of values. For more information on adding parameters to the list of values, see the Oracle Analytics Publisher documentation.