?
Solved

Last 6 Months only

Posted on 2014-01-24
4
Medium Priority
?
646 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 101

Expert Comment

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

mlmcc
0
 
LVL 35

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 35

Accepted Solution

by:
James0628 earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 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