Field in crystal reports duplicating lines from quote

5 pts.
Tags:
Crystal Reports
Crystal Reports XI
SQL Server 2000
Visual Manufacturing
Windows XP
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following