Re: Reports Aggravation
Date: Fri, 16 Jul 1999 21:03:35 GMT
Message-ID: <7mo6mu$r20$1_at_nnrp1.deja.com>
Jason,
Yes Reports is a bear, especially without docs.
Been there, done that. Do you like my motto:
"Success in spite of Oracle..."?
Here is how I was able to control the ORDER BY in spite of Reports altering the ORDER BY clause:
I had a child repeating frame inside of a larger parent frame. It displayed Instrument_Code, Instrument_Name, and another data column. Wish: I wanted to let the user sort either by Instrument_Name or by Instrument at will. Background: I was preparing the SQL statement in Forms and passing it to Reports as a parameter. Problem: Reports altered my ORDER BY to match the GROUP in the DATA MODEL. I could not control nor alter this fact.
I chose to fool Reports into doing my bidding, by knowing that it would make decisions based on the items in the GROUP. I created another dummy column in the SELECT statement called SORTKEY, and placed that column FIRST in the data Group. Then, when Forms created the SQL statement I made it place the actual name of the column that I wanted in the first position and alias it as SORTKEY.
Thus, when the user wanted to sort by instrument_code,
my parser would prepare:
Select
instrument_code SORTKEY,
instrument_code,
instrument_name, ....etc.
and pass this to Reports.
When the user wanted to sort by instrument_name, the code would read:
Select
instrument_name SORTKEY,
instrument_code,
instrument_name....etc.
In fact, knowing that Reports would alter the ORDER BY clause anyway, this method completely negated the need for me to even add an ORDER BY to the original query.
More difficult and more messy, yes I agree,
but ultimately very flexible and very powerful...
"success in spite of Oracle" :-)
Good Luck,
Let me know if you get stuck or have questions,
Robert Proffitt
Beckman Coulter
Brea, California
email: RTProffitt AT Beckman DOT com
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Fri Jul 16 1999 - 23:03:35 CEST