Link to home
Create AccountLog in
Avatar of Nathan
NathanFlag for United States of America

asked on

Crystal Reports 2008 Formula Question

I have a formula that deperates data by the previous 3 months. It works great except iit does not remove the rest of the data not needed. Can some one tell me what I need to add to it so it disregaursds the rest of the data I do not need.

if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate),1) to currentdate then totext(dateserial(year(currentdate),month(currentdate),1),"MMMM yyyy")else
if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate)-1,1) to dateserial(year(currentdate),month(currentdate),1)-1 then totext(dateserial(year(currentdate),month(currentdate)-1,1),"MMMM yyyy")else
if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate)-2,1) to dateserial(year(currentdate),month(currentdate)-1,1)-1 then totext(dateserial(year(currentdate),month(currentdate)-2,1),"MMMM yyyy")
Avatar of JayConverse
JayConverse
Flag of United States of America image

You'll have to explain some more.  What does "it does not remove the rest of the data not needed" mean?  Give some examples.
if you're using a SQL query to pull your data into the report, you could limit data to only those records that are within the last 3 months by using date criteria in the WHERE clause.

example

....
WHERE RegisteredMembers.RegisteredTime >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 3, 0)
Avatar of Mike McCracken
Mike McCracken

The formula you are using is simply to use in the report.  You aren't using it to filter the data so you need a filter as suggested above.

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Nathan

ASKER

So would I add the string to the formula I posted above?

if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate),1) to currentdate then totext(dateserial(year(currentdate),month(currentdate),1),"MMMM yyyy")else
if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate)-1,1) to dateserial(year(currentdate),month(currentdate),1)-1 then totext(dateserial(year(currentdate),month(currentdate)-1,1),"MMMM yyyy")else
if {RegisteredMembers.RegisteredTime} in dateserial(year(currentdate),month(currentdate)-2,1) to dateserial(year(currentdate),month(currentdate)-1,1)-1 then totext(dateserial(year(currentdate),month(currentdate)-2,1),"MMMM yyyy") else
if {RegisteredMembers.RegisteredTime} >= dateadd('m',-3,today())


Thansk
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account