Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Using 2 date ranges to filter different columns. Post Reply Post New Topic
Page  of 2 Next >>
Author Message
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Topic: Using 2 date ranges to filter different columns.
    Posted: 27 Jul 2009 at 1:10pm
Here is my issue:
 
I have a report that, when run, prompts for a date range (min/max) and sums the required data.
 
What I wish to do is subtract 1 year from that date in order to populate two separate columns (# products sold and total sales $) which would allow me to see sales from the same time frame a year earlier and compare the values.
 
I am new to Crystal Reports, but have above average SQL knowledge.
 
Using Crystal Reports 2008 (Version 12.0.0.683).
 
 
Thanks in advance!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Jul 2009 at 1:37pm
gathering the data is easy, comparing side by side not as easy.
in your select statement just add an OR statement using Dateadd to get your data
({Table.datefield} in {?Start Date} to {?End Date})
or
({Table.datefield} in dateadd("y",-1,{?Start Date}) to dateadd("y",-1,{?End Date}) )
Not sure how you want to present it but this should get you started.
IP IP Logged
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Posted: 28 Jul 2009 at 5:19am
I'm sorry, which SELECT statement may you be referring to?
 
I currently have a report with "Commodity Code", "Part Number", "Part Description", "Ship Quantity", "Prior Year Ship Quantity", "Total Sales $", and "Prior Year Sales $".  Of course, it's the "Prior Year..." columns that I would like to manipulate in order to get the previous year's stats.
 
The Select Expert shows:
 
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{SHIPPER.SHIPPED_DATE} = {?Shipped Date} and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
 
Not sure where to add your code, but when added to this I get a "This field name is not known."
 
Thanks for the quick reply, but I require a bit more mentoring to finish this report.  Smile
IP IP Logged
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Posted: 28 Jul 2009 at 5:23am

Of course I changed "Table.Datefield" to "SHIPPER.SHIPPED_DATE", and still field is unknown.  Confused

 
Thanks.
IP IP Logged
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Posted: 28 Jul 2009 at 5:51am
Okay, I got that bit of code to work, so I now have:
 
{SHIPPER.SHIPPED_DATE} in Minimum ({?Shipped Date}) to Maximum ({?Shipped Date}) or
{SHIPPER.SHIPPED_DATE} in (DateAdd("y",-1,Minimum ({?Shipped Date}))) to (DateAdd("y",-1,Maximum ({?Shipped Date}))) and
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
 
This is giving me boolean values, all of which are "true".  Still quite confused!
 
I wouldn't think this to be a difficult task, as comparing data year to year on a single report makes a lot of sense, sales-wise.
 
Is there no simple way to do this?  Any help is greatly appreciated!
 
 
Thanks folks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jul 2009 at 6:14am
Sorry for the confusion. I was giving you a way to limit the data being brought into thr report via the select expert (select statement) so that you would only grab 2 comparible date ranges of data and exclude everything else. It sounds like you are placing this formula in a formula field which will return a True or a False. Also note you need to add parenthesis around the OR statement at the beginning of the statement.
( {SHIPPER.SHIPPED_DATE} in Minimum ({?Shipped Date}) to Maximum ({?Shipped Date}) or
{SHIPPER.SHIPPED_DATE} in (DateAdd("y",-1,Minimum ({?Shipped Date}))) to (DateAdd("y",-1,Maximum ({?Shipped Date}))) ) and
not ({PART.PRODUCT_CODE} in ["Advertising", "Misc", "Packaging", "Repairs"]) and
{CUSTOMER_ORDER.STATUS} = "C" and
{SHIPPER_LINE.USER_SHIPPED_QTY} <> 0.00
 
Your description of trying to get the previous years data on the same row as the current year is a little different. Crsytal is designed primarily to analyze row level data, not converge rows of data into one. You have to handle that outside of crystal and let crystal do what it is good at. If you are not married to analysis in a column fomrat, rethink your design and use the row level grouping power of crystal to compare items that way...
or you may be able to use a crosstab or chart to show the data in a column process analysis.
IP IP Logged
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Posted: 28 Jul 2009 at 6:27am
Thanks so much for your help!
 
I am not married to any specific format Tongue, I just wanted to be able to compare/contrast our records from year to year, on a single report for easy reading.   Is this not possible then?
 
Is there a way to get 2 date range prompts; one that will populate certain columns, and another to populate columns that represent previous year's data?
 
This seems like an all-to-easy task that is made into an almost impossible one.
 
If not, what is the best route to take?  My manager would appreciate this very much!  Smile
 
Please help!  Two days trying to figure out this junk.
 
 
Thanks again, friend.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jul 2009 at 6:45am

Sorry, you cannot use two date ranges to populate side by side columns. Unless you create a View or stored procedure to create a table that has this data in it.

You can feed to sets of dates to get Row by row data (that was my first post process). Crystal does everything in rows of data. The columns that appear are only columns in that row.
Try using a CrossTab to get what you want. I am totally guessing here as I have no idea what your row level data is and what you need to get results on so please keep in mind that I am just trying to give you a process that might work for you if you adjust it based on your data source and needed end product.
You will need to create 2 formulas to group Month and Year side by side.
Create a formula as called "Year":
totext({SHIPPER.SHIPPED_DATE},"yyyy")
Create a formula as called "MOnth":
MOnthname(month({SHIPPER.SHIPPED_DATE}))
Add a crosstab and In the Crosstab Expert add Columns of @MONTH and then @YEAR.
This will group all your data for month year side by side.
As for the rows and sumarized fields I do not know what you need so I am not sure what else to tell you.
Try playing around with this process to see if it will work for you.
IP IP Logged
vinpa
Newbie
Newbie
Avatar

Joined: 27 Jul 2009
Location: Canada
Online Status: Offline
Posts: 10
Quote vinpa Replybullet Posted: 28 Jul 2009 at 6:54am
Okay, I will re-read that post a few times to get the idea.  Again, thanks for the quick reply.
 
What other information could I provide for more explanation?
 
 
Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jul 2009 at 6:57am
Post sample row level data as it looks when imported into crystal (you can dummy the numbers if it is sensitive info) and then what you want the end result report to look like
IP IP Logged
Page  of 2 Next >>
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.017 seconds.