Solved

Use a formula to suppress report data based on statement date

Posted on 2013-12-30
16
582 Views
Last Modified: 2014-01-08
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)
Discount-Amount-Formula.JPG
0
Comment
Question by:decker587
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 39746581
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:
http://www.r-tag.com/Pages/Preview_Demo.aspx
Jump directly to 14:40.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 150 total points
ID: 39746628
Try this to get the last day of 2 months ago

Date(DateAdd('m',-1,DateAdd('d',-Day(CurrentDate),CurrentDate)))

To use your statement date

Date(DateAdd('m',-1,DateAdd('d',-Day({@statementdate}),statementdate)))

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

    DateAdd('d',-Day({@statementdate}),statementdate)


Subtract 1 month from that date
   DateAdd('m',-1,DateAdd('d',-Day({@statementdate}),statementdate))


Since DateAdd returns a DateTime convert it to a date
   Date(DateAdd('m',-1,DateAdd('d',-Day({@statementdate}),statementdate)))

mlmcc
0
 

Author Comment

by:decker587
ID: 39746652
@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.
0
 
LVL 18

Expert Comment

by:vasto
ID: 39746722
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.
0
 

Author Comment

by:decker587
ID: 39748932
@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.
Crystal-Error-2.JPG
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 50 total points
ID: 39749113
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: http://www.r-tag.com/Pages/FreeCrystalReportsViewer.aspx
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39749875
What is the formula for the statement date?

mlmcc
0
 

Author Comment

by:decker587
ID: 39751594
@mlmcc, the formula for the statement date is formula = "" .
0
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

 
LVL 100

Expert Comment

by:mlmcc
ID: 39753082
That is obviously NOT a date but a string.  You need to fix StatementDat eso it returns a date.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39753470
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.

 James
0
 

Author Comment

by:decker587
ID: 39760658
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?
fig-1.JPG
fig-2.JPG
fig-3.JPG
0
 
LVL 34

Expert Comment

by:James0628
ID: 39761718
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.

 James
0
 

Author Comment

by:decker587
ID: 39763214
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="").
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
ID: 39765033
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)))


 James
0
 

Author Closing Comment

by:decker587
ID: 39766036
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39766178
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".  :-)

 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

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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

21 Experts available now in Live!

Get 1:1 Help Now