Month to date Issue
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21912
Printed Date: 27 Apr 2024 at 7:32pm
Topic: Month to date Issue
Posted By: cheryla
Subject: Month to date Issue
Date Posted: 15 Mar 2016 at 9:19am
Hello-
I have a report that I need to pull in a recv date field. This field can only be in the date range of a month end field. How can I tell this field to do this?
For example:
recv date end of month
1-4-16 1-31-16
1-15-16 1-31-16
2-18-16 1-31-16
7-19-16 1-31-16
So I only want the first 2 dates. The others should group into their proper month. I am grouping on the end of month field. I hope this makes sense.
Thanks in advance!
|
Replies:
Posted By: DBlank
Date Posted: 16 Mar 2016 at 3:51am
I don't really follow your post...
When you group on a date field you can group it by a month. So grouping, by month, on the recv date field would accomplish what you want. If you have other examples where the recv date field is < then the 'end of month' field and yo need to still group on the 'end of month' you can use a formula to decide the process and group on it (by month)
if table.RecvDate < table.EndOfMonth then table.EndOfMonth else table.RecvDate
|
Posted By: cheryla
Date Posted: 17 Mar 2016 at 8:42am
Thank you for your reply. Let me try to clarify.
I have a field that is a batch date and will usually be the last day of the month. I am grouping on this field. The next field I have is a recv date. Currently my report is pulling everything that has been recvd for a particular po. So if something was recvd in oct 2015 (or any other month) it is showing. Ideally I only want to show the lines that were recvd during a particular batch date. So if my batch date is Jan 2016, I only want to show the po lines that were recvd in Jan 2016 not any other month. There could be multiple lines on a po or even multiple recvgs to a line. Each recpt does have it's own recv date.
I hope this makes more sense. Again thanks for the help!!
|
Posted By: DBlank
Date Posted: 17 Mar 2016 at 8:59am
if you just want to exclude them you can use a select statement using datediff with a month and setting that to 0.
Th same thing can be done to suppress
datediff('m', table.recvdate,table.batchdate)=0
|
Posted By: cheryla
Date Posted: 17 Mar 2016 at 9:28am
|
that works sort of. I put that formula into the section expert to suppress that info but instead it is giving me all the "false" info. Is there something I should do to make it only show me the "true" info?
|
Posted By: DBlank
Date Posted: 17 Mar 2016 at 10:10am
make it <> 0 for the suppression.
=0 for exclusion in the select expert.
Keep in mind that suppressed records are still part of the report (data set) and will be part of any summarized data unless explicitly excluded via a running total (or other method).
|
Posted By: cheryla
Date Posted: 17 Mar 2016 at 10:13am
|
Wonderful! tyvm for your help! much appreciated as always!
|
|