Field in crystal reports duplicating lines from quote
Home > IT Answers > DataManagement > Field in crystal reports duplicating lines fr...
Mcgentrix
5 pts.
0
Q:
Field in crystal reports duplicating lines from quote
Crystal Reports XI, SQL Server 2000, Visual Manufacturing, Crystal Reports, 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.
ASKED: Jun 26 2009  6:01 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Sergio09
15 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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:

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'


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:

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=45648&enterthread=y
Last Answered: Jul 15 2009  1:00 AM GMT by Sergio09   15 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



0