Home » Developer & Programmer » Reports & Discoverer » Displaying column dynamically in report using parameters
Displaying column dynamically in report using parameters [message #259744] Thu, 16 August 2007 06:51 Go to next message
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 messageGo to next message
Littlefoot
Messages: 20896
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 #259765 is a reply to message #259762] Thu, 16 August 2007 07:21 Go to previous messageGo to next message
muthaharora
Messages: 11
Registered: August 2007
Junior Member
Hi Littlefoot,

Thanks for you reply, may you please explain me in details using varchar which you have mentioned since i have many columns. Also if possible give me the sample code.

Thansk and Regards
Muthahar
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
Littlefoot
Messages: 20896
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 be
SELECT 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.
Previous Topic: list box for passing parameters
Next Topic: report calling graph
Goto Forum:
  


Current Time: Tue Dec 06 06:48:45 CST 2016

Total time taken to generate the page: 0.13389 seconds