Solved

Last 6 Months only

Posted on 2014-01-24
4
479 Views
Last Modified: 2014-01-28
Hi

I have a report which shows month as a number and years, along with other data as below

Year          Month         Total
2013         2                  1200
2013         6                  1400 etc.

I now want the report to show only the last 6 months worth of data, this includes the current month, so if ran today would show months 7,8,9,10,11,12 and 1

I cannot seem to do this using month numbers.

Any help would be appreciated
0
Comment
Question by:halifaxman
  • 2
4 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39807012
You want this based on Current Date?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39808560
You say that you want the last 6 months, including the current month, but, technically, your example includes 7 months -- The current month, plus the previous 6 months.

 Does your data have Month and Year fields and no date field?  Just checking.  You said that your report shows the month and year, but we don't know where the report gets those values.

 James
0
 

Author Comment

by:halifaxman
ID: 39810059
Hi Sorry last 6 months plus current month (7 months in total)

Yes my data does not have have a date only month number and year, there is not date in the database I am reporting from

Thanks
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 39810583
I am assuming that there is no future data (with dates after today) that needs to be excluded from the report.

 Basically, you need to check the month and year together, not just the month.  There are various ways that you could do that.  My concern is that, depending on how you do it, the record selection test may or may not be passed to the server.  If not, then the server will have to send all of the records to the report, and then the report will do the filtering.  If you have a lot of data, that could be a real problem.

 I think there's a good chance that a test like the one below would be passed to the server, because it's simple and doesn't rely on anything like a db function to convert the month and year fields into a date.  Just enter that as your record selection formula (use your field names, of course).

(
{Month_field} >= Month (DateAdd ("m", -6, CurrentDate)) and
{Year_field} = Year (DateAdd ("m", -6, CurrentDate))
) or
{Year_field} > Year (DateAdd ("m", -6, CurrentDate))


 The first part picks up anything between 6 months ago and the end of that year.  The second part picks up anything in the year following that (eg. this month).  Since you (presumably) don't have any future data to exclude, you can assume that anything in a year greater than the year 6 months ago should be included.  FWIW, if you did need to exclude future data, you could add a check on the month to the second half, to make sure that it was <= the current month.

 Today, for example, CR would translate the formula above to

(
{Month_field} >= 7 and
{Year_field} = 2013
) or
{Year_field} > 2013


 James
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

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

930 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

11 Experts available now in Live!

Get 1:1 Help Now