Solved

Crystal Reports 2008 Formula Question

Posted on 2011-02-16
6
1,184 Views
Last Modified: 2012-05-11
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")
0
Comment
Question by:Nathan Webb
6 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 34910799
You'll have to explain some more.  What does "it does not remove the rest of the data not needed" mean?  Give some examples.
0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 34910974
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)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34911004
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 42

Accepted Solution

by:
zephyr_hex (Megan) earned 250 total points
ID: 34911036
oops... also meant to say

if you are not using SQL Query / Command,
you could use a Report Selection formula that limits the records to only those in the last 3 months.

{RegisteredMembers.RegisteredTime} >= dateadd('m',-3,today())
0
 

Author Comment

by:Nathan Webb
ID: 34915767
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
0
 
LVL 12

Assisted Solution

by:JayConverse
JayConverse earned 250 total points
ID: 34915793
No, you add it to the Record Selection formula.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Store code inside oracle BLOB column 7 42
Crystal Reports - pulling data for fiscal year 2 47
Insert timestamp data field of max summary 8 39
Crystal Reports 13 5 45
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now