Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: pulling field data by date Post Reply Post New Topic
Author Message
thetonyleone
Newbie
Newbie


Joined: 21 May 2009
Online Status: Offline
Posts: 4
Quote thetonyleone Replybullet Topic: pulling field data by date
     Posted: 21 May 2009 at 8:43am
I'm sorry I am extremely new to Crystal and even using 8.5 for Mas 90/200 (eww I know)

I was wondering if anyone could let me know how to pull field data by a date time frame.

Basically I have daily sales orders and I needed to pull the cases sold and pounds sold by item number for this week in this year and for this week last year, then for QTD this year and last year, and YTD this year and last year.

I wasn't quite sure how to pull those specific field data for a specific time period.

Thanks in advance for the help.
Tony
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2009 at 9:22am
Is this 3 reports, 1 report with the option of selecting one of the 3 items or 1 report with all 3 options in it?
IP IP Logged
thetonyleone
Newbie
Newbie


Joined: 21 May 2009
Online Status: Offline
Posts: 4
Quote thetonyleone Replybullet Posted: 21 May 2009 at 9:34am
If possible one report with selecting all 3. I attached a rough drawing of the report.

You can see the rough drawing here:
http://www.backofficesite.com/report.pdf
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2009 at 9:48am
well pulling the data is pretty straight forward but the rest is going to take some work.
I would pull the data based on the largest possible group (YTD) and then use formulas to be able to identify within these records which of these records fall into the sub categories. Assuming your YTD is Jan1 to present and not a fiscal year:
 
{table.datefield} in dateadd("d",-(datepart("y",currentdate))+1,currentdate) to currentdate
//this gives you jan1 to present of this year
or
{table.datefield} in dateadd("y",-1,dateadd("d",-(datepart("y",currentdate))+1,currentdate)) to dateadd("y",-1,currentdate)
// this gives you jan1 of last year to one year ago today
IP IP Logged
thetonyleone
Newbie
Newbie


Joined: 21 May 2009
Online Status: Offline
Posts: 4
Quote thetonyleone Replybullet Posted: 21 May 2009 at 9:57am
I added the selection expert to pull data from 1/1/2008 till today. Where would I put the formulas to have say the YTD TY cases filed pull the items that fell in this years date time frame?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2009 at 10:04am

Do not use static items like "1-1-08" or your report will not work as teh year rolls over. Use the formula I gave you above, replacing the {table.field} with your actual data date field, place it in the select expert and it will handle both criteria (this FY and last):

{table.datefield} in dateadd("d",-(datepart("y",currentdate))+1,currentdate) to currentdate
or
{table.datefield} in dateadd("y",-1,dateadd("d",-(datepart("y",currentdate))+1,currentdate)) to dateadd("y",-1,currentdate)
IP IP Logged
thetonyleone
Newbie
Newbie


Joined: 21 May 2009
Online Status: Offline
Posts: 4
Quote thetonyleone Replybullet Posted: 21 May 2009 at 10:11am
oh ok did that but then how would I pull say QTD for this year for cases? and where would I put that formula. I think that is the one thing I dont understand since Im used to coding in php and mysql and crystal is a little different than that :) 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2009 at 11:55am

I think this is a much more complex question because your really asking about how to design your report. I can't really help much with that based on the sketch, it is far too vague and I do not know your data...

However you have a number of options to consider. Since you have all of your data you could:
1. create running totals that would give you #'s based on YTD, quarter or week. Or
2. you could create formulas that would identify if a record fell into any of your groups and use those to help design your report.
3. Or you can add the table 3 times and pull the data 3 different ways from the table and join them and use those too count.
If you choose this method you would need 3 record selection critieria and left join the tables together.
Once you decide on a model if you need more assistance let us know.
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 | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.