Formula-Maximum value 1

[mnasuto]

Dear All,

I have to find record with the latest date in order to perform some calculation on this record.

Data:

Date Calloutcome
10/16/01 X
10/17/01 Y
10/18/01 Z

Any ideas?
Best,
Marta

 

[kenhamady]

You can use the Max function to find the highest date in a column, or you can sort them and the highest date will be at the top/bottom. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Thanks for replaying

My problem is more complex

In this same time I need to know CallOutcome for max date.
I have data:


Name Date CallOutcome

Smith 10/12/01 X
Smith 10/15/01 Y
Smith 10/16/01 Z

Brown 10/15/01 Y
Brown 10/21/01 X

West 10/21/01 X

I have to create report with Latest Calloutcome for customer
Desired result:

CallOutcome Count
Z 1
X 2


How to create such report?

Best,
Marta

 

[kenhamady]

What version of CR do you have?
How many possible Call Outcomes?

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,

I am using Crystal Reports 8.5 and I can have many possible Call Outcomes.
Is it possible to do this report using Crystal? If so how?

Best,
Marta

 

[kenhamady]

It is possible, but you will probably have to create a separate total (in advance) for each outcome.

Because you need CR to find the maximum you have to group by Name. That costs you the Grouping step in CR and you can no longerany grouping or totaling automatically by Outcome.

You will need a Group Selection Formula that says {Date} = Maximum({Date})

Then you will need a running total for each Outcome with an evaluate formula that says:

{Outcome} = "X"

Is there another field that will identify the Max record without looking at the rest of the group? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,

Thank you for your help.

I tray your sugestions.

1. Create report group by name,
2. In Group Selection Formula type: {Date}=Maximum({Date})
3. Create running totals with evaluate formula: {Outcome}="X"

Problem starts in Step 2.
As the result I get only Max value from All grups-Name:

example:
Data:
Smith 10/12/01 X
Smith 10/15/01 Y
Smith 10/16/01 Z

Brown 10/15/01 Y
Brown 10/21/01 X

West 10/20/01 X

Result:

Brown 10/21/01 X

Is it something I am doing wrong?

Best,
Marta

 

[kenhamady]

Sorry, I didn't give the complete formula.
IT would be:

{Date} = Maximum ( {Date} , {name} )

That extra field has to be the group field of your report. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,

Thank you for your help.
Step 2 is working excellent.
Step 3 I aplay in Report futer and it is working.

Thanks a lot for taking your time to help me.
You are THE BEST.

Love,
Marta

 

[mnasuto]

Dear Ken,

Sorry to bug you, but I still have some problems with this report.

My problem is that I can have many possible Calls Outcome-diferent sets for diferent programme id

For prog_Id=1
Data:

Smith 10/12/01 X
Smith 10/15/01 Y
Smith 10/16/01 Z

Brown 10/15/01 Y
Brown 10/21/01 X

West 10/20/01 X

For prog_Id=2

East 10/12/01 F

I have separate table for Outcome
tbl_CallOutcome:

Outcome_ID Prog_ID Outcome
1 1 X
2 1 Y
3 1 Z
4 2 F

My report is based on parameter Prog_ID.
So I can have diferent Outcomes set for diferent Prog_ID.
Any time I can add new programme with new CallOutcomes.
So in Step 3
{Outcome}="X"
{Outcome}="y"
{Outcome}="Z"

{Outcome}="F"

{Outcome}="...."
..........

possibility are endless.
To make this report works I will have to add always new outcomes to Step 3.

Do you know how to overcome this problem

Best,
Marta

 

[kenhamady]

Well, then it gets complicated. I think you are going to have to use subreports.

The main report will simply group ALL records by outcome, just to get a list of the possible outcomes, with details hidden. Then in each outcome Group footer you will run a subreport that is similar to the report you currently have, but that only has one running total. This will be just like the one I described, but it will have an evaluate condition of:

{Outcome} = "{?param}"

{?param} is a link back to the outcome in the group of the main report. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,

Thanks for help.
I can see, this will work.
But propably I am doing something wrong.As a result I have all 0.
A. I create one running total in subreport futer.
{Outcome}="{?param}"
In Subreport All sections are suppressed exept report futer with only one running Total present. Subreport is group by Name .Group Selection Formula : {Date}=Maximum({Date},{name})


B. I inserted subreport to main report in group futer next to group name. I suppress all sections exept group futer .Main report is group by CallOutcome

C. I linked subreport to main report:

- Field to link to: spCallOutcome;1.Outcome
- Subreport parameter field to use:
?Pm-sp_CallOutcome;1.Outcome
-Select data in subreport based on field: sp_CallOutcome;1.Outcome

Result:
Group name Running Total
X 0
Y 0
Z 0
F 0

Is linking done correctly?
Do I have to create parameter field ?param in subreport?

Best,
Marta

 

[kenhamady]

I think you have 2 different parameters, the one that CR created and the one that you created. You need to get rid of one and use the same parameter for both. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,

Thak you for replay

I did not create any parameter.
Both, report and subreport are base on this same store procedure.Store procedure is asking for four parameters before report is run. So with subreport I get 8 parameters before report is run, But if I do not type parameter value it takes all records base on store procedure.
I think that presents of these parameters shuld not efect on 0 values.
Everythig is running OK, but returns all 0 value.
Maybe is some problem with linking?
What is your opinion?

Best,
Marta

 

[kenhamady]

Stored procedures make this even more complex. I am afraid that this may be more complex than I can explain without looking at the report, which is a level of service I reserve for customers.

But the general idea is that you have to link your subreport parameters to the main report. You should not be prompted for the subreport parameters.

Good luck. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[mnasuto]

Dear Ken,
Thanks for help recived.
What are your charges for consultation?

My e-mails: mnasuto@quantumtm.com
mnasuto@hotmail.com

Best,
Marta

 

[kenhamady]

Marta,

You can contact me through my web site for more info. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/