Solved

Last 6 Months only

Posted on 2014-01-24
4
497 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal - variable evaluate after with criteria 4 54
Crystal Reports - group header 2 47
Date is within the current week in Crystal Reports 2 62
Crystal Reports 13 5 61
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 …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

823 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