OraFAQ Forum: Reports & Discoverer » Displaying column dynamically in report using parameters

Home » Developer & Programmer » Reports & Discoverer » Displaying column dynamically in report using parameters

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Displaying column dynamically in report using parameters [message #259744]

Thu, 16 August 2007 06:51

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

Report message to a moderator

Re: Displaying column dynamically in report using parameters [message #259762 is a reply to message #259744]

Thu, 16 August 2007 07:13

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Displaying column dynamically in report using parameters [message #259765 is a reply to message #259762]

Thu, 16 August 2007 07:21

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

Report message to a moderator

Re: Displaying column dynamically in report using parameters [message #259771 is a reply to message #259765]

Thu, 16 August 2007 07:43

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	list box for passing parameters
Next Topic:	report calling graph

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 18:33:24 CST 2024