Field in crystal reports duplicating lines from quote
Using Crystal Reports XI on windows XP accessing SQL 2000 running on windows 2003 server using a Visual Manufacturing database.
This is hard to describe accurately but here goes...
I am running a report that uses QUOTE, QUOTE_LINE, QUOTE_PRICE and QUOTE_Binary_to_notes.
QUOTE_Binary_to_notes is a view in the DB:
CREATE VIEW dbo.[Quote Binary to Notes]
AS
SELECT TYPE, OWNER_ID, CONVERT(varchar(8000), CONVERT(binary(8000), NOTE)) AS cnote
FROM dbo.NOTATION
WHERE (TYPE = 'Q')
I am running the following to get @Quote Line Dollars:
if isnull({QUOTE_PRICE.TRADE_DISC_PERCENT})
then {QUOTE_PRICE.QTY}*{QUOTE_PRICE.UNIT_PRICE}
else {QUOTE_PRICE.QTY}*{QUOTE_PRICE.UNIT_PRICE}-({QUOTE_PRICE.QTY}*{QUOTE_PRICE.UNIT_PRICE}*({QUOTE_PRICE.TRADE_DISC_PERCENT}/100))
This is a summation of the line entries in the quote and is summed in the group footer for each USER_ID.
If I have QUOTE_Binary_to_notes in the report, it duplicates each line and repeats this formula for every note that has been entered in the quote. So, if a quote has had 4 quote notations entered, this formula is repeated 4 times and summed up which is very frustrating.
I need someway of either suppressing the extra line entries (which I have been unable to do so far)
If you can offer me any help on this I would be eternally grateful.
Software/Hardware used:
Software/Hardware used:
ASKED:
June 26, 2009 6:01 PM
UPDATED:
July 15, 2009 1:00 AM
Answer Wiki:
Well, obviously the View creates those extra line items because you have several notations per quote. You say that you want to suppress those extra line entries. Does this mean that you are not interrested in those extra notes at all? If so, you can modify QUOTE_Binary_to_notes:
<pre>SELECT TYPE, n.OWNER_ID , CONVERT(varchar(8000), CONVERT(binary(8000), NOTE)) AS cnote
FROM NOTATION as n
JOIN (SELECT MIN(ROWID) AS ROWID, OWNER_ID FROM NOTATION GROUP BY OWNER_ID) AS f ON n.ROWID = f.ROWID
WHERE n.TYPE = 'Q'</pre>
This will enable you to see only the first notation per quote. In case you want to see all notations, you will have to transpose rows to columns and then concatenate those notations (columns) in order to get one notation field per quote. Here is a hint on what you might be dealing with:
<a href="http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=45648&enterthread=y">http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=45648&enterthread=y</a>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
