APEX

Hello,

I've not been able to find any examples similar to my situation but perhaps I'm using the wrong search phrases...

I'm basically trying to create a cross-tab type report based on a dynamic query (since I do not know how many accounts a person has):

select '  SELECT x.name, x.type, ' from dual
union 
select ' max(decode(x.account,'''||m.account||''',x.amount,null)) as "'||substr(s.proj||' '||m.account,1,30)||'",'
  from db1.manager m, db1.person p, pi.charges c, db1.status s 
 where m.manager_id = p.id
   and M.ACCOUNT_ID = C.ACCT_ID
   and M.ACCOUNT_STRING = S.project
   and C.JNL = (select max(id) from db1.journ j where j.TYPE ='M')
   and p.username = :username
   and nvl(M.END_DATE, sysdate +1) >= sysdate
 group by m.account, s.proj
union 
select 'sum(x.amount) grand_total from (select m.account, c.name, c.type, c.amount '
          ||'from db1.manager m, db1.person p, pi.charges c '
          ||'where m.manager_id = p.id '
          ||'and p.username = :P68_PRINC '
          ||'and c.acct_id = m.account_id '
          ||'and c.jnl = (select max(id) '
          ||'from db1.journ j where j.TYPE =''M'')) x '
          ||' group by type, name' from dual

The output from this query for the particular manager I selected is:

SELECT x.name, x.type, 
 max(decode(x.account,'12012',x.amount,null)) as "Internal 12012",
 max(decode(x.account,'17929',x.amount,null)) as "Staged 17929",
 max(decode(x.account,'18054',x.amount,null)) as "Help Software 18054",
 max(decode(x.account,'3428',x.amount,null)) as "Mow 3428",
 max(decode(x.account,'3428',x.amount,null)) as "Mow 3428_1",
 max(decode(x.account,'3508',x.amount,null)) as "Stampede 3508",
 max(decode(x.account,'9102',x.amount,null)) as "Open Collaborative 9102",
sum(x.amount) grand_total 
from (select m.account, c.name, c.type, c.amount 
from db1.manager m, db1.person p, pi.charges c 
where m.manager_id = p.id 
    and p.username = :P68_PRINC 
    and c.acct_id = m.account_id 
    and c.jnl = (select max(id) 
   from db1.journ j where j.TYPE ='M')) x 
  group by type, name

This query generates another query that ultimately produces the report below (please pardon the alignment) that I want to see on a report page.

NAME	TYPE	Internal 12012	Staged 17929	Help Software 18054	Mow 3428	Mow 3428_1	Stampede 3508	Open Collaborative 9102	GRAND_TOTAL
#EXAM1221	M				                                                        22		                         		                                                22
#EXAM1222	M		                14.83	        14.77	                12.56		                2.22		                                                44.38
#EXAM1223	M		                6.73            	6.7	                        5.7	                        	1	                                                	20.13
THOEORY  	M		                106.5	                                                                                                        					106.5

Should I be using the report type that is based on a 'PL/SQL function body returning SQL query' for this type of report??? If so, how does that get set up?

Thanks in advance!

~Jake