I am sure this will help you!
Requirement: Display a Text String In Place of Column Headings
Display a master-detail report for departments and employees. If a department has
no employees, suppress the detail column heading frame and display a piece of text
instead.
Steps
1 Create a summary column, :count_emp, in the master group that counts the
number of employee records (reset = master group).
2 Create a piece of boilerplate text, “No employees in this department”.
3 Position the text behind the column heading frame.
4 Code a format trigger on the column heading:
5 Either code the reverse logic in the format trigger of the boilerplate text item
created in Step 2, or ensure that the column heading frame has a solid fill.
function hide_headings return boolean is
begin
if :count_emp = 0 then
return(false);
else
return(true);
end if;
Note: Code format triggers at the highest possible level of the layout hierarchy. The
format trigger on the column headings frame suppresses all objects enclosed by the
frame. Do not duplicate code unnecessarily by coding the same trigger on all
individual objects in the frame; this can cause increased processing and
maintenance overheads.
Main Topics
Browse All Topics
by: HenkaPosted on 2004-01-27 at 23:34:47ID: 10216278
I think that Note:29736.1 from Metalink can help you:
PLACING ZEROES WHEN NO DETAIL RECORDS ARE FOUND
The example report below fills in the "hole" by inserting a zero value into the Sal column of the detail group of the master-detail report when there is no employee in the Emp table corresponding with deptno = 40.
Deptno Dname Deptno Ename Sal
------ --------------- ------ ------------- ------
10 ACCOUNTING 10 CLARK 2450
10 KING 5000
20 RESEARCH 20 SMITH 800
20 JONES 2975
20 SCOTT 3000
30 SALES 30 ALLEN 1600
30 WARD 1250
40 OPERATIONS 0
Note: Do not confuse empty spaces in the detail records with null values. A null value is an actual value fetched from the database. The spaces in the master-detail report are empty because nothing has been fetched to fill them.
DEFINE THE DATA MODEL
The example report will be based on two queries, using two tables (Dept and Emp tables). In the Dept table, there is an instance of deptno = 40 but no corresponding employee in the Emp table. When there is no instance of the deptno = 40 in the Emp table, you want to insert a zero value to fill in the "hole". Perform the following steps:
1. Create two queries: Q_1 and Q_2
Master (Q_1): SELECT deptno, dname FROM Dept
Detail (Q_2): SELECT deptno, ename, sal FROM Emp
Join Q_2 to its parent, Q_1, and specify DEPTNO column for the join.
2. Below Deptno in G_1 (G_1 is the groupname for Q_1), create the Summary column from the Tool palette named CS_1.
3. Double-click on the new column CS_1 to display its property sheet. You need to define the Source column, Function, and Reset level.
4. The Source column contains the field from the detail group of Emp table. For this example, select ENAME.
5. The Function column tells Oracle Reports how to compute the Summary column values. Select COUNT.
6. The Reset level determines when to reset the value of the Summary Column to zero. Select G_1, which is the groupname of the first query, Q_1.
DEFINE THE LAYOUT
From the Menu Option, select Tools ---> Default Layout (Reports V2.5) or select Reports ---> Default Layout (Reports V2.0). Use the Master/Detail style as the default layout for the report. Next, create a boilerplate text item containing a zero '0' and perform the following steps:
1. Go to the layout editor and select the Text tool.
2. Click and drag to create an empty boilerplate object named B_Zero in the Detail repeating frame (R_2) over the F_Sal field. It is recommended to place the B_Zero directly overlapping the F_Sal field for the zero to appear if no detail records are returned.
3. Type the number 0. Then click outside B_Zero to deselect it.
4. Double-click on B_Zero to display its property sheet.
5. If you are using Reports V2.5, click on Edit, then OK, your code should appear as follows in the Format Trigger:
FUNCTION B_ZeroFormatTrigger RETURN BOOLEAN IS
BEGIN
IF :CS_1 = 0 OR :CS_1 IS NULL THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;
If you are using Reports V2.0, then your code should appear as follows in the Format Trigger:
IF :CS_1 = 0 OR :CS_1 IS NULL THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
6. Select the detail repeating frame (R_2), then select the Fill Color Tool to display the color palette, and give the detail repeating frame (R_2) a Red fill color.
7. Move B_Zero behind R_2 until it disappears. First, click on B_Zero. Next, select these Menu Options: "Arrange ---> Send to Back" once then "Arrange ---> Move Forward" twice.
8. Do Step 6 again, but this time, select the detail repeating frame (R_2) with a No Fill when the color palette is displayed.
9. Run the report.