Home » Developer & Programmer » Reports & Discoverer » RPT2XLS.put_cell for dynamic column (Reports 6i)
RPT2XLS.put_cell for dynamic column [message #664645] Wed, 26 July 2017 01:51 Go to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
While running a report, a table with different column name (every time being run) is created. Need to populate the output directly into a excel sheet using RPT2XLS.put_cell function. How can i pass dynamic column names to this function to print the data in excel. The below example is from a static table.

FOR J IN C2
LOOP
	RPT2XLS.put_cell(1, J.CATG);
	RPT2XLS.put_cell(2, J.ITEM);
	RPT2XLS.put_cell(3, J.ITEM_NAME);
	RPT2XLS.put_cell(4, J.GRADE1);    	
	RPT2XLS.put_cell(5, J.GRADE2);
	RPT2XLS.put_cell(6, J.KN);
	RPT2XLS.put_cell(7, J.KN2);
	RPT2XLS.put_cell(8, J.KNDM);
END LOOP;
Re: RPT2XLS.put_cell for dynamic column [message #664662 is a reply to message #664645] Wed, 26 July 2017 08:09 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
Are you really doing CREATE TABLE, populating table & then running a report against it?
WHY?
Re: RPT2XLS.put_cell for dynamic column [message #664707 is a reply to message #664662] Sun, 30 July 2017 03:37 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Yes, am doing the same way. Trying to find stock for various location wherein the location code may increase going forward. These location codes will be my table column name, so i can update the stock for each item against that location (column).
Re: RPT2XLS.put_cell for dynamic column [message #664708 is a reply to message #664707] Sun, 30 July 2017 07:25 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Actually i am using a Global Temporary table.
Re: RPT2XLS.put_cell for dynamic column [message #664717 is a reply to message #664708] Mon, 31 July 2017 03:09 Go to previous messageGo to next message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
So why does the column name keep changing?
Re: RPT2XLS.put_cell for dynamic column [message #664718 is a reply to message #664717] Mon, 31 July 2017 08:20 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Since every technician is given a location code, there will be increase of location code every month. The GTT will have these location code as column name, based on which i will update the stock of the item under that particular column. The structure of table and data being updated, looks as below

Item Loc1 Loc2 Loc3 ...
CodeA 10 15 20
CodeB 20 10 23
Re: RPT2XLS.put_cell for dynamic column [message #664719 is a reply to message #664718] Mon, 31 July 2017 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
chat2raj.s wrote on Mon, 31 July 2017 06:20
Since every technician is given a location code, there will be increase of location code every month. The GTT will have these location code as column name, based on which i will update the stock of the item under that particular column. The structure of table and data being updated, looks as below

Item Loc1 Loc2 Loc3 ...
CodeA 10 15 20
CodeB 20 10 23
We speak SQL.
You should speak SQL, too.
If you designed data/table to Third Normal Form, solution would be easier.
Table should NOT contain LOC1, LOC2, LOC3, LOC4
Table should contain LOC_VALUE & new column LOC_ID.
Re: RPT2XLS.put_cell for dynamic column [message #664722 is a reply to message #664719] Tue, 01 August 2017 01:22 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
I just revised the 3rd Normal form and i don't think my table is bypassing it.

To be more clear, this report has to show the item wise, location wise stock details. The total location code in Master table is 3 (LOC1, LOC2, LOC3) as of today. End of next month it may be 5 (LOC1, LOC2, LOC3, LOC4, LOC5). So in this scenario, i am trying to build the table at run time and my table structure end of this month would be TABLE_STOCK (ITEM_CODE, LOC1, LOC2, LOC3) and end of next month should be like TABLE_STOCK (ITEM_CODE, LOC1, LOC2, LOC3, LOC4, LOC5). So i need to use this dynamically know column name with populated data and export them to excel, which i am trying to achieve using RPT2XLS.put_cell.
Re: RPT2XLS.put_cell for dynamic column [message #665925 is a reply to message #664722] Tue, 03 October 2017 02:10 Go to previous messageGo to next message
chat2raj.s
Messages: 104
Registered: October 2010
Location: Chennai, India
Senior Member
Hi, am still waiting for the solution. Any help on this.
Re: RPT2XLS.put_cell for dynamic column [message #665936 is a reply to message #665925] Tue, 03 October 2017 08:12 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: RPT2XLS.put_cell for dynamic column [message #665952 is a reply to message #664722] Wed, 04 October 2017 08:16 Go to previous message
cookiemonster
Messages: 12927
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you have a table with columns of the form <somename>1, <somename>2, <somename>3 ....
It's a fairly safe bet it isn't third normal form.

3NF would have a single column for location (in your case) and you store the multiple values in different rows.
And if you have that you wouldn't need the report to be dynamic.
Previous Topic: setting vertical elasticity overlapping on lines below it
Next Topic: Account number not expend
Goto Forum:
  


Current Time: Mon Nov 20 11:24:18 CST 2017

Total time taken to generate the page: 0.08385 seconds