Displaying column dynamically in report using parameters [message #259744] |
Thu, 16 August 2007 06:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
muthaharora
Messages: 11 Registered: August 2007
|
Junior Member |
|
|
hi All,
Consider a simple table for example
name char;
age int;
sex char;
job char;
According to the requirements
All the columns of the table will be displayed to the user in the form check box.
When the user select a name only that column should be displayed in report.
when the user select name and job only that should be in the report output
May any one please guide me on how to achieve this.
Thanks and Regards
Muthahar
|
|
|
Re: Displaying column dynamically in report using parameters [message #259762 is a reply to message #259744] |
Thu, 16 August 2007 07:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If there are only those three items, create three report parameters whose values will be (for example) 1 or Y if you want to display this value / 0 or N if you don't want to display it. Those values should be set as checkbox items' "(un)checked value" values.
In Reports Builder, create a format trigger on every field which would look like this:
RETURN (:par_yn_name = 1); In English: if parameter for the 'name' item (par_yn_name, where yn = yes-no)) equals 1, it will return TRUE and 'name' will be displayed; otherwise, it will not.
Now, if there are many more items and you wouldn't want to create all those parameters, create only one which would be a CHARACTER column and its length would be equal to the number of items you are displaying. For example, it there are 7 items, it would be VARCHAR2(7). Its value might be a binary representation of the above example: 1000111 meaning "display first, fifth, sixth and seventh item". Format trigger would be the same as previously, with a minor change - you'd include a SUBSTR function to extract only desired value.
|
|
|
|
Re: Displaying column dynamically in report using parameters [message #259771 is a reply to message #259765] |
Thu, 16 August 2007 07:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
There are 8 columns in Scott's EMP table:SQL> SELECT column_id, column_name
2 FROM USER_TAB_COLUMNS WHERE table_name = 'EMP';
COLUMN_ID COLUMN_NAME
---------- ------------------------------
1 EMPNO
2 ENAME
3 JOB
4 MGR
5 HIREDATE
6 SAL
7 COMM
8 DEPTNO
8 rows selected.
SQL> In order to display those columns, you'll create a form with 8 checkboxes, every of them will have 1 if checked and 0 if unchecked.
Parameter's name will be 'PAR_EMP_COLUMNS', will be VARCHAR2(8).
If you want to display
a) EMPNO, JOB, DEPTNO => PAR_EMP_COLUMNS = '10100001'
b) SAL, COMM => PAR_EMP_COLUMNS = '00000110'
c) none of the columns => PAR_EMP_COLUMNS = '00000000'
Reports Builder: query would still beSELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp Format Trigger will have to be written for every single field, and it would look like-- EMPNO
RETURN (SUBSTR(:par_emp_columns, 1, 1) = 1; -- ENAME
RETURN (SUBSTR(:par_emp_columns, 2, 1) = 1;
etc.
-- DEPTNO
RETURN (SUBSTR(:par_emp_columns, 8, 1) = 1;
Compile, run, enjoy.
|
|
|