Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Month to date Issue Post Reply Post New Topic
Author Message
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Topic: Month to date Issue
    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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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

Edited by DBlank - 16 Mar 2016 at 3:52am
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet 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!!

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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).
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 17 Mar 2016 at 10:13am
Wonderful! tyvm for your help! much appreciated as always!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.