This chapter contains general information about running Oracle Communications Billing and Revenue Management (BRM) reports that are implemented with Oracle Business Intelligence (BI) Publisher.
For an overview of BRM Reports, see "About BRM Reports".
You can run BRM reports from the following BI Publisher components:
BI Publisher Enterprise
To run BRM reports from BI Publisher Enterprise, you need scheduling privileges.
See the discussion of scheduling a report in the BI Publisher User's Guide for information about scheduling a report.
BI Publisher Desktop
To view BRM reports from BI Publisher Desktop, you need BI Publisher design privileges.
To view reports with this component, see the discussion of using BI Publisher Desktop to schedule reports in the BI Publisher User's Guide.
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:
|
Billing Type |
The method of payment for an account. |
Select one of the following billing types:
|
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. |
The end date of the report. In some reports, you can also specify an end time. Important
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:
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:
You can enter one-digit day, month, and year specifications. Reports use the current date and time as 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. |
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:
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 BI 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 BI Publisher server in the GMT+0530 time zone, whereas reports display the time as 12/12/2003 07:00 on a BI Publisher server in the GMT-0700 time zone. This is expected behavior of BI Publisher.Reports in XML data format display all the records even if you select a specific value for a parameter.
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 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" |
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 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.
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 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.Values Entered by the User | Headings Displayed in the Report |
---|---|
Offset Days: 5 Period 1 Days: 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.
When entering dates in response to report prompts, note the following restrictions and formatting conventions:
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, BI Publisher displays an error message.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.
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 BI Publisher, restart the BI Publisher server, log in to BI Publisher, and open the report again.
Important:
To customize report queries, you should have SQL experience and be familiar with the BRM database schema.
To customize queries in BI Publisher, you must have edit privileges.
For more information on customizing queries, see "Customizing BRM Reports in Business Intelligence Publisher".
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.
To run some reports, you must load a stored procedure into the BRM database you plan to run the reports against.
Important:
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 BI 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:\BIP_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.
Important:
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.
In BI 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 BI Publisher documentation.