year to date and last year to date in Crystal reports 1

[shummy1]

Hi there, I have a report set up to pull data for year to date and last year to date.
What I ned to do is restrict the YTD and LYTD to a certain month, that is chosen by the user as a parameter field.

This is what i am using so far
>>if{BALANCES.CURR_DATE} in YearToDate
then 1 else 0

How can I set the month??

 

[kskid]

Need a little more technical information for a more precise solution.

Crystal version
Database and connectivity
Sample date
Expected output

When you say certain month, is it parameter driven or based on currentdate?

If currentdate, is it as of today or as of last full month? If parameter driven, does it include the parameter date?

Assuming that it is parameter driven to include the ending date, then the record selection would include the following

(
{table.date} in [dateserial(year({?date}),1,1) to {?date}] or
{table.date} in [dateserial(year({?date}},1,1) to dateserial(year({?date})-1,Month({?date}),day({?date})]
)

Cheers,
-LW

 

[shummy1]

crystal version is 8.5
I can not tell what the database is I use an ODBC to access it and I beleive it is set up to work with sequel.

The curr date used in the formula is actually the day the contract was started.

I need to pull all the contracts that were started at Jan 1 2005 till February 28, 2005 even though I am running the report in March.
the other column on the same report is for all contract from Jan 1 , 2004 till February 28, 2004.

The month ie February in this case would be chosen through a perameter called {?month}


the output is something like this :
no date is seen on the report except the parameter month


----------------------------------------------------
New Contracts YTD and LYTD for month of February

2005 2004
Customer name Cost Count Cost Count

John Gray 2200 5 1900 3
Bill Happy 1800 2 1600 4

------------------------------------------------------
I hope this helps

 

[kskid]

If you always want through the last full month, then your record selection would include

(
(
{table.date} in YearToDate and
{table.date} < Minimum(MonthToDate)
) or
(
{table.date} in LastYearYTD and
{Table.date} < Minimum(LastYearMTD)
)
)


Create formulas for each of the columns

@YTDCost
if {table.date} in YearToDate and {table.date} < Minimum(MonthToDate) then
{table.Cost}
else
0

@LYTDCost
if {table.date} in LastYearYTD and {table.date} < Minimum(LastYearMTD) then
{table.Cost}
else
0

@YTDCount
if {table.date} in YearToDate and {table.date} < Minimum(MonthToDate) then
1
else
0

@LYTDCount
if {table.date} in LastYearYTD and {table.date} < Minimum(LastYearMTD) then
1
else
0

Place the above formulas in the detail sections

Insert a summary on the formulas

That should do it

-LW

 

[shummy1]

ok that seems to be ok except for one thing.
I need to set the month for anytime during the year.

The parameter could also be January. Does that get put in anywhere??

-s1

 

[kskid]

If that is the case, then if the user parameter is {?date}

(
{table.date} in [dateserial(year({?date}),
1,
1) to
dateserial(Year({?date)),
Month({?date})+1,
1-1)
) or
(
{table.date} in [dateserial(year({?date})-1,
1,
1) to
dateserial(Year({?date))-1,
Month({?date})+1,
1-1)
)
)

This allows the user to pick ANY date and ANY year and the formula will automatically select the proper timeframe for 2 years.

The above changes apply to the other formulas as well.

-LW

 

[shummy1]

The user is only selecting the month.Or could set it up to choose the end date.
What works in crystal is if I change the stprint date option to the last day of the month. But how do I get this to set in the formula for the column instead of having set the print date option.

If I set the print date function in crystal under report menu to the right set time date the report works fine.

Is there a way to set this in a formula?

Sorry I am not having any luck with the above as it requires a full date entry

 

[kskid]

Before I continue, how do you want to handle December? For exmaple, if today is January 2, 2005 and the user enters 12, do you want data from 2004 and 2003? What about November? From any date in 2005, how far back in the past do you want to go? Are you going to research something in April, 2004 and need 2003 data to go with it?

If so, the user will have to specify the year as well. Without specifying the year, the date will be missing from Database->Show SQL Query. That means you will be retrieving ALL the records.

Let me know what you want.

-LW

 

[shummy1]

I guess your right the user would need to enter the year as well. hmmm
I would need to see dec 2004 and 2003 year to date total even though I am running in Jan.

In this case I may just go with a date fields and use a "date" parameter.

I will wait for your response and then try out a few things before I bug you again.

thanks - s1

 

[kskid]

Good choice. Otherwise, the user will have to fill out 2 parameters instead of 1 so it saves mouse clicking there.

The way I currently have the formula, you can set any day of the month on the date field since the only parts the formula is working with is the month and year.

For example, today is 3/18/2005. When you are prompted with a date, just enter 1 (for December), hit the tab key twice to skip to the year field and enter 2004. Click OK and you are done. The date selection will be 1/1/2003 to 12/31/2004.

Another example, If today is 6/15/2005 and you want March data, then all you have to do is enter 3 on the month and click OK. The records selected will be between 1/1/2004 and 3/31/2004 and between 1/1/2005 and 3/31/2005.

Cheers,
-LW

 

[stoopers]

I have a similiar question....I am creating a new customer report. A new customer is defined as not having an order in the last 12 month period. So I was attempting to check each order date in the current month to see if another order was recorded in the last 12 month period. Any suggestions?

 

[kskid]

I use Crystal 8.5 via ODBC to Oracle 8i and I would add the following in Database->Show SQL Query

AND NOT EXISTS
(
SELECT * 
  FROM "SID"."CUSTOMER_ORDERS" CO
 WHERE CO."ORDER_DATE" BETWEEN ADD_MONTHS(SYSDATE,-12)
                           AND SYSDATE
   AND CO."CUSTOMER_ID" = CUSTOMER_ORDERS."CUSTOMER_ID"
)

There is the Add command with later versions but I am not sure about the structure.

Will know more when we get Crystal Reports XI Enterprise.

-LW

 

[stoopers]

We're using Crystal 7.0 to Scala 5.1...is there anyway to do it using the LYTD function????

 

[kskid]

Hmmmm.... Don't know the equivalent SQL syntax for Scala 5.1.

The LYTD function will only cover 1/1/2004 to 3/23/2004.

Another option is to create a subreport to check for orders in the past 12 months and pass a shared variable to the main report for conditional suppressing. However, this option comes with a performance hit along with other printing/spacing considerations associated with subreports.

-LW

 

[beanbrain]

According to Google searches Scala (not iScala) is powered by Microsoft SQL Server 2000. I would hazard to guess that SQL Server (2000) commands and functions would work with Scala.

 

[bdreed35]

Though not the ideal solution, you could pull all records back for the past 12 months to the current date and then suppress groups if they that have orders in the previous 12 months.

The record selection would be something like this:

{table.orderdate} in dateadd("m",-12,minimum(monthtodate)) to currentdate

Now you need to create a group on customer if you don't already have one.
Then enter the following as the group selection formula (Report menu, Selection Formulas, Group):

minimum({table.orderdate},{table.customer}) >= minimum(monthtodate)

This should only show customers that have orders in the current month only.

~Brian

 

[shummy1]

The serial date formula worked just fine.
I am finally on the path to finishing this thing. Thank you

-s1

 

[stoopers]

Thanks for the help! One more question - I also need to group by the product as well as customer and date....can I add an extra variable?