Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: multiple lines Post Reply Post New Topic
Author Message
clip
Newbie
Newbie


Joined: 25 Jan 2010
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote clip Replybullet Topic: multiple lines
     Posted: 25 Jan 2010 at 8:19am
Hi newbie here, i am back using Crystal again after a few years break & have forgotton some of the tricks i used to know, so a little help is required.
 
I have to create a report from an Access db, it is based on an invoice, so in the db, there are multiple lines that build up the invoice all with the same doc ref number, i.e.
single Product price
quantity
carriage
Discount
VAT
 
So i have a table that includes columns of:-
Doc Ref
IT_Stock i.e. Product, Carriage, discount
IT_exvat
 
What i want the report to show is the price ignoring the carriage , so it would be  IT_exvat for the line that contains the Line of the product code, minus the price IT_exvat where the line shows carriage.
But i can't rememnber how to do this?
I hope it makes sense, can you post screen shots on here?
I am not confortable with code, as i used to work with a developer who usually could sort this in VB
Thanks
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 26 Jan 2010 at 6:04am
Create formulas to do this.  Right click on Formulas and select New.  Then it is drag and drop for the fields.
 
Simple If then logic.  When your formula is ready, drop it on report where you want it.
 
HTH
IP IP Logged
clip
Newbie
Newbie


Joined: 25 Jan 2010
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote clip Replybullet Posted: 26 Jan 2010 at 6:23am
I did start along this theory & got to:-
 
if {c_itran.it_anal} = "CARR" then {c_itran.it_price} else
 {c_itran.it_price}/100
 
But then i started getting confused, so any help would be appreciated.
 
An example of the table is:-
 
IT_DOC         IT_STOCK     IT_DESC         IT_ANAL    IT_PRICE   IT_QTY
DOC75718    PROM0.85       850MM           PROMO     8700           5
DOC75718    DISCGRA        GRAPHICS     DISCGRA  -23500       1
DOC75718    CARRIAGE    CARRIAGE & P  CARR       2000           1
 
All this for the same invoice number, the IT_DOC is the field that links to other tables
 
So the answer for this should be in maths terms ((5*8700)-2000)/100 but how do i represent this in a formula?


Edited by clip - 26 Jan 2010 at 6:24am
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 26 Jan 2010 at 11:13am
I believe it would be complicated.  I think (not sure without trying) that you could use two shared variables.  The first one would be set to IT_Price if Next({IT_Doc}) = IT_Doc (thus getting the first value), then the second shared variable would be set in the set in the footer of the IT_Doc group (yes it would need to be grouped).  Then you could do your formula.  But I am not sure how the second record comes into play and if the ordering would always be correct.
 
I hope this helps.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 26 Jan 2010 at 12:01pm
If you're not using a stored proc to retrieve the data, the only option to accomplish something like your example is to use subreports.  The subreport can tell if the same matching criteria has been found.  As it stands, using your example, CR cannot replicate that math as the necessary row is not adjacent to the current column.
 
Looks like a subreport is the only way to go, and this will impact performance.  If possible, I would suggest creating a store proc or view to perform the calculations prior to handing off the data to CR.
IP IP Logged
clip
Newbie
Newbie


Joined: 25 Jan 2010
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote clip Replybullet Posted: 27 Jan 2010 at 1:10am
I have never used a stored proc before & looking at the help, it all points to a SQL server, as opposed to me using an Access database to look at a Fox Pro source, so for now i think i would have to leave that to far more experienced users than me.
To note i have not used SQL before, only access & CR
I will look into the sub reports path, the report is fairly quick to run, so performance is not an issue here.
Any help with how the subreport should be set up?
Thanks
IP IP Logged
saoco77
Senior Member
Senior Member


Joined: 26 Jun 2007
Online Status: Offline
Posts: 104
Quote saoco77 Replybullet Posted: 27 Jan 2010 at 8:03am
For what it's worth...

An Access Query can be the equivalent of a Stored procedure.

Depending on what functions are used in the Access Query Crystal Reports will see it as either a View or a Stored Procedure
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.