Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 889

# oracle report builder

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
fakrul_alam
• 5
1 Solution

Author Commented:
HELP
0

Author Commented:
oracle report builder
0

Author Commented:
oracle report builder using matrix with group
0

Author Commented:
matrix with group
0

Author Commented:
cccc
0

programmer-analystCommented:

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

Commented:
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}