Use a formula to suppress report data based on statement date

Is there a way to calculate the last date of the previous month using the date formula (@statementdate) to suppress report detail?  

Currently, I have to change this formula manually every month and would like for it to calculate automatically from the statement date. see attached.

Statements are printed on the first on the month for the previous month, i.e. Jan 1,2014 we'll print statements for December 2013, but I do not want to show discount information prior to  Nov 30,2013. (11/30/2013 is the date I need to calculate from the statement date 01/01/2014)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I might be wrong but this is the second time you are asking similar questions. You can handle this with a formula, as the previous time, but you may also check if your current product supports relative dates.

Check page 4 and 5 of this document to see what I mean: RelativeDates

If your application supports relative dates you will be able to create a parameter and use it to control the behavior of the report. Parameter values will be provided as default values by the calling application. Having parameters with default values will allow you to switch between the cases using templates. This is not exactly your current case but it is still related to a date related to today date. Previous month start, previous month end, current date etc. are all relative dates.

You can check this video to see hot relative dates and templates could be used:
Jump directly to 14:40.
Mike McCrackenSenior ConsultantCommented:
Try this to get the last day of 2 months ago


To use your statement date


Explanation of the formula

Last day of previous month - Subtract day value from the date.  Evaluates to Day 0 of the month which Crystal evaluates as the last day of the previous month


Subtract 1 month from that date

Since DateAdd returns a DateTime convert it to a date


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
decker587Author Commented:
@vasto,Yes, I did have a similar question the other day, but since it had an accepted solution I didn't want to reopen it.  Plus this issue had more to do with data suppress based on a date formula rather displaying a date formula.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I understand. However having a report, which MUST be printed on  a specific day in order to work correctly sooner or later will be a problem. In your case - I am sure that there is, or there will be, a case when you will need to print a statement for the current month. If the date is hardcoded in a formula you will need to change the report in order to print it as required. Instead you can provide the statement date as a parameter. You can also compare the current month with the parameter month and if they are equal to hide the discount information , so you will be able to print the statement if necessary without showing the discount.

It is up to you. I am trying to give you another point of view , which will allow you to handle the reports in general without dealing with them case by case.
decker587Author Commented:
@mlmcc,  I am getting an error that a date is required, see attached.

@vasto, Thanks for the links, but unfortunately the version of crystal we are using does not accept relative dates.

However you are correct, I will need the date to The statement date is the parameter I need to calculate in the suppression formula.  But, I may not have been clear and I gave you incorrect information.

Statements are run on the first of the month, but the statement date is the last day of the previous month, i.e.  January 2nd, we will run statements for December 2013, dated 12/31/2013.  So, the formula I need is to suppress dates prior to November 30, 2013 using the statement date, which is also a formula in the report.
It is up to you . There is no version of Crystal reports, which support relative dates. You need to use a 3rd party viewer. You can get a free license for the viewer in my link from this webpage:
Then you can run the reports using the viewer and avoid changing the report.

This error is the same as the error oin the previous question. Most likely the statement date is a string and you need to use DateValue({@statementdate}) to convert it to a date
Mike McCrackenSenior ConsultantCommented:
What is the formula for the statement date?

decker587Author Commented:
@mlmcc, the formula for the statement date is formula = "" .
Mike McCrackenSenior ConsultantCommented:
That is obviously NOT a date but a string.  You need to fix StatementDat eso it returns a date.

What, exactly, is the @statementdate formula that you referred to in your first post?

 The way that you used the name kind of implied that it was an existing formula that produced a "statement date", and you wanted to base your "last date of the previous month" calculation on that "statement date".

 But I think what you were really asking for was a formula that would calculate the last day of the previous month based on the current date, and you were going to call that formula @statementdate.

 If I'm correct, the first formula in mlmcc's first post would be your @statementdate formula.  I think that, like me, he thought that you wanted to base the date on an existing formula named @statementdate, so he replaced CurrentDate with that formula name in the second version of his formula.  (He also used statementdate in one place, instead of {@statementdate} )

 Now, having said all of that, I think there is a problem with mlmcc's formula.  It's based on the last day of the month, which changes.  For example, when run in December, it would get 11/30 as the last day of the previous month, and when it subtracted 1 month from that, it would get 10/30, instead of 10/31.

 I would base the formula on the first day of the month, and include dates >= that date (instead of just >).  This should give you the first day of the previous month.

Date (DateAdd ('m', -1, Date (Year (CurrentDate), Month (CurrentDate), 1)))

 If my guesses are right, you'd use that in your @statementdate formula (which would replace the Date (2013, 11, 30) in your first screenshot), and change the formula in your screenshot to use >=, instead of >.

 I also have a couple of questions about a line in one of your last posts:

 > So, the formula I need is to suppress dates prior to November 30, 2013
 > using the statement date, which is also a formula in the report.

 Suppression in a CR report means that you're using formatting options or group selection to "hide" a field, section or group.  The data is still included in the report (eg. in summaries).  It's just not visible.  I'm guessing that you don't mean that kind of "suppress".

 Did you really mean "_prior_ to November 30, 2013"?  That would mean that you want to include 11/30.  I'm guessing that you really meant "exclude dates on or before 11/30", but wanted to make sure.

decker587Author Commented:
We use Sage MAS 200.  All of the system reports are written in crystal.  I can customize system reports.  We want to customize our statements so that if a customer does not pay invoices from the previous month, the report does not print their discount on the current statement.  The statement date I am talking about is the "age invoices by" date from the ERP, fig 1, and is used as a formula string in the crystal report, fig 2.

Now is it possible to use @StatementDate in the formula to pull discount amounts for only the current billing cycle, fig 3?  Or do I need to go about this a different way?
I don't know anything about Sage, so I may not be of much help.

 Your 3rd screenshot indicates that the report has no parameters, so the "Age Invoices as Of" date in your first screenshot may not be available to the report, unless it's included with the other data (as a column in the tables or whatever the report is reading).

 If that date shows up on the report somewhere, then I guess it's being passed to the report somehow.  If the report does not have access to that date, then, obviously, it can't use that date to try to control the discounts, or anything else.

 Assuming that the report has access to that date, then the next question is, can you use that date to do what you're trying to do?

 If the report is producing a statement for a certain month, but you want to know if the invoices from the previous month(s) have been paid, does the report even have access to the information from the previous months?  As I mentioned earlier, based on one of your screenshots, the report doesn't have any parameters, so I'm guessing (just guessing) that Sage may be gathering the information, using the "Age Invoices as Of" date and the other controls in your first screenshot, and then passing it to the report somehow.  If that's the case, then the report itself may not have access to information, like payments, for previous months.  I really don't know.

 Hopefully someone familiar with Sage can give you some more definitive answers.  It might also help if you could post the report (RPT file), although if it's part of a proprietary package, maybe you can't.

decker587Author Commented:
Yes, there are no parameters setup in the select expert, however from the printing prompt, you can select a statement date, ranges of customer number, cycle types...and these 'parameters' are passed through to the report into formulas, i.e. @statementdate (formula="").
Ah.  I guess you have an app that runs the report, and it changes the formulas?  I've never run reports from code like that, but I think I read that you can change formulas like that.  So I'm guessing that the formula="" in the @statementdate formula is basically just a placeholder.

 The first thing you could try would be to change @statementdate to
formula = CurrentDate

 Then CR will see that the formula produces a date value, and it should let you use a function like Day on the formula.

 But you'd also have to try that with your app and see if the report still works, since the formula used to be an empty string, and now it's a date.  I don't know if code can change the type of value that's produced by a formula.  If not, then that formula may have to be a string, because the code is going to put a string there.

 Assuming that the report still runs normally after you change @statementdate to produce a date, then you could use a formula similar to the ones posted earlier.  As I said in my first post, I would base the calculation on the first day of the month, instead of the last (as mlmcc suggested), because the last day of the month changes from month to month.

 You could replace

> Date (2013, 11, 30)

 in your formula with

>= Date (DateAdd ('m', -1, Date (Year ({@statementdate}), Month ({@statementdate}), 1)))

decker587Author Commented:
I was able to use mlmcc's formula, convert the date value (vasto) and use the first of the month (James) to get the desired result.

Thanks for everyone's input.
I'm glad you were finally able to get it working.  I really didn't know what was going on there.  Passing values by changing formulas in code is very "sneaky".  :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.