zondag 8 december 2013

Dynamically showing reports side by side

I always see complex requests as a challenge. A recent example is a request to dynamically display reports side by side. Suppose you want to display a number of reports side by side but at runtime you don't know how much reports will be displayed. Could be 2, could be 4, could be anything. You don't know, it depends on the data or the user request. Difficult? Here's how to solve this.

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.