Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Report building... Post Reply Post New Topic
Author Message
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Topic: Report building...
    Posted: 07 Nov 2009 at 7:47am
Hey All,
 
I'm currently starting to work on a report that show sales and buys from articles.
For my base i have 4 diferent DB's
 
1 with all the article's
2 i need to use to see how much of every article we bought
1 i need to see how much we sold
 
i want to know how much we bought and sold in a time frame
 
the link between the db's are like the following
 
DB1: Articles                 DB2: recregel        DB3: receptie
   
 
       a_a_volgnr   ->     r_volgnr
       a_omschr              r_document     ->   R_document
                                    (r_aantal)               (r_date)
 
                           ->     DB4:  h_volgnr
                                              (h_date)
                                              (h_aantal) 
 
Everything starts for my articles DB (DB1)
The stuff between the () is the field info i need to see.
 
I need the DB2 becouse my date when we bought stuff is only availeble in DB3 not in the second
a date can also be found in DB4
 
Now when i try to build this up i keep getting LOTS of results but i know i only have 200 artikles i want to check
I keep getting arround 26k results .... :( and he gives me a lot of articles 2-3-10-.. times
 
I can see how it comes but i have no clue what to do about it
 
My last resort would be working with 2 subreports as i know that will work but i'm still trying to make it all in one...
 
End result should be somthing like this
 
start date:  01-01-2009
End date:   30-01-2009
 
Article       discription                     bought    sold    
305406    Hard drive 500 GB          10              8
706304    Sound card V4                 2               9
a_volgnr       a_omschr                   r_aantal    h_aantal
 
 
Any idee's on this ?
 
 
Tnx in advance !!!
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 09 Nov 2009 at 6:15am
I am guessing, but you are probably getting multiple results because an article can be bought multiple times and then sold even more times.  You don't buy an article once and all of the shipment is sold to one person.  If you are just looking for summary data, you could probably pull a min on the date in db3 and a max for the date in db4 along with a sum for the items sold in db4, that might get rid of your dupes.
 
HTH
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 09 Nov 2009 at 6:20am
Hey Lockwelle
 
that's indeed the problem i buy a product a couple of times during that period and i sell it even more ...
for every buy it gives me all the sales or other way arround for every sale i get all the buys
 
Could you explain a bit what you mean with the min and max on the dates ?
 
Adam
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 09 Nov 2009 at 6:30am
if you are getting the data via a stored proc, then: Select Min(table.field) From table. (I get all my data from stored procs)
 
If you are getting the data by linking to the tables, what you migh try is allowing the data to duplicate (since it is not duplicated, each row is slightly different) then for the grouping, for you min/max date just use the aggregated functions in CR to get the min/max date of a field.  For each line you would display in a group footer/header the sum of the sold items, summing the bought items would be harder as you would need  a formula/running total to only add the purchased amount 1 time per purchase record (even though there are x sell records)
 
Hope this gives a path.
IP IP Logged
AdamField
Groupie
Groupie


Joined: 04 Jun 2009
Online Status: Offline
Posts: 88
Quote AdamField Replybullet Posted: 09 Nov 2009 at 6:35am

It sure does :)

This is compleet different as what i normalt do (i only link to a SQL db) and don't use any sql querry's @all that i make myself
only the stuff on record or group lvl and some formula's.
 
Think it will be a lot more easy in the end to make 2 sub reports 1 for sales and gives me a total per day and one for buy's with a total per day and just return those 2 to the main report
Will take a bit longer to load but that doesn't matter
 
Tnx a lot for thinking about it !!
 
Greets
 
Adam
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.061 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.