Databases Reference
In-Depth Information
Figure 2-66.
Dashboard chart drilled-down to the Sales department
Behind the scenes, we used the wizard to create three different charts:
•
The pie chart that shows the salary by department uses the following query:
SELECT 'f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.::::P8_DEPTNO:'||d.deptno LINK,
d.dname LABEL,
sum(e.SAL) sal
FROM emp e, dept d
where e.deptno = d.deptno
group by 'f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.::::P8_DEPTNO:'||d.deptno, d.dname
ORDER BY d.dname
•
The 2D column that shows the average, minimum, and maximum salaries by
department uses this query:
SELECT NULL LINK,
d.dname LABEL,
avg(e.SAL) as "Average Salary",
min(e.SAL) as "Minimum Salary",
max(e.SAL) as "Maximum Salary"
FROM
emp e, dept d
where e.deptno = d.deptno
and d.deptno = nvl(:P8_DEPTNO, d.deptno)
group by d.dname
ORDER BY d.dname
•
The 2D Column that shows the salary by employee uses this query:
SELECT NULL LINK,
