Formula for Select Expert 4

[jodie]

I am doing a report from a database which has fields:
Proposal No.
Version NO. etc

More than one proposal can have the same Proposal NO. ; but they have to have different version No. There is no limit of versions a proposal can have. The greatest version no. dentoes the final proposal.

Ex:
Proposal No. 6389 Version No. 1
Proposal No. 6389 Version No. 2
Proposal No. 6389 Version No. 3
[Version No. 3 is the final version]
I am doing a report which adds up the totals of the income we will be getting from these proposals.
My problem is this: I only need to include the latest or most recent proposal from the database and suppress all the rest. ex. for Proposal No. 6389 (above) i only need to
include version No. 3 in my report. I don`t know how to say
that the report is to make sure that incase of duplicate (or more than one record with the same Proposal NO. , it has to check the Version No. and then include only the record with the max. version no. (eg. the record with Version 3 in our case as it was the most recent version.)

Would appreciate any help. Thank you

 

[kenhamady]

1) Group by proposal#.
2) Create a summary of the version using operation "maximum"
3) In the Group Selection formula put:

{version} = Maximum ({version}, {Proposal#})

Make sure that you use a running total field to do any totals of these records. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Alternatively
1) Group by proposal#, then Version #. ascending order (the default)
then, just drag your fields for Proposal number and Version number from the detail section into the Version number group footer, and suppress the detail section.

Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[kenhamady]

That will work also to show the right version, but then doing totals that only include the last version will be harder. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Good point Ken. You would either have to do manual running totals, using the three formula technique.
Or switch the sort order from ascending to descending, and put the fields in the group header instead of the footer. Then you could use the running total wizard. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[jodie]

Thanks a lot for your replies. I used the way suggested by Ken and was able to display only the highest version # for a proposal. My report is sorted by Office Name, then Company Name (and now I added Proposal and Version). I need totals in the footers of the Groups: Company Name, Office Name and then the grand Total ofcourse. Currently, I am using the summary field and calculating the sum of 'Coverage Lives' and 'Monthly premium'(fields in my database) in the 3 groups mentioned above.
The problem is that even though all the version# are not displayed in the report, it is adding up data from all records (whether or not they are displayed) in the totals. I do see that Ken has suggested 'running total field' and Malcom 'manual running total using the three formula technique' to avoid this scenario. However, I do not know how to do a running total or the other solution suggested.

Please help. Thanks a lot.

Jodie

 

[kenhamady]

Go the Insert Menu and insert a running total field. Just fill out the top 3 boxes in the window (Name, Field to total, summary operation). Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[jodie]

Ken,

There is just one problem. I don't have "Running Total" in the Insert menu. Upon reading more, I found out that it is available only in Crystal versions 7 & 8 and I am using Version 5 or 6. Is there another way to do a Running Total? I would appreciate it you can give me the next easiest easy way bec. I have to do the running total for atleast 15 fields in the report.

I also have one more question. I applied the formula for the Version No. to the Group Selection formula (which occurs when it prints records and that is the reason why I am getting totals of records I don't need). so, I tried to use the same formula in the record selection formula editor, but it does not work. Why is this happening? Won't my problem be solved if there is a way to put in this formula in the Record Selection as it will not have the extra records in the report anymore. and since we are not using summary fields or group name fields, I would think it should be possible to do it in there, but it does not work.

Thanks
Jodie

 

[kenhamady]

At the time of record selection it doesn't know what the subtotals are, so it can't use them to evaluate the records.

You need to do old fashioned running totals, using formulas and variables. These are described in the FAQ on running totals in the General CR forum. See the section on the 3-formula technique. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[jodie]

OK, I AM SORRY, NOW THIS PROBLEM CHANGES TO A RUNNING TOTAL PROBLEM.

I used this formula to get running subtotals. [I followed the Help Section in Seagate Crystal Reports).First I declared a variable and created a formula “Reset Total” that resets the value of the variable to Zero. Then, I created a formula for running subtotal.
As I mentioned before, my report is sorted first by Office name(GH1), within that by Company Name(GH2).
So, I need subtotals for each company, each office and the Grand Total (for all companies and offices). The database already provides subtotals for the Companies so I do not need to do that. I just need subtotals for each office and then I need a Grand Total for all Offices and companies (including all companies ---there are some companies that are not assigned to any office and are just sorted by company name---these are displayed in the beginning of the report.))

These are the 2 methods and the problems I faced with each.

METHOD 1
I put the ‘ResetTotal Formula’ in GH1 because I want it to start a new subtotal for each office. And, I put the Running Total formula in GF1 bec. I want a subtotal for each office. However, when it runs, in GF1 it actually shows me the subtotal for only the last company in the office instead of adding all the companies of that Office.
Example:
Chicago Office has 3 companies with the following data
MARIANI 51
OAK GROVE 0
SHERMAN 148

THIS IS DISPLAYED
RUNNING TOTAL of chicago office: 148 [This is wrong bec. it should show 199]

METHOD 2

Then, I tried putting the ‘Running Total’ formula in GF2 (which is actually the footer for company). But, since the ResetTotal is in GH1 (it resets to 0 only after it has subtotaled completely for that office).

THIS IS DISPLAYED:
Chicago Office
MARIANI 51
OAK GROVE 51
SHERMAN 148

RUNNING TOTAL for chicago office: 199

This works perfect except for that I do not want the subtotals to display after each company (as the company subtotals are already displayed by a field in the database).
I only want the last line to be displayed(RUNNING TOTAL:199)

I hope I did not totally confuse you.

Thank you very much for your help.

Jodie

Thanks
Jodie

 

[kenhamady]

You need 3 formulas for the subtotal:

Reset - GH1 (Suppressed)
Running Total - Detail (Suppressed)
Display Total - GF1

You need another 2 formulas for the Grand total, using a different variable name:

Running Total - Detail (Suppressed)
Display Total - GF1

All formulas should start with the first line of:
WhilePrintingRecords; Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[jodie]

THANK YOU VERY MUCH. IT WORKS.