Solved

Last 6 Months only

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
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, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

12 Experts available now in Live!

Get 1:1 Help Now