Formula ??

[pungigi]

I have the following formula,

if {_MTMTRP.TAPTDT} >= 20031001
then if {_MTMTRP.TAPTDT} <= 20031031
then if {_MTMTRP.TREASC}=&quot;08&quot;
then 1 else 0

I actually have 100 of the above formulas where {_MTMTRP.TREASC}= (different numbers 1-100). What I need is below.

1 = 12
2 = 18
3 = 0
4 = 5
5 = 7

What I want is if 0 is returned above I don't want that to show at all, how would I do this??

 

[synapsevampire]

Please post Crystal version and database when requesting technical inforamtion.

Your architecture has overkill, try posting example data and expected output rather than describing how other's must help you to accomplish soemthing.

You've added extra IF constructs, and were this formula required, it might read:

if {_MTMTRP.TAPTDT} >= 20031001
AND
{_MTMTRP.TAPTDT} <= 20031031
AND
{_MTMTRP.TREASC}=&quot;08&quot;
then
1
else
0

More importantly, I'm guessing that you don't need the date portions in your formulas at all, use the report->Edit selection formula->record:

{_MTMTRP.TAPTDT} >= 20031001
AND
{_MTMTRP.TAPTDT} <= 20031031

Now only those rows are returned.

Now group the report by the {_MTMTRP.TREASC} field (Insert->Group) and do a count aggregate (place a field in the details, right click it and select insert summary->count).

Drag the count field to the group header alongside the name

Suppress the details and the group footer.

Or you can easily use a cross-tab.

Again, until you're familiar with Crystal and prgramming, try posting technical information and someone will likely help you:

Crystal version:
Database used:
Example data:
Expected output:

-k

 

[pungigi]

Sorry,

Crystal 9.0
SQL Database

I do need the dates, because I have monthly & YTD formulas for each field, I would love to give you an example but it is HUGE, there are 31 fields and I summarize on 8 different ones BUT, most of the formulas are based on multiple criteria, I have been fighting this for two months now and at this point am willing to do just about anything. Please let me know if there is a way I can get you what you need to possibly help.

TIA

 

[synapsevampire]

Try posting example data and expected output.

It can be basic, you say that you have monthly and YTD summaries, these can be handled automatically as well.

Anyway, there's no easy way to explain it if you don't share specifics.

If the solution is relatively fast, I donate time here, otherwise I do this for a living and require contracts, etc.

I can be reached at crystalreports// at // hotmail.com, slight decoding required to prevent spamming.

-k

 

[campsys]

Without more details and if your willing to try anything...
try adding to Record Selection Formula(one for each formula):

and
(({@FormulaName}) <> 0)

This should filter any lines where formulas returns a zero

or

Surpress the Detail line using (({@FormulaName}) = 0)

Knowing this needs to be done for 100 formulas, not sure of implications, but worth a try.

 

[pungigi]

1/5/2004 JALIAH HICKS ARK40304037 NOT HOME ALONE TRANS 72114 PULASKI M 7 S1 C R 1 1 84
1/5/2004 ALVIN R LEIN ARK40304071 NOT HOME ALONE TRANS 72120 PULASKI M 12 S1 C R 1 1 84
1/5/2004 JALIAH HICKS ARK40304037 NOT HOME ALONE TRANS 72114 PULASKI M 7 X1 C R 1 1 84
1/5/2004 ALVIN R LEIN ARK40304071 NOT HOME ALONE TRANS 72120 PULASKI M 12 DX B R 1 1 84
1/5/2004 JALIAH HICKS ARK40304037 NOT HOME ALONE TRANS 72114 PULASKI M 7 W7 P R 1 1 84
1/5/2004 ALVIN R LEIN ARK40304071 NOT HOME ALONE TRANS 72120 PULASKI M 12 S1 S F 1 1 84
1/5/2004 JALIAH HICKS ARK40304037 NOT HOME ALONE TRANS 72114 PULASKI M 7 S1 A T 1 1 84
1/5/2004 ALVIN R LEIN ARK40304071 NOT HOME ALONE TRANS 72120 PULASKI M 12 S1 C F 1 1 84

Above is the detail headers as follows:
Date Name Trip # Vendor
Zip County RMU Mileage Trip Status Vehicle Type Trip Type Addl Pass Addl Pass w/Appt Trip Reason

I need a summary to be as follows:

1st section formulas
Trip Summary
1 WAY COUNT if &quot;Trip Type is T or F and if Trip Status is S* then 1 else 0&quot;
ROUND TRIP COUNT if &quot;Trip Type is R and if Trip Status is S* then 1 else 0&quot;
PHARMACY TRIP COUNT if &quot;Trip Type is P and if Trip Status is S* then 1 else 0&quot;
DENIED TRIPS if &quot;Trip Status is D* then 1 else 0&quot;
WAITING TRIPS if &quot;Trip Status is W* then 1 else 0&quot;

Vehicle Type
Cab if &quot;Vehicle type is C then 1 else 0&quot;
Paralift if &quot;Vehicle type is P then 1 else 0&quot;
Bus if &quot;Vehicle type is B then 1 else 0&quot;

Trip Reason
01-Alcohol if &quot;Trip Reason is 01 then 1 else 0&quot;
02-Allergy if &quot;Trip Reason is 01 then 1 else 0&quot;
03-Dental if &quot;Trip Reason is 01 then 1 else 0&quot;
04-Dialysis if &quot;Trip Reason is 01 then 1 else 0&quot;
05-Drug Abuse if &quot;Trip Reason is 01 then 1 else 0&quot;

OK, each formula above is prefaced with either
for month to date formula
if {_MTMTRP.TAPTDT} >= 20031001
then if {_MTMTRP.TAPTDT} <= 20031031
for year to date formula
if {_MTMTRP.TAPTDT} >= 20030101
then if {_MTMTRP.TAPTDT} <= 20031031

Summary should look like

Trip Summary Current Month Year to Date
Count % of Total Count % of Total
1 Way Count 78 12.68 649 11.74
Round Trip 324 52.68 2791 50.48
Pharmacy 10 1.63 103 1.86
Denied 7 1.14 66 1.19
Waiting 3 0.49 59 1.07

Vehicle Type

Cab 562 78.03 5220
Paralift 1 4
Bus
Stretcher
Ambulance 3 6

Trip Reason

01-Alcohol 12
02-Allergy 8
03-Dental
04-Dialysis 92
05-Drug Abuse 12

NOW, Where there is no number I do not want that field to show BUT, if there is an amount in the YTD column it should.

Right now this report has 200+ formulas they are duplicates so I can get the MTD & YTD coulmns. If ANYONE knows of an easier way to get what I need I would be forever GREATFUL.

If this is overkill I am sorry just trying to be clear.

 

[pungigi]

Does anyone know if there is an easier way to do this or do I continue creating the 2 formulas for each summary on my report???