Returning Groups with Formula

[Garyjr123]

Below is a formula I created to evaluate each row in a table and bring back the correct row due to the criteria (but I was having trouble doing so):

if {schedlog.audittype_id} = 2 then date({schedlog.scheduled_datetime})
else
if (date({schedlog.scheduled_datetime}) <> date(previous({schedlog.scheduled_datetime}))and {schedlog.reschedreason_id} in 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22)
then date({schedlog.scheduled_datetime})

I was trying to get the report to return the correct row but I realize that I want to return the correct group with this formula. I tried to place this formula in Group Selection (instead of Row Selection)but I keep getting this message: This function can not be used b/c it must be evaluated later with "previous({schedlog.scheduled_datetime})" highlighted.

Is there a way to return the correct group based on the formula above?

Basically, I want the correct schedlog.schedlog_id (GH3 in report)to return if the schedlog.audittype_id = 2 or if schedlog.scheduled_datetime is different from the previous row where only the date is different (I don't care if the time changed) and the schedlog.reschedule_id is 1-22.

 

[Madawc]

Group functions have to be based on properties of an individual detail line. Using 'previous' in the formual prevents this.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Garyjr123]

Madawc,

How do I make this work then? The GH uses a unique schedlog_id to group on. I want to return the rows that falls into the criteria I specified above. If I can't use previous, I'm assuming next will not work. Basically, the rows returned need to be evaluated first. Ideas?

Gary

 

[infinitizon]

1. I'm trying to understand your situation, maybe if you make yourself clearer.
2. What version of CR are u using?
3. You may try to put a square bracket around the group
if {schedlog.audittype_id} = 2 then date({schedlog.scheduled_datetime})elseif (date({schedlog.scheduled_datetime}) <> date(previous({schedlog.scheduled_datetime}))and {schedlog.reschedreason_id} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22])then date({schedlog.scheduled_datetime})

 

[Garyjr123]

1) Ultimately, I want my report to evaluate each row and return the row dependent on the criteria I specified.
- I want to see new sched appts (schedlog.audittype_id = 2)
- I also want to see resched appts if the date changes not the time, again evaluating each row (date({schedlog.scheduled_datetime))I figured using next/previous would help and if it is a true resched appt then there will be a reschedreason (schedlog.reschedreason_id).

2) CR XI

3) Square Brackets where?

 

[MCuthill]

GaryJr,

Infinitizon is referring to the [] around the list of resched reason codes... I don't beleive this will change your issue.

Sadly, I only have one solution that comes to mind for what you are seeking - and it is a bit cumbersome. Okay... two solutions, but the first is that the DBA builds a view in the database that returns the data.

The "self-serve" solution is to build a report that can export a table-like structure to an excel file and then query the Excel file with another crystal report. if you keep a static placement of this export and archive historic copies to a separate location, your secondary report will not need to be reconnected to the new excel file each time generated. Essentially, I see this as a multi-pass solution - I do not beleive it can be accomplished in a single report.

Hope this helps!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Garyjr123]

IS there a way to suppress a GH if I have a formula within it that returns a blank result or doesn't fulfill it's criteria?

[@NextDataBaseRecord]:
if {schedlog.audittype_id} = 2 then date({schedlog.scheduled_datetime})
else
if date({schedlog.scheduled_datetime}) <> date(previous({schedlog.scheduled_datetime}))and {schedlog.reschedreason_id} IN [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22]
then date({schedlog.scheduled_datetime})

So,

GH1 pat.name_display (suppressed)
GH2 resunit.abbr (suppressed
GH3a [start_datetime] [name_display] [birthdate] [pat_acct_num] [@NextDataBaseRecord]
GH3b [Procedures.rpt] subreport
D suppressed
GF3 suppressed
GF2 suppressed
GF1 suppressed

Now,

If [@NextDataBaseRecord] formula is false then can we have the GH3a return either suppressed or not at all? If so, how is this done?

Gary

 

[MCuthill]

Gary,

Assuming this statement true

If [@NextDataBaseRecord] formula is false then can we have the GH3a return either suppressed or not at all? If so, how is this done?

I don't have Crystal in front of me at the moment, but you should just need to go into the Section Expert and enter the following formula into the Suppress x+2 field for Section GH3a:

{@NextDataBaseRecord}=FALSE

Might be {@NextDataBaseRecord}="" though. And assuming your field can be used for the suppression formula - it is worth a shot.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Garyjr123]

When I try to add this formula to the GH3a X+2 I get a message: A date is required here and 'false' is highlighted.

 

[MCuthill]

Garyjr123,

Can you add a clause to your formula to output a "catch" date instead of "False"? Something like Date(0,0,0) if it allows it. Then this date could be used in the suppression formula.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Garyjr123]

Thanks Mike for your help. I realized that I could convert the datetime field totext then use your formula to suppress the GH I didn't want to see.

totext({@ReadNextDatabaseRecord}) = " "

This seemed to woget the results I wanted.