Solved

# oracle report builder

Posted on 2003-03-16
Medium Priority
878 Views
I have build a report using oracle report builder. And use the martix with group to generate the report. I use the following query:

SELECT ALL ACCOUNTS.A_NAME, COMPLEX.C_NAME,
MAIN_DATA.F_YEAR, MAIN_DATA.T_AMOUNT, UNIT.U_NAME, ACCOUNTS.A_SE
FROM ACCOUNTS, COMPLEX, MAIN_DATA, UNIT
WHERE MAIN_DATA.A_CODE = ACCOUNTS.A_CODE
AND MAIN_DATA.C_CODE = COMPLEX.C_CODE
AND MAIN_DATA.U_CODE = UNIT.U_CODE
AND UNIT.C_CODE = COMPLEX.C_CODE
ORDER BY ACCOUNTS.A_SE

The problem is order by clause is not working. It has no function. I get the same report with and with out order by cluase. Is there any restriction of using order by clause in matrix with group?
0
Question by:fakrul_alam
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5

Author Comment

ID: 8145878
HELP
0

Author Comment

ID: 8145880
oracle report builder
0

Author Comment

ID: 8145885
oracle report builder using matrix with group
0

Author Comment

ID: 8145887
matrix with group
0

Author Comment

ID: 8145888
cccc
0

LVL 22

Accepted Solution

Helena Marková earned 100 total points
ID: 8150121

Problem Description:
The user of Oracle Reports version 2.XX is attempting to create a matrix  report.

Problem Explanation:
Example: The matrix report below plots attendance in classes versus their academic departments. Each element shows the number of students attending each
class.
ART   BIOL   CHEM   COMPSCI   GEO   HIST   LIT   MATH   MUSIC   PHYS --------------------------------------------------------------------    classA  17      (numbers of students)             classB        18                    classC        21      15    classD                      28  classE                                             21  classF                              33    classG                              33  classH                                      22             classI                                      28                     classJ                                                   18  classK                                                           15  classL                                                                  17  ---------------------------------------------------------------------------     Example Tables:    CLASS_SCHEDULE    NAME  ----------------------------  CLASS_ID      NUMBER  CLASS_NAME      VARCHAR2(10)  DAYS_OFFERED      VARCHAR2(3)  START_TIME      NUMBER  END_TIME      NUMBER  STUDENTS      NUMBER  DNAME            VARCHAR2(7)  PROF            VARCHAR2(10)      ACADEMIC_DEPT    NAME  ----------------------------  ID            NUMBER  DEPT_NAME      VARCHAR2(8)  DEAN            VARCHAR2(10)
For information on creating summaries for this matrix, see referenced  PRE-1008800.6 Create Sums and Percentages in MATRIX report.

Q1 AND Q2 SELECT DISTINCT X AND Y AXIS LABELS, Q3 SELECTS DATA AND FOREI

Solution Description:
HOW TO CREATE A MATRIX REPORT: Use the following steps to create a matrix:
Solution Explanation:
THREE QUERY METHOD:
1. Create the query Q1 which provides the labels for the (top) x-axis using a select distinct statement, similar to the following:
"SELECT DISTINCT dept_name FROM academic_dept ORDER BY dept_name"
2. Create the second query Q2 which provides the labels for the vertical y-axis, as illustrated below:
"SELECT DISTINCT class_name FROM class_schedule ORDER BY class_name"
3. Click on the Cross Product Group button on the Toolbar.
4. Drag and drop the box to enclose the groups G_1 and G_2 which were formed when the queries where created and accepted. A new cross-product group box appears (G_4).
5. Create a third query Q3 (outside the cross-product group box) which selects the data column which will appear at the intersection between the Academic Department labels (dept_name columns) and the class title labels (the   horizontal rows). Ensure that the two foreign key columns are included which correspond to the distinct columns selected in the previous queries (the x-axis label dept_name and the y-axis label class_name). Select the data column itself, SUM(students), which will be the intersection (X,Y) coordinate of the matrix.
Example:
"SELECT dname, class_name, SUM(students) FROM class_schedule GROUP BY dname, class_name ORDER BY class_name"

Note: For a matrix to be meaningful, only single row values should be returned for each X and Y combination, hence the use of the group function SUM(). Use group functions to ensure that only one value appears for each (X,Y)  coordinate.
6. Use the Link tool to create the matrix relationships:
a) Click on the Link tool.
b) Find and click on the column in the first query group box (G_1) which retrieves the labels for the x-axis (dept_name).
c) Find and click on the corresponding column in the third query group box (G_3) outside the Cross Product group.     The linking column name appears at the bottom of the Cross Product group (G_4), and a link-arrow is drawn from the Cross Product group itself to the top of the soft-box of Q3. The column native to Q3 which is linked to dept_name (dept_name1) is written inside the soft box for reference.
c) To complete the relationships, repeat the above actions with the y-axis label column (class_name).
8. Generate the Default Layout. Remove the label text in the default layout sheet. Set the group that corresponds to the top (x-axis) labels (dname) to REPEAT ACROSS. Set the Group which corresponds to the y-axis labels   (class_name) to REPEAT DOWN. Set the data summary column (SUM_STUDENTS) to REPEAT DOWN.Reduce the column WIDTH on those columns which defaulted to 40 characters wide to the smallest acceptable width they can be expected to   contain.For information on creating summary totals and '% of Total' summaries, see  PRE-1008800.6 'Create a matrix with sums and percentages in aggregate totals'.

Q1 IS BROKEN INTO GROUPS - DATA IS LEFT OUTSIDE CROSS PRODUCT GROUP
Solution Description:
HOW TO CREATE A MATRIX REPORT:
Use the following steps to create a matrix:
Solution Explanation:
ONE QUERY METHOD:
1. Create the query Q1 which provides the labels for the (top) x-axis, the labels for the vertical axis, and the data column(s) which will appear at the intersection between the x and y-axis labels (This query does not select distinct values). Use a Group By clause and the Group Function SUM() ensure that only a single row value is returned for the number of students.
Example:
"SELECT dname, class_name, SUM(students) FROM class_schedule GROUP BY dname,class_name"
The Group G_1 appears, containing the 2 labels and the summary data column.
2. Break these columns into separate groups to prepare for creating a matrix by following these steps carefully. The order in which the columns are separated is critical to the successful creation of the matrix:
a) Highlight and drag the data column (sum_students) downwards and out of the  Group G_1.A new group is created called G_2. This group must be outside the Cross Product group.
Note: If other single row values in the query will appear as data in the matrix, such as those resulting from: SUM(tuition) or SUM(fees), for example, move them into the same group (G_2) as sum_students).
b) Choose one axis label from G_1 group, and pull it off to the side, horizontally, so it becomes a new group (G_3).
c) Click on the Cross Product button on the Tool Bar.
d) Drag and drop the box to enclose the groups which correspond to the x and y-axis labels (G_3 and G_1). A new cross-product group box (G_4) appears positioned above the data group containing sum_students.
e) Choose Report->Default Layout. On the Default Layout property sheet, configure the repeat directions, remove   the boilerplate, and improve the layout by doing the following:
- Remove the label text in the default layout sheet.
- Set the group that corresponds to the top (x-axis) labels (dname) to REPEAT DOWN.
- Set the Group which corresponds to the y-axis labels (class_name) to REPEAT ACROSS.
- Set the data summary column (SUM_STUDENTS) to REPEAT DOWN.
- Set the Column WIDTH on those columns which defaulted to 40 characters in width to the minimum appropriate width by changing the values manually.
- Click OK.

Henka
0

LVL 17

Expert Comment

ID: 10342020
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Henka {http:#8150121}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
###### Suggested Courses
Course of the Month10 days, 11 hours left to enroll

#### 765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.