To make it more understandable, I will demonstrate this with the good old EMP and DEPT tables so make sure you have access to these tables. We will make a page with a tabular form based on the EMP table and the dynamic reports beneath.
First, create the tabular form. Just create a page with a form of type tabular form. Select the EMP table as the base table for this form. Select all the columns from the table.
Here are the steps to create the dynamic reports:
- In the application builder go to the page where you just created the tabular form
- In the regions section, click the add region icon (the "+" sign)
- Select "pl/sql dynamic content" and click next
- Enter a title for the region and click next
- In the pl/sql source text area, enter the following:
declare
cursor c_col is
select distinct deptno
from emp
order by 1;
--
cursor c_emp (b_deptno in number) is
select deptno
, empno
, ename
, job
, sal
, comm
from emp
where deptno = b_deptno;
--
cursor c_dept (b_deptno in varchar2) is
select dpt.deptno
, dpt.dname
, dpt.loc
from dept dpt
where dpt.deptno = b_deptno;
--
r_dept c_dept%rowtype;
l_query varchar2(4000);
begin
sys.htp.p('<table>');
sys.htp.p('<tr>');
for r_col in c_col
loop
open c_dept(r_col.deptno);
fetch c_dept into r_dept;
close c_dept;
--
sys.htp.p('<td>');
sys.htp.p(' <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
sys.htp.p('<table class="uReport uReportStandard">');
sys.htp.p('<thead>');
sys.htp.p('<tr>');
sys.htp.p('<th id="EMPNO">Empno</th>');
sys.htp.p('<th id="ENAME">Ename</th>');
sys.htp.p('<th id="JOB">Job</th>');
sys.htp.p('<th id="SAL">Sal</th>');
sys.htp.p('<th id="COMM">Comm</th>');
sys.htp.p('</tr>');
sys.htp.p('</thead>');
for r_emp in c_emp(r_col.deptno)
loop
sys.htp.p('<tr>');
sys.htp.p('<td>'||r_emp.empno||'</td>');
sys.htp.p('<td>'||r_emp.ename||'</td>');
sys.htp.p('<td>'||r_emp.job||'</td>');
sys.htp.p('<td>'||r_emp.sal||'</td>');
sys.htp.p('<td>'||r_emp.comm||'</td>');
sys.htp.p('</tr>');
end loop;
sys.htp.p('</table>');
sys.htp.p('</td>');
end loop;
sys.htp.p('</tr>');
sys.htp.p('</table>');
end;
First, the script runs through all distinct departments. For each deptno, a HTML column (<td>) within a HTML table is created so that each collection is displayed separately. The line with the classes (uReport and uReportStandard) makes sure that the reports look just as the standard reports.
- Click next
- Click create region
The region is ready. Run the page and use the tabular form to update the department for some employees. Especially when you add more departments and move some employees to the new departments, you will see that the number of reports extends to the right side. You could add a scrollbar to the region by entering the following in the region attributes text field in the attributes section of the region:
style="height: 400px; width:1000px; overflow:scroll"
If you have APEX 4.2, use theme 26 and the template for the scrollable content region. Otherwise, if you are using another theme, create a new template as a copy of the scrollable content region template.
This looks quite good. However, the customer could just ask for an extra requirement that the reports should be responsive. So no scrollbar but instead a region with a report for each department. And when resizing the browser, or displaying on a mobile device, the regions are stacked. In that case, use the following pl/sql code instead of the above code:
declare
cursor c_col is
select distinct deptno
from emp;
--
cursor c_emp (b_deptno in number) is
select deptno
, empno
, ename
, job
, sal
, comm
from emp
where deptno = b_deptno;
--
cursor c_dept (b_deptno in varchar2) is
select dpt.deptno
, dpt.dname
, dpt.loc
from dept dpt
where dpt.deptno = b_deptno;
--
r_dept c_dept%rowtype;
l_query varchar2(4000);
l_col number(2);
l_num_of_cols number(2) := 0;
l_rowcount number(2) := 1;
begin
select count(distinct deptno)
into l_num_of_cols
from emp;
--
if l_num_of_cols > 4
then
l_col := 2;
else
l_col := 12 / l_num_of_cols;
end if;
--
sys.htp.p('<div id="uOneCol:">');
sys.htp.p('<div class="apex_grid_container">');
sys.htp.p('<div class="apex_cols apex_span_12">');
sys.htp.p('<div class="apex_row">');
for r_col in c_col
loop
open c_dept(r_col.deptno);
fetch c_dept into r_dept;
close c_dept;
--
if l_rowcount = 1
then
sys.htp.p('<div class="apex_cols apex_span_'||l_col||' alpha">');
elsif l_rowcount = l_num_of_cols
then
sys.htp.p('<div class="apex_cols apex_span_'||l_col||' omega">');
else
sys.htp.p('<div class="apex_cols apex_span_'||l_col||'">');
end if;
l_rowcount := l_rowcount + 1;
sys.htp.p('<section id="" class="uRegion clearfix">');
sys.htp.p(' <div class="uRegionHeading">');
sys.htp.p(' <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
sys.htp.p(' </div>');
sys.htp.p(' <div class="uRegionContent clearfix">');
sys.htp.p('<table class="uReport uReportStandard">');
sys.htp.p('<thead>');
sys.htp.p('<tr>');
sys.htp.p('<th id="EMPNO">Empno</th>');
sys.htp.p('<th id="ENAME">Ename</th>');
sys.htp.p('<th id="JOB">Job</th>');
sys.htp.p('<th id="SAL">Sal</th>');
sys.htp.p('<th id="COMM">Comm</th>');
sys.htp.p('</tr>');
sys.htp.p('</thead>');
for r_emp in c_emp(r_col.deptno)
loop
sys.htp.p('<tr>');
sys.htp.p('<td>'||r_emp.empno||'</td>');
sys.htp.p('<td>'||r_emp.ename||'</td>');
sys.htp.p('<td>'||r_emp.job||'</td>');
sys.htp.p('<td>'||r_emp.sal||'</td>');
sys.htp.p('<td>'||r_emp.comm||'</td>');
sys.htp.p('</tr>');
end loop;
sys.htp.p('</table>');
sys.htp.p('</div>');
sys.htp.p('</section>');
sys.htp.p('</div>');
end loop;
sys.htp.p('</div>');
sys.htp.p('</div>');
sys.htp.p('</div>');
sys.htp.p('</div>');
end;
The difference with the first code snippet is that here a number of div's are used to get the responsive behaviour. For the responsiveness, a 12-column layout is used. If two regions are displayed, the two regions get the class apex_span_6 (so 12 divided by 2 columns). If three regions are displayed, the three regions get the class apex_span_4 (so 12 divided by 3 columns). The first region also gets the alpha class, the last region also gets the omega class.
How do I know this? Well, create a new application with theme 25 (the responsive theme) and create a number of reports and display them side by side. Then use the inspect element or view source option from your browser to check what HTML code is generated by APEX.
Run the report. Resize the browser so that you can see that the reports will be stacked when the browser's width is too small to show them all side by side.
