Conditional String Assignment in Crystal 11

[thatgirlinMS]

I am a pretty advanced report designer but this has me baffled and it's driving me CRAZY! I'm working on some data that uses a gabillion tables for everything. I have a report of all discharged patients during a time frame. I am grouping by patient number. I have a diagnosis table, a table of when the diag was assigned, and a diagnosis type table. The ending value I need to have is the LAST (max effective date) assigned primary diagnosis.

It would be great if I could make a couple of variables (effdate and diag) somewhere in the crystal report and then have a formula field that checks to see if the currently read date is greater than the variable-stored date....if so, store the new date and corresponding diag.

This formula would print at the group footer level and need to be reset for each patient.

I even began by making a running total of the max of the diag assign date (based on if the diag is primary and reset on group change). I then thought I could just check to see that the current detail diag assign date = max(running total date) and is primary, then that detail's diag code would be the one I need.

I am currently getting the results I need using subreports but they cause the report to run entirely too long and will not export in csv format.

Did I do a decent job of explaining what I need? Can anyone help me?

Thanks in advance!

 

[lbass]

It would held to see some sample data, along with {table.field} names. Here's a guess, based on your description:

//{@assign} to be placed in the detail section:
whileprintingrecords;
stringvar diag;
datevar maxdt;
if {diagtype.type} = "Primary" then(
if {diagdate.date} > maxdt then (
maxdt := {diagdate.date};
diag := {diag.diag}
));

//{@displ} to be placed in the group footer for patient:
whileprintingrecords;
stringvar diag;
datevar maxdt;
diag & ": " & maxdt

Or make the display formulas separate for each variable.

Use a reset formula in the patient group header:
whileprintingrecords;
stringvar diag;
datevar maxdt;
if not inrepeatedgroupheader then (
diag := "";
maxdt := date(0,0,0)
);

-LB

 

[kenhamady]

Another approach is to write these two formulas and put the second one in the Group Footer (or the Group Header, which is another advantage):

//{@Combined}
if {diagtype.type} = "Primary" then '1' else '0'
& Totext ({Eff.Date} , 'yyyyMMdd')
& {primary.diagnosis}

//{@Display}
Maximum ({@Combined} , {Pat.Num}) [ 10 to 30 ]

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[thatgirlinMS]

Thanks SO much for your help, folks.

kenhamady, if I'm reading correctly, it looks like you are concatenating the date and diag code and then getting the max of that string which is a great idea but I only need to display the diagnosis code that corresponds with the max effdate.

The whole problem with data examples is that (kid you not) there are literally about 25 tables that are linked to get the data I need. I guess I could give you a made up example of the troublesome data.

Axis1 (contains diag codes)
Diagnosis ICD9 Code
Description
Link Key

Clientaxis1
Effdat (Date assigned to patient)
Link Key

Axiscodetype
Code
Link Key

I am only pulling the clientaxis1 data with effective dates between the patient's admission and dishcarge date...this gives me the diagnosis for the current episode.

Usually, a patient is assigned a diag code on admission, anytime during the stay, and on discharge. These codes can be primary or secondary....I only want the primary and only the LAST assigned primary code.

lbass, let me break down what you are doing. I create a formula (assign) that contains the syntax below and put it in the detail section (My detail is actually suppressed won't matter, will it?)

------------------------------------------------------
whileprintingrecords;
stringvar diag;
datevar maxdt;

if {Axiscodetype.Code} = "Primary" then
if {Clientaxis1.Effdat} > maxdt then
maxdt := {Clientaxis1.Effdat}
diag := {Axis1.Diagnosis ICD9 Code};

------------------------------------------------------

What the above formula will do the first-time through is the maxdt is empty so it will fill the var with the detail date if the diag is a primary type and also fill the diag.

FORMULA 2 will actually deliver the diagnosis code to the group footer

whileprintingrecords;
stringvar diag;
datevar maxdt;
diag

And to reset the variables between patients I create a new formula with the syntax below. I guess the inrepeatedgroupheader is a reserved crystal syntax word?

whileprintingrecords;
stringvar diag;
datevar maxdt;
if not inrepeatedgroupheader then
diag := "";
maxdt := date(0,0,0);

 

[kenhamady]

>> but I only need to display the diagnosis code
>> that corresponds with the max effdate.

I am concatenating three things, but I take the first two of them back off in the last 'display' formula. So it should only display a diagnosis code, and it should be the one from the last date of a primary diagnosis.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

thatgirlinMS,

You removed the parens from all of my formulas--please put them back--they are necessary.

Did you try Ken's formula? It looks like it should work.

-LB

 

[thatgirlinMS]

I'm sorry I'm being thickheaded but I really want to wrap my mind around what ya'll are doing.

kenhamady, you've lost me on your formula. What is the 1 or 0 for?

Will the resulting string be 120100130379.8 if the code is a primary diag and 020100130279.8 if it's not?

Will I put the {@Combined} formula in the detail section?

//{@Combined}
if {diagtype.type} = "Primary" then '1' else '0'
& Totext ({Eff.Date} , 'yyyyMMdd')
& {primary.diagnosis}

Then the {@Display} goes in the group footer....It gets the max if the combined formula, based on the patient number group. What is the [ 10 to 30 ] for?

//{@Display}
Maximum ({@Combined} , {Pat.Num}) [ 10 to 30 ]

I think I can make it work with an additional formula...

//{@PriDiag}
mid({@Display},10,(len({@Display}) - 10))

This should spit out 379.8 - which is what I want IF I'm understanding the process...I'm still stumped on the [ 10 to 30 ]

 

[kenhamady]

>>Will the resulting string be 120100130379.8 if the
>> code is a primary diag and 020100130279.8 if it's not?
Yes, this makes sure the MAX will be a primary

>> Will I put the {@Combined} formula in the detail section?
It doesn't even have to be on the report layout, since there are no variables involved. You might want it there for troubleshooting.

>>I'm still stumped on the [ 10 to 30 ]
This subscripts out the diagnosis by skipping the first 9 characters of "combined. I wasn't sure if it was just the IDC/DSM code or the text so I was generous with the 30.

This blog post I wrote might help:

http://kenhamady.com/cru/archives/304

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[thatgirlinMS]

I get it now! Thanks so much for your help (both of you). I'll let ya'll know if I'm successful.