Home » Developer & Programmer » Reports & Discoverer » Highlight Changed data
icon5.gif  Highlight Changed data [message #186805] Wed, 09 August 2006 12:17 Go to next message
ghamm2070
Messages: 8
Registered: March 2006
Location: Denver
Junior Member
Hello experts

I'm building a report in 6i 8.1.7 database. I have a requirement to highlight/bold any field on the report that changed from the previous instance of the report. Luckily the tables I access have a pointer back to the previous row of data in the table (last_r_id) so I can get at the data I need for the compare.

This report is already very complicated and I'd like to avoid complicating things further by taking the wrong approach.

Has anyone ever had a need to do something like this? If you have please give me a brief description of what you did:

created another query with the previous data and compared with a format trigger, Unioned the old and new data and compared with a format trigger etc.

Any input you can provide will be a big help.

Regards
Re: Highlight Changed data [message #208448 is a reply to message #186805] Sun, 10 December 2006 23:13 Go to previous messageGo to next message
Mike_23
Messages: 11
Registered: December 2006
Location: Australia
Junior Member
ghamm2070,

I'm guessing that you are referring to Intergraphs Marian application database? If so, I did a similar thing for a PO report....Let me know if you want details.

Michael.
icon7.gif  Re: Highlight Changed data [message #208631 is a reply to message #208448] Mon, 11 December 2006 10:23 Go to previous messageGo to next message
ghamm2070
Messages: 8
Registered: March 2006
Location: Denver
Junior Member
You are correct this is an Intergraph Marian application (good call). The report I'm working on is a Requisition document but I'm sure your approach for POs would work as well.

Any information you could provide on your approach would be helpful. We are running Marian version 5.5.3 at the present time but plan a migration to 6.1.5 sp20 this coming weekend.

By the way I'm glad you replied, It's difficult to find Marian users that develop custom reports and forms. Maybe we can help each other out as the need arises. Best regards.

Re: Highlight Changed data [message #208656 is a reply to message #186805] Mon, 11 December 2006 16:16 Go to previous messageGo to next message
Mike_23
Messages: 11
Registered: December 2006
Location: Australia
Junior Member
You wont believe it but I have actually already done one for the Req also (just forgot that I had!!) Mine, however, only really is used for qty changes.

---------------------------------------

What I did was created a function and called it something like RLI_Change and placed it within the RLI SQL group. Then I used the following code to look for changes:

function CF_RLI_ChangeFormula return Char is
retval varchar2(12);
begin

SELECT rli_id
INTO retval
FROM m_sys.m_req_line_items rli, m_sys.m_reqs req, m_sys.m_commodity_codes cc,
m_sys.m_commodity_code_nls ccn,
m_sys.m_commodity_code_layouts ccl,
m_sys.m_idents i,
m_sys.m_ident_nls idn
WHERE req.r_id = rli.r_id
AND rli.ident = i.ident(+)
AND i.ident = idn.ident(+)
AND idn.nls_id(+) = 1
AND i.commodity_id = cc.commodity_id(+)
AND cc.commodity_id = ccn.commodity_id(+)
AND ccn.nls_id(+) = 1
AND ccl.commodity_id = i.commodity_id
AND req.r_supp > 0
AND ((rli.last_rli_id IS NULL) OR (TRIM(TO_CHAR(rli.total_release_qty-rli.last_total_release_qty, '9999999990.99')) <> '0.00')
OR DECODE(cc.commodity_code,'', idn.description, REPLACE(to_char(ccl.layout_long),chr(10),' ')) !=
(
SELECT DECODE(cc.commodity_code,'', idn.description, REPLACE(to_char(ccl.layout_long),chr(10),' '))
FROM m_sys.m_req_line_items rli1,
m_sys.m_reqs req1,
m_sys.m_commodity_codes cc,
m_sys.m_commodity_code_nls ccn,
m_sys.m_commodity_code_layouts ccl,
m_sys.m_idents i,
m_sys.m_ident_nls idn
WHERE req1.r_id = rli1.r_id
AND rli1.ident = i.ident(+)
AND i.ident = idn.ident(+)
AND idn.nls_id(+) = 1
AND i.commodity_id = cc.commodity_id(+)
AND cc.commodity_id = ccn.commodity_id(+)
AND ccn.nls_id(+) = 1
AND ccl.commodity_id = i.commodity_id
AND req1.r_code = :WP_REQ_ID
AND req1.r_supp = (:WP_SUPP - 1)
AND req1.proj_id = USER
AND ((actual_ind = DECODE(:WP_RTYPE, 'Requisition', 'N', 'Y')) OR (actual_ind = DECODE(:WP_RTYPE, 'Requisition', 'Y', 'N')))
AND actual_ind = DECODE(:WP_RTYPE, 'For Purchase', 'Y', 'N')
AND RLI1.RLI_ID = :RLI_ID
))
AND RLI.RLI_ID = :RLI_ID
AND rownum < 2;

RETURN retval;
EXCEPTION WHEN OTHERS THEN
RETURN null;

end;

----------------------------------
Then on the required fields, I created a format trigger with this code:

function F_Prev_QtyFormatTrigger return boolean is
begin

IF (:rli_id = :CF_RLI_CHANGE) AND :PREV_QTY > '0.00' THEN
srw.set_text_color('red');
srw.set_font_face('Verdana');
srw.set_font_size(6);
srw.set_font_weight(srw.bold_weight);
srw.set_font_style(srw.plain_style);
RETURN (TRUE);
ELSE
IF :PREV_QTY > '0.00' THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END IF;

end;

----------------------

Hope it helps ya!! It would actually be good to keep in contact because like you I'm constantly creating reports for the Marian app and dont know of many other people who do this. Cool
Also, be careful of creating forms for Marian because I heard that they may cause problems when doing future upgrades!!
Re: Highlight Changed data [message #208896 is a reply to message #186805] Tue, 12 December 2006 08:17 Go to previous messageGo to next message
ghamm2070
Messages: 8
Registered: March 2006
Location: Denver
Junior Member
Thanks for the information Mike. There's 2 of us here in Denver working for a Construction, Engineering and Mining company that create custom reports and forms in the Marian application.

Gary Hamm gary.hamm@wgint.com
Ken Thurston ken.thurston@wgint.com

Send us you email if you'd like. Maybe we can help each other out in the future.

Re: Highlight Changed data [message #208941 is a reply to message #208896] Tue, 12 December 2006 12:00 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe Gary and Ken will be thankful to you for revealing their business e-mail addresses to spammers.

You could have sent them (if necessary) directly to Mike via Private Message or any other way, but leaving them here ... huh, I'd say it is a BAD idea.
Previous Topic: Excel
Next Topic: How to fix error "uncompiled PL/SQL" or "uncompiled program unit"?
Goto Forum:
  


Current Time: Tue Dec 03 07:43:59 CST 2024