Re: How to change report columns programatically?

From: <RTProffitt_at_beckman.com>
Date: Mon, 17 May 1999 15:45:24 GMT
Message-ID: <7hpdij$jgt$1_at_nnrp1.deja.com>

Hi Kok Ming,

I am not sure I understand the scope of your question... There are various answers depending on what you mean....

1. If you mean that :REQ comes from the result of each row in the cursor, or even is a constant outside the query, then you could alter the cursor to include the value: cursor Shipped is Select a,b,c,d, :REQ||shipped_row.sp as MyNewCol from table where etc....

The report would simply contain a column MyNewCol and display it.

2) If you mean that at runtime, the actual name of the column changes, but only changes once for the whole query, then the approach I have used might work:

     I pre-parse and prepare the entire Select statement and send it onto     Reports, either as passed parameter, or into a text file which Reports    references. Thus I can control the name of the column at runtime:

    wrkval := 'Select a,b,c,d,e,' || :Req||shipped_row.sp || ' as MyColName

         from mytable where ....';
    wrkval := 'Select a,b,c,d,e,' || 'NAME as MyCol1, ADDR as MyCol2, ' ||

        ' as MyColName from mytable where ....';     The logic of of your PL/SQL block determines the name of the column     to be used, while Reports uses a standard name that never changes     (Mycol1, mycol2,etc).

3) If you mean that you are actually changing the name of which column to be returned from the table with EACH row, this is more problematic it seems to me....

row 1:     name, address, city, state, etc.
row2:     Middle name, address, city, state, etc.
row3:     Company, address, city, state, etc.

row4: othercolumn, address, city, state, etc.

By the time the query is executed inside Reports, there has to be a consistent column. Seems like the only way to do this would be to have a column function go figure out what to get and return, or possibly a DECODE.

1. Select a,b,c,d, MyColFunc(x,y) as MyNewCol from table where....;

   Function MyColFunc(x ...., y....) return varchar2 as    begin

    .....figure out which column you are interested in....
    .... Select from table (again) using exact key (x,y...etc)
   IF ...... Then
      wrkReturnVal := NAME;
   Else
      wrkReturnVal := COMPANY;

  .....etc....
  END IF:
RETURN wrkReturnVal;

b. Select a,b,c,d,

            DECODE ( Firstdecision, NAME,
                   DECODE( Seconddecision, COMPANY,
                         DECODE(thirddecision, ANOTHERCOL, OTHERCOL)
             as MyNewCol
        from tablex where .... etc;

Robert Proffitt
Beckman Coulter
Brea California
RTProffitt_at_beckman.com

In article <7hokhh$2oc$1_at_nnrp1.deja.com>,   oldorder_at_yahoo.com wrote:
> Is there a way to alter Reports 5.0's report column output using PL/SQL?
> I'm trying to use a cursor to add a report's column value with another SQL
> query resultset, and place it in a new column?
>
> FOR shipped_row IN shipped LOOP
> :MY_COL = (:REQ + shipped_row.sp);
> END LOOP;
>
> But Reports Builder reports :MY_COL as a bad bind, though it's in the report
> layout.
>
> What is exactly wrong?
>
> Kok Ming
> Undergrad Faculty of IT, QUT Brisbane
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Mon May 17 1999 - 17:45:24 CEST