Hello,
I am working on Crystal reports and I cannot find a way to use the classic functions -sum() or max()- on formula fields I created (it only works with data field ), I get the following error message:
'This field cannot be summarized'
My formula field is the following:
WhilePrintingRecords; NumberVar compte; compte=0; if {JOB_MAIN.datetime_open} in {@dateStartsup} to {@dateEndsup} then compte:= compte + 1;
Basically it just counts the number of rows in a certain date range.
Then, I need to pass the total number to the main report, so I need to apply maximum() function to my formula field:
WhilePrintingRecords; Shared NumberVar maxvalue:=maximum({@compte}); maxvalue;
And then I get the error message 'this field cannot be summarized'. I cannot apply maximum() to {@compte}.
If anyone has an idea on how to fix this issue, please let me know!
Thanks!
Mounia
Hi Mounia,
What is your formula returning? For the Sum or Max functions to work, it has to return a number or date. It cannot be a running total or use the WhilePrintingRecords function.
If you send us the formula to look at, we can see why you can't summarize it.
Brian
Hi Brian
My formula field is the following:
WhilePrintingRecords; NumberVar compte; compte=0; if {JOB_MAIN.datetime_open} in {@dateStartsup} to {@dateEndsup} then compte:= compte + 1;
Basically, it just counts the number of rows in a certain date range.
Then, I need to pass the total number to the main report, so I need to apply maximum() function to my formula field:
WhilePrintingRecords; Shared NumberVar maxvalue:=maximum({@compte}); maxvalue;
And then I get the error message 'this field cannot be summarized'. I cannot apply maximum() to {@compte}.
If you know how to fix this, please let me know.
Thanks!
Mounia
Hi Mounia,
The problem is using trying to sum a formula using WhilePrintingRecords. Crystal does two passes over the database. The first pass is to read the records and the second is when printing the records.
Formulas are normally evaluated at the following times:
If no database or group field is included in the formula, the formula is evaluated before the program reads database records.
If a database is included in the formula, the formula is evaluated while the program reads database records.
If a group field, page # field, subtotal, and so on, is included in the formula, the formula is evaluated after database records are read and while the data from the records is being printed in the report.
In your case, the WhilePrintingRecords function is forcing the the report to evaluate the Maximum at the same time which it can't do.
If you have the first formula in the Details section and have your second formula in the Group footer or report Footer section, it will show the last value of your variable. Just change your second formula to:
WhilePrintingRecords; NumberVar compte; compte;
Then drop it in a section below the detail section.
Good luck,
Brian
Hi Brian,
Thank you, it works like this and I get the last value, which is what I need.
But, I also need to pass this last value to the main report and I cannot add 'Shared' to the new formula otherwise it does not work.
Do you know how I can pass this value to the main report since the usual way to do it (with 'Shared') is not working here?
Thanks again!
Mounia
Brian,
I just found how to do it!
Thanks
Mounia
Mounia
Please don't double post.
I did ask you to to give more info about your formula.
https://answers.sap.com/questions/205317/how-can-i-get-the-maximum-value-of-a-formula-field.html
If you had answered that instead of posting again you would have got the same answer a lot quicker!!
Ian
Ian
Sorry about that I've just seen your reply. I did not get any notification informing me that you replied otherwise I would have answered as you can imagine!
Thanks
Have a nice day
Mounia