Using 2 date ranges to filter different columns.
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7162
Printed Date: 29 Apr 2024 at 12:26am
Topic: Using 2 date ranges to filter different columns.
Posted By: vinpa
Subject: Using 2 date ranges to filter different columns.
Date 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!
|
Replies:
Posted By: DBlank
Date 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.
|
Posted By: vinpa
Date 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:
{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.
 |
Posted By: vinpa
Date Posted: 28 Jul 2009 at 5:23am
|
Of course I changed "Table.Datefield" to "SHIPPER.SHIPPED_DATE", and still field is unknown. Thanks.
|
Posted By: vinpa
Date 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.
|
Posted By: DBlank
Date 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.
|
Posted By: vinpa
Date Posted: 28 Jul 2009 at 6:27am
|
Thanks so much for your help!
I am not married to any specific format
 , 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!
Please help! Two days trying to figure out this junk.
Thanks again, friend.
|
Posted By: DBlank
Date 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. |
Posted By: vinpa
Date 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.
|
Posted By: DBlank
Date 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 |
Posted By: vinpa
Date Posted: 28 Jul 2009 at 7:07am
| Yeah, I was trying to post an image but it seems this tool is non-functional. |
Posted By: vinpa
Date Posted: 28 Jul 2009 at 7:25am
|
Product Code Commodity Code Part Number Ship Quan. TotalSales Assembly 60 10 2207.09
Previous Year 12 2410.23
Assembly 61 20 7878.09
Previous Year 5 2207.23
Assembly 62 10 2207.09
Previous Year 12 2410.23
Where previous year (as of right now) is the same value as above it because it's just grabbing the same information, rather than the year before.
Hope this gives you a better idea. |
Posted By: DBlank
Date Posted: 28 Jul 2009 at 7:56am
|
So your rows data is already summed per part #? If I am understnading your data and if you want your end report to look like the above... Change your select statement in teh Select Expert to pull previous years data. ( {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
You may want to group your data (probably on Part #).
You can make a formula field to show either the Part # or the Text "Previous Year" as:
if year({SHIPPER.SHIPPED_DATE})=year(currentdate) then totext({table.partnumber},0,"") else "Previous Year" |
Posted By: vinpa
Date Posted: 28 Jul 2009 at 8:24am
|
That is pretty much what I am tring to accomplish.
However, I used:
but this only prints out the same data as current year.
Is there a way to subtract 1 year from this SELECT in order to display last year's data?
Yes, I used SUM in order to get the total number of a specific parts on the report. I also used @LINE CDN EQUIVALENT for the prices.
I simply need to get the second row to display the data from last year. This is so CRAZY!
 Thank you so much, any more help is GREATLY APPRECIATED! : )
|
Posted By: DBlank
Date Posted: 28 Jul 2009 at 8:35am
|
OK back to basics here to start. 1. You must first get the report to select the correct data rows either by filtering the data before it gets to crystal (via stored procedures, views, etc) or after it gets to crystal via the Select Expert. This is where I was suggesting to alter your select statement. If that is working properly...
Your rows of data are not already summarized as in your sample data. Therefore you will need to create groups to do this.
Group on Part Number
Group on Shipped Date in Descending order and set "for each year"
Now you can get your SUMS at Group LEvel 2 (shipped date) to compare yty per Part #.
You can play around with the formatiing and supressing items as needed.
Is any of this making sense?
|
Posted By: vinpa
Date Posted: 28 Jul 2009 at 9:45am
|
Okay...
So the SELECT Expert is essentially an SQL query that returns the data I wish to use in the report.
From there, I can create formula fields to manipulate the returned data in order to place values where I wish.
I am a little confused with the grouping and group levels. Also, why I cannot simply take the value a user enters at the opening prompt, for example, 12/31/2008, subtract 1 from the year value, and return the same data a year earlier.
Are the any free instructional tutorials or ebooks you can refer to me? Again, this seems like such a simple task, perhaps I am not explaining myself well enough.
A million thanks and appreciate any additional insight you can give me on this.
|
Posted By: DBlank
Date Posted: 28 Jul 2009 at 10:01am
|
Originally posted by vinpa
So the SELECT Expert is essentially an SQL query that returns the data I wish to use in the report.
Basicaly although it uses Crystal Syntax which is close to SQL. It should be written to return a TRUE or FALSE per row of data that is being called via the DB Expert.
Originally posted by vinpa From there, I can create formula fields to manipulate the returned data in order to place values where I wish.
Close, generally your formulas are evaluating a row of data although you can use variables and summaries adn place these on Headers and Footers.
Originally posted by vinpa I am a little confused with the grouping and group levels.
My experiance is this confuses new users the most however it also the crux of good design. It is essential to grasp it how it impacts the row level data.
Originally posted by vinpa Also, why I cannot simply take the value a user enters at the opening prompt, for example, 12/31/2008, subtract 1 from the year value, and return the same data a year earlier.
You can (that is the query I started the thread ansewer with) however you are just getting more rows of data from your source. YOu still have to design the report to compare it.
Originally posted by vinpa Are the any free instructional tutorials or ebooks you can refer to me?
Not that I am aware of. If you have to use crystal for more than this one design buy Brian's book from this site (or get the Ebook and learn on line). It is an excelent resource and you will learn what you need to step by step.
Originally posted by vinpa Again, this seems like such a simple task, perhaps I am not explaining myself well enough.
It can be an easy process but there the questions you are asking go to the base design of the report and it is very hard to do that via posts. IT is much easier to assist in tweaking an already designed report
 Originally posted by vinpa
A million thanks and appreciate any additional insight you can give me on this. Glad to help you enter the world of Crystal
|
Posted By: lockwelle
Date Posted: 29 Jul 2009 at 7:01am
|
I don't mean to butt in...
Crystal is not my first choice for record selection. If you can write a stored proc, you will find Crystal is much easier to use, as you can your DB to do the heavy lifting of gathering the data that you want to display.
In this case, taking an EndDate, I would create a temp table with the itemnumber, thisYear, lastYear...keeping it simple for the example.
I would do something like:
select partNumber, sum(sold) as thisYear, 0 as lastYear into #compare from tableA group by partNumber where soldDate between datediff(year, -1, @dateEnd) AND @dateEnd Then
Update #compare
set lastYear = ss.sold
from #compare as c
join (select partNumber, sum(sold) from tableA group by partNumber where soldDate between dateadd(year, -2, @endDate) and dateadd(year, -1, @endDate)) as ss On ss.partNumber = c.partNumber
select * from #compare
What your goal is, for Crystal/any reporting tool, is to get all the data that you need to display in one row of data.
In a stored proc/view, you can easily accomplish this. Joining straight to tables in Crystal, is possible, but so much more difficult, and it might not be possible as you would want to join on different date ranges.
If you are advanced at SQL, leverage that skill and the database software to gather what you need, rather than coercing Crystal to do it. Let SQL do the data retrieval and Crystal the data display....play to each parts strengths.
Hope this helps.
|
Posted By: lockwelle
Date Posted: 29 Jul 2009 at 7:02am
| As a last note, the only book that I can recommend, in good conscience, is Brian's. Whenever I have needed something, that is where I have found it. Other than that, it is pretty hit and miss. |