BO error messages

Computation errors

#COMPUTATION in cumulative aggregations

#COMPUTATION occurs in cumulative aggregations for the following reason:
• The reset context is not included in the output context.

Solution for #COMPUTATION in a cumulative aggregation
To avoid #COMPUTATION in a cumulative aggregation, the reset context must
be included in the output context. In other words, the dimension or dimensions
after the semi-colon (;) in the formula must also be listed after the operator (In,
ForEach or ForAll) on the right of the formula.

#COMPUTATION in non-aggregate formulas
In non-aggregate formulas, #COMPUTATION occurs because the output context
is not included in the input context
Solution for #COMPUTATION in a non-aggregate formula
You must:
1. Edit the formula so that the output context is included in the input context, as
described in the example above.
2. Add Max or Min to the beginning of the formula, followed by an opening
parenthesis.
3. Add a closing parenthesis at the end of the formula, then press Enter.

#MULTIVALUE

#MULTIVALUE in aggregations

#MULTIVALUE occurs in aggregations because
The output context is not included in the local context.


#MULTIVALUE in break headers and footers

#MULTIVALUE in a cell at the section level

A report section displays data in a master cell and in a block or blocks. If you use
two variables at the section level, #MULTIVALUE can occur.

Read more »

BO error messages

Computation errors

#COMPUTATION in cumulative aggregations

#COMPUTATION occurs in cumulative aggregations for the following reason:
• The reset context is not included in the output context.

Solution for #COMPUTATION in a cumulative aggregation
To avoid #COMPUTATION in a cumulative aggregation, the reset context must
be included in the output context. In other words, the dimension or dimensions
after the semi-colon (;) in the formula must also be listed after the operator (In,
ForEach or ForAll) on the right of the formula.

#COMPUTATION in non-aggregate formulas
In non-aggregate formulas, #COMPUTATION occurs because the output context
is not included in the input context
Solution for #COMPUTATION in a non-aggregate formula
You must:
1. Edit the formula so that the output context is included in the input context, as
described in the example above.
2. Add Max or Min to the beginning of the formula, followed by an opening
parenthesis.
3. Add a closing parenthesis at the end of the formula, then press Enter.

#MULTIVALUE

#MULTIVALUE in aggregations

#MULTIVALUE occurs in aggregations because
The output context is not included in the local context.


#MULTIVALUE in break headers and footers

#MULTIVALUE in a cell at the section level

A report section displays data in a master cell and in a block or blocks. If you use
two variables at the section level, #MULTIVALUE can occur.

Read more »

Oracle By Example (OBE)

Oracle By Example (OBE)
The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment. In addition to the following OBE tutorials, you can also access more product training at the Oracle University Knowledge Center.

Expand the folder of the product area or component you are interested in.

Oracle by Example

Read more »

Oracle By Example (OBE)

Oracle By Example (OBE)
The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment. In addition to the following OBE tutorials, you can also access more product training at the Oracle University Knowledge Center.

Expand the folder of the product area or component you are interested in.

Oracle by Example

Read more »

How to Add to the List of Existing Connections

Read more »

How to Add to the List of Existing Connections

Read more »

Interactive Viewing with BusinessObjects Web XI

Read more »

Interactive Viewing with BusinessObjects Web XI

Read more »

Avoid overlapping months and years

Useful way to group data by week to avoid the quandary of weeks overlapping months and years.

CREATE OR REPLACE PROCEDURE weekly_proc IS

CURSOR x_cur IS
SELECT DISTINCT SUBSTR(TO_CHAR(date1),4,3) m, SUBSTR(TO_CHAR(date1),1,2) w, COUNT (*) cnt
FROM cpad_errors
GROUP BY SUBSTR(TO_CHAR(date1),4,3), SUBSTR(TO_CHAR(date1),1,2);

x_rec x_cur%ROWTYPE;

week_var NUMBER;

BEGIN

EXECUTE IMMEDIATE 'truncate table week_test';

OPEN x_cur;

LOOP

FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;

IF TO_NUMBER(x_rec.w) < 8
THEN week_var := 1;
ELSIF TO_NUMBER(x_rec.w) < 15 AND TO_NUMBER(x_rec.w) > 7
THEN week_var := 2;
ELSIF TO_NUMBER(x_rec.w) < 22 AND TO_NUMBER(x_rec.w) > 16
THEN week_var := 3;
ELSE week_var := 4;
END IF;


INSERT INTO week_test (WEEK_NUM, TTL, MNTH)
VALUES (week_var, x_rec.cnt, x_rec.m);

END LOOP;

CLOSE x_cur;

COMMIT;


END weekly_proc;


*********************************
SELECT mnth|| ' week '|| week_num, SUM(ttl)
FROM week_test
GROUP BY mnth|| ' week '|| week_num;
*********************************

Read more »

Avoid overlapping months and years

Useful way to group data by week to avoid the quandary of weeks overlapping months and years.

CREATE OR REPLACE PROCEDURE weekly_proc IS

CURSOR x_cur IS
SELECT DISTINCT SUBSTR(TO_CHAR(date1),4,3) m, SUBSTR(TO_CHAR(date1),1,2) w, COUNT (*) cnt
FROM cpad_errors
GROUP BY SUBSTR(TO_CHAR(date1),4,3), SUBSTR(TO_CHAR(date1),1,2);

x_rec x_cur%ROWTYPE;

week_var NUMBER;

BEGIN

EXECUTE IMMEDIATE 'truncate table week_test';

OPEN x_cur;

LOOP

FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;

IF TO_NUMBER(x_rec.w) < 8
THEN week_var := 1;
ELSIF TO_NUMBER(x_rec.w) < 15 AND TO_NUMBER(x_rec.w) > 7
THEN week_var := 2;
ELSIF TO_NUMBER(x_rec.w) < 22 AND TO_NUMBER(x_rec.w) > 16
THEN week_var := 3;
ELSE week_var := 4;
END IF;


INSERT INTO week_test (WEEK_NUM, TTL, MNTH)
VALUES (week_var, x_rec.cnt, x_rec.m);

END LOOP;

CLOSE x_cur;

COMMIT;


END weekly_proc;


*********************************
SELECT mnth|| ' week '|| week_num, SUM(ttl)
FROM week_test
GROUP BY mnth|| ' week '|| week_num;
*********************************

Read more »

%TYPE vs %ROWTYPE

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database.

The %TYPE and %ROWTYPE constructs provide data independence, reduce maintenance costs, and allows programs to adapt as the database changes

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/



-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

Read more »

%TYPE vs %ROWTYPE

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database.

The %TYPE and %ROWTYPE constructs provide data independence, reduce maintenance costs, and allows programs to adapt as the database changes

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/



-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

Read more »