How to use Crystal Formulas for data within a Group

[porsche2k7]

Hi All,

I have a sql code which extracts the hospital patient data. The records are grouped by SSN and ordered by timestamp. Here is the source data

SSN HospClass Activity

123 Inpatient Admission
123 Outpatient Discharge

234 Inpatient Admission
234 Outpatient Transfer
234 Outpatient Transfer
234 Inpatient Discharge

345 Inpatient Admission
345 Ambulatory Transfer
345 Outpatient Discharge

456 Inpatient Admission
456 Emergency Transfer
456 Outpatient Discharge

However from this data I need to be able to generate a error report based on the rule, that a patient with Inpatient Admission cannot be transferred to Ambulatory or Emergency. Hence for this data the report would look like below.

SSN HospClass Activity

345 Inpatient Admission
345 Ambulatory Transfer
345 Outpatient Discharge

456 Inpatient Admission
456 Emergency Transfer
456 Outpatient Discharge

If the first record has a HospClass of Inpatient then next records for the same SSN having Ambulatory or Emergency as the HospClass should be shown on the report.

Is there anyway to acheive this using Crystal report formulas. I am using CR XI. Thanks for your feedback in advance.

 

[porsche2k7]

Can anyone share any thougts on how Crystal formulas can be used in this scenario. Thanks,

 

[lbass]

Save the main report as a subreport with a new name and then reopen the main report and insert the subreport in the report header. In the subreport, add these formulas:

//{@reset} to be placed in the SSN group header:
whileprintingrecords;
shared numbervar i := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
shared stringvar array ssn;
shared numbervar i;

if not onfirstrecord and
{table.ssn} = previous({table.ssn}) and
{table.hospclass in ["Ambulatory","Emergency"] and
previous({table.hospclass}) = "Inpatient" then(
i := i + 1
);

//{@setssn} to be placed in the sub group footer:
whileprintingrecords;
shared stringvar array ssn;
shared numbervar i;
shared numbervar k;
shared numbervar j := distinctcount({table.ssn});

if i = 0 then (
k := k + 1;
if k in 1 to j then (
redim preserve ssn[j];
ssn[k] := {table.ssn}
));

While in the subreport, suppress all sections within the subreport (but the not the sub itself or the section in which it is located).

Then in the main report, go into the section expert->group header->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar array ssn;
{table.ssn} in ssn;

Then repeat for details and group footer--select the detail section and group footer and add the same formula in the suppression formula section for each.

This should suppress all groups except the ones where you find an ambulatory or emergency entry immediately following an inpatient entry.

-LB

 

[porsche2k7]

Thank you LB for the response. However I need the report for a little complicated scenario.

1. The patient SSN will have 2 (admission and discharge) or more records based on the treatment. So instead of checking for the first record we need to verify based on the previous record. For example first 3 records and inpatient and 4th record is Emergency would be an error and needs to be reported also.

2. For a given SSN if all the HospClass are same then it should not be reported. How can I include this to the formula.

3. Inpatient cant be transfered to Abmulatory or Emergency.

4. Outpatient cant be transfered to Emergency or Newborn

5. Newborn cant be transfered to Inpatient or Outpatient or Emergency or Abmulatory

6. Emergency cannot be transfered to Newborn

Can I include all the other conditions in the same formula {@accum}. Or would I need to create new formulas.

Thanks again for the timely help.
Best Wishes

 

[lbass]

1-The formula is not limited to checking the first record. It looks for any set of records where inpatient is followed by Ambulatory or Emergency.

2-If all the classes were the same, the ssn group would not appear anyway, since the criteria would not be met.

3-6- Change {@accum} to:

//{@accum} to be placed in the detail section:
whileprintingrecords;
shared stringvar array ssn;
shared numbervar i;

if not onfirstrecord and
{table.ssn} = previous({table.ssn}) and
(
(
{table.hospclass in ["Ambulatory","Emergency"] and
previous({table.hospclass}) = "Inpatient"
) or
(
{table.hospclass in ["Newborn","Emergency"] and
previous({table.hospclass}) = "Outpatient"
) or
(
{table.hospclass in ["Inpatient","Outpatient","Ambulatory","Emergency"] and
previous({table.hospclass}) = "Newborn"
) or
(
{table.hospclass in ["Newborn"] and
previous({table.hospclass}) = "Emergency"
)
) then
i := i + 1
;

-LB

 

[porsche2k7]

Hi LB,

I did everything which you mentioned. But however the report doesnot show the results which we wanted. Infact its showing the opposite. The report has same hospclass groups and valid transfers. Is there anything which we have to change.

Thanks

 

[porsche2k7]

LB you mentioned in your first response:

While in the subreport, suppress all sections within the subreport (but the not the sub itself or the section in which it is located).

I saved my report as two reports subrpt.rpt and mainrpt.rpt. Now I inserted the subrpt.rpt as a sub-report in the report header section of the main report and included all the formulae as you mentioned.

Now do you want me to supress all the section in sub-report except the report header in sub-report.Is that right? I guess I am missing something here...

 

[lbass]

You can suppress all sections within the subreport so that it does not show.

The logic is that if the records within an SSN group meet your criteria, accumulate a number. Then add those SSN's to an array if the accumulated value = 0 (no records meet the criteria). Then in the main report, suppress those SSN groups that are in the array. When I tested it, this worked.

If you want me to troubleshoot, please post the exact formulas you are using. Note that you could reverse what is displayed by changing one line in {@setssn}:

if i <> 0 then

-LB

 

[allenzacchy]

How was your day? pls i ve been trying to write a formular to sum the amount of a particular sales report. im new in the forum let me explain better.

i ve got

{PRICE}* {sum of Quantity of product}={Amount},
then i want to sum {Amount} to get the percentage contribution of a product.what i dont get is how to sum the {Amount} can you help me please.thanks for your anticipated co operation

 

[dgillz]

Allen,

Please start a new thread.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?