Displaying Different Data by Group

[former_member959984]

I am using Crystal Reports 11 on a Windows 7 64 bit operating system

We have a Clinical Application order system that creates order_id for the order and detail_id for each order within the it. (there are two tables, the first is the parent order table with the order_id and details of the order such as who entered it and when). This table is linked to the order details table via patient_id and order_id, in this table you will find the actual ordered detail within the order. Patient ID not shown in the example below.

order_id..........detail_id.......order

100000010.....10000001.....CT Scan

100000010.....10000002.....MRI

100000011.....10000001.....Sodium, calcium, potassium

100000011.....10000002.....CBC

100000011.....10000003.....CA125

We created a report that has two groups

Group 1 = order_id

Group 2 = detail_id

What we have done is we place an image of a requisition in the background of Group 2 and then place the content of the order on the requisition. So the CT Scan would go on it's own requisition and the MRI would go on it's own requisition. But in the case of order_id 100000011 above I would like all the detail_id from that order to go on one requisition. So I tried to place this requisition and order content in Group 1 and have all the detail appear together. Unfortunately I only get the content of detail_id 10000001 appearing, the other items do not show. I tried left outer joins from the order table to the order detail table but this doesn't make a difference.

If I change Group 1 to order (data field) then I get the items coming out on 3 different requisitions, almost right but I want it all on one.

I am looking for guidance on how I can accomplish this.

[former_member959984]

Here are screen images to support the issue

[former_member959984]

Here are screen images to support the issue

[DellSC · Accepted solution]

If you have the details image in the details section of the report, that's what's going to happen. I would suggest putting the image in a group footer (possibly for category??? You know your data better than I do, so you'll have to figure that out.) I would then calculate where the X's should go as the report is processing through the details. I'm going to assume that you have formulas that place the "X" in each box based on the current record. Instead, you'll need a variable for each possible X. It would work like this:

1. Create a formula where you'll initialize the variables. This will be a single formula that will set each of the variables to a blank string when you place it in the Order ID group header - this way you start with blanks for each order. It might look something like this:

{@InitVars}
WhilePrintingRecords;
StringVar na := '';
Stringvar k := '';
StringVar cl := '';
StringVar cbc := '';
...
''

The final empty string will ensure that the formula doesn't show anything.

2. Re-purpose your existing formulas for the X's so that they set a variable instead of returning a value. They might look like this:

{@HasNA}
WhilePrintingRecords;
StringVar na;
<if statement that indicates the order is asking for the sodium count> then
  na := 'X';
''

You'll do one of these for each X. Place them all in a single details section and then suppress the section. The formulas will still run if the section is suppressed.

3. Create a formula to display each of the variables in the right place. It will look like this:

{@ShowNA}
WhilePrintingRecords;
StringVar na

This will gather up the X's for all of the order details so they should show up on the report.

-Dell

[former_member959984 · Accepted solution]

Thank you Dell, I will attempt what you have explained. I will post my code here so you can validate that I am writing it as you expressed.

WhilePrintingRecords;
StringVar cbc := 'CBC: CBC, differential, retic count';
StringVar elec := 'Electrolytes: Na, K, Cl, CO2 (bicarbonate)';
StringVar exten := 'Extended electrolytes: Ca, Mg, phosphate, albumin';
StringVar renal := 'Renal Function: urea, creatinine';
StringVar para := 'Parathyroid Tests: PTH, CA, phosphate, albumin';
StringVar thyr := 'Thyroid Tests: T3, T4, TSH';
StringVar liv := 'Liver Function Tests: AST, ALT, GGT, alkaline phosphatase, LD, glucose, total bilirubin, direct bilirubin';
StringVar bio7 := 'Biochemistry 7: Na, K, Cl, CO2 (bicarbonate), glucose, urea, creatinine';
StringVar bio := 'Biochemistry: Na, K, Cl, CO2, Ca, Mg, phosphate, albumin, AST, ALT, GGT, alkaline phosphatase, LD, glucose, total bilirubin, direct bilirubin, urea, creatinine';
StringVar bio20 := 'Biochemistry 20: Na, K, Cl, CO2, Ca, Mg, phosphate, albumin, AST, ALT, GGT, alkaline phosphatase, LD, glucose, total bilirubin, direct bilirubin, urea, creatinine, total protein, uric acid';
StringVar inr := 'INR';
StringVar coag := 'Coagulation: INR, clauss fibrinogen';
StringVar hemo := 'hemolysis: total/direct bilirubin, LD, haptoglobin, direct antiglobin';
StringVar na := 'sodium';
StringVar k := 'potassium';
StringVar cl := 'chloride';
StringVar co2 := 'total CO2 (bicarbonate)';
StringVar g := 'glucose';
StringVar u := 'urea';
StringVar cr := 'creatinine';
StringVar ca := 'calcium';
StringVar p := 'phosphate';
StringVar mg := 'magnesium';
StringVar ck := 'CK';
StringVar htnt := 'troponin (high sensitivity)';
StringVar smyo := 'myoglobin';
StringVar tp := 'total protein';
StringVar al := 'albumin';
StringVar lipp:= 'lipid panel';
StringVar ch:= 'cholesterol';
StringVar tg:= 'triglyceride';
StringVar alk:= 'alkaline phosphatase';
StringVar alt:= 'ALT (SGPT)';
StringVar ast:= 'AST (SGOT)';
StringVar bcr:= 'BCR/abl';   //this value not on form
StringVar tb:= 'bilirubin, total';
StringVar db1:= 'bilirubin, direct';
StringVar epo:= 'erythropoietin';   //this value not on form
StringVar ggt:= 'y-glutamyl transferase GGT';
StringVar ld:= 'LD';
StringVar lip:= 'lipase';
StringVar ua:= 'uric acid';
StringVar iron:= 'iron';
StringVar tibc:= 'TIBC';
StringVar fer:= 'ferritin';
StringVar os:= 'osmolality';
StringVar afp:= 'alpha-fetoprotein';
StringVar bzmg:= 'beta-2 microglobulin';
StringVar ca12:= 'CA125';
StringVar ca15:= 'CA 15-3';
StringVar ca19:= 'CA 19-9';
StringVar cea:= 'carcinoembryonic antigen CEA';
StringVar psa:= 'PSA';
StringVar amm:= 'ammonia';
StringVar ace:= 'angiotensin converting enzyme';
StringVar bhb:= 'beta-hydroxybutyrate';
StringVar ceru:= 'ceruloplasmin';
StringVar eto:= 'ethanol';
StringVar fep:= 'FEP';
StringVar fish:= 'FISH';   //this value not on form
StringVar gyhb:= 'glycated hemoglobin';
StringVar hpt:= 'haptoglobin';
StringVar hcq:= 'homocysteine';
StringVar ige:= 'IgE';
StringVar ica:= 'ionized calcium';
StringVar lac:= 'lactic acid';
StringVar pb:= 'lead';
StringVar jak:= 'JAK-2';   //this value not on form
StringVar palb:= 'prealbumin';
StringVar pth:= 'PTH';
StringVar tcell:= 'T cell gene rearrangement'; //this value not on form
StringVar b12:= 'vitamin B12';
StringVar zn:= 'zinc';
StringVar tm:= 'tumor markers'; //this value not on form
//StringVar := 'endocrine tests';   //this value not on form
StringVar chro:= 'chromagramin A';   //this value not on form
StringVar rcf:= 'red cell folate';   //this value not on form
StringVar thy:= 'thyroglobulin';   //this value not on form
StringVar athy:= 'anti thyroglobulin';   //this value not on form
StringVar bnp:= 'brain naturetic peptide';   //this value not on form
StringVar acth:= 'ACTH';
StringVar ansd:= 'androstenedione (ANSD)';   //this value not on form
StringVar cor:= 'cortisol';
StringVar cnin:= 'calcitonin';   //this value not on form
StringVar dhas:= 'DHAS';
StringVar et3:= 'endocrine tests: LH, FSH, estradiol';
StringVar e2:= 'estradiol';
StringVar fsh:= 'FSH';
StringVar gh:= 'growth hormone';
StringVar hcgq:= 'HCG (quantitative)';
StringVar inh:= 'inhibin';   //this value not on form
StringVar pr17:= '17-hydroxyprogesterone';
StringVar ins:= 'insulin';
StringVar lh:= 'LH';
StringVar pgn:= 'progesterone';
StringVar pl:= 'prolactin';
StringVar shbg:= 'SHBG';
StringVar tst:= 'testosterone';
StringVar fai:= 'FAI';
StringVar ft3:= 'T3, free';
StringVar ft4:= 'T4, free';
StringVar tsh:= 'TSH';
StringVar tpo:= 'thyroperoxidase antibodies';
StringVar cbc5:= 'complete blood count (includes 5 cell differential)';
StringVar slr:= 'blood film review (reason must be given)';
StringVar reta:= 'reticulocyte count';
StringVar rh:= 'reticulocyte hemoglobin';
StringVar ipf:= 'immature platelet fraction';
StringVar esr:= 'sedimentation rate (ESR)';
StringVar hss:= 'sickle cell screen';
StringVar mal:= 'malaria';
StringVar gpd:= 'glucose-6-phosphate dehydrogenase';
StringVar aptt:= 'APTT';
StringVar cfib:= 'fibrinogen';
StringVar ddim:= 'D-dimer';
StringVar basd:= 'basic DIC screen (includes PT/INR, APTT, Fibrinogen, D-dimer and CBC)';
StringVar lups:= 'lupus inhibitor';
StringVar fvl:= 'factor V leiden & prothrombin variation (G20210A)';   //this value not on form
StringVar hbs:= 'heinz body screen';   //this value not on form
StringVar actm:= 'acetaminophen';
StringVar amio:= 'amiodarone';
StringVar carb:= 'carbamazepine';
StringVar cy:= 'cyclosporin';
StringVar dig:= 'digoxin';
StringVar gent:= 'gentamicin';
StringVar li:= 'lithium';
StringVar mtx:= 'methotrexate';
StringVar mypa:= 'mycophenolic acid';
StringVar phen:= 'phenobarbital';
StringVar pyn:= 'phenytoin';
StringVar sal:= 'salicylate';
StringVar siro:= 'sirolimus';
StringVar fk5:= 'tacrolimus (fk506)';
StringVar teo:= 'theophylline';
StringVar tobr:= 'tobramycin';
StringVar valp:= 'valproic acid';
StringVar vanc:= 'vancomycin';
''
<br>

[former_member959984 · Accepted solution]

This is the formula that I place in the details section

WhilePrintingRecords;
StringVar cbc;
if {vrbl_order_detail.po_desc} = 'CBC: CBC, differential, retic count'
then
  cbc := 'X';
''

This is the final formula that I place where I want the x to appear.

WhilePrintingRecords;
StringVar cbc

It is working but I must have made a mistake. It is displaying the actual test name rather than an X

[former_member959984]

I have tried suppression formulas to accomplish the expected outcome detailed above without success. Based on the way the rows are being read in combined orders I can make the biochemistry pages appear but then on pages where the orders are not combined I am getting blank biochemistry pages.

My workaround has been to place the biochemistry in a subreport that reads a stored procedure that pulls just the biochemistry data. The subreport is then placed in

group Header#2 {vrbl_order_detail.vrbl_order_detail_id}

This has corrected the suppression issue but based on how many items exist in one of these catagories {vrbl_order_detail.po_cat} in ["Biochemistry","Drug Levels","Endocrine Tests","Hematopathology"] it is printing that many pages.

Is this example I am getting 3 pages for biochemistry.

Is there a way I can suppress pages if a page count of any items in these catagories

{vrbl_order_detail.po_cat} in ["Biochemistry","Drug Levels","Endocrine Tests","Hematopathology"]

exceeds 1 page?

[former_member959984]

As for your question am I trying to suppress everything in Group 1 that is not in those four categories. The answer to this is no that is wrong.

When I started this project it was quite straight forward. When a Physician created an order it could contain 1 or more items that needed to be placed on it's own requisition. So what I did was created the report with two groups.

Group 1 = order_id (an single order could contain many individual orders CT,MRI,PET Scan.....)

Group 2 = vrbl_order_detail_id (requisitions are added to the group header based on the order type CT Requisition, MRI Requisistion, PET Requisition....Each new requisition is added to a new group header for {vrbl_order_detail.vrbl_order_detail_id}).

I would suppress each group header {vrbl_order_detail.vrbl_order_detail_id} (the requisition) unless it was part of the order.

{vrbl_order_detail.po_desc} <> "CT"

{vrbl_order_detail.po_desc} <> "MRI"

{vrbl_order_detail.po_desc} <> "PET"

It wasn't until I was asked to add the Biochemistry Requisition to this that I ran into this problem. With the biochemistry requisition we still have 1 order with many items on it, but now I want all of those items on the same requisition. So following your recommendation, I created variables as outlined above and have placed the Biochemistry Requisition in the group footer of {vrbl_order_detail.vrbl_id}. I then added the suppression formula

not({vrbl_order_detail.po_cat}in["Biochemistry","Endocrine Tests","Hematopathology","Drug Levels"])  

This has worked perfectly for orders that only contain items in these categories but in the example I provided it has two other items that belong on a separate requisition that are in Group Header 2 and their requisitions are appearing.

I think you are onto something when you say that I am running into a groups are hierarchical issue.

[DellSC]

The issue is in your formula to initialize the variables. You want to initialize them to empty strings instead of to the full name of the test.

-Dell

[former_member959984]

Thanks Dell, I have been successful in having all the data appear on the one requisition for the example I laid out above. I now have a large task ahead of me finishing the remaining requisition and detail.

[former_member959984]

I have run into further issues with this report. After coding all of this up and testing it I came across an issue and it has me stumped. The report itself currently has 16 requisitions embedded in it. For 15 out of the 16 requisitions I have them in

Group 2 = detail_id

For this particular requisition from this thread I have it in

Group 1 = order_id

I created an order that spans three requisitions

I use suppression formulas to show a requisition and the contents. For the requisitions found in Group 2 = detail_id I use the following suppression formula's

{vrbl_order_detail.po_desc} <> "molecular markers"

{vrbl_order_detail.po_desc} <> "OncoType DX"

For the requisition found in Group 1 = order_id I use the following suppression formula.

not ({vrbl_order_detail.po_cat} in ["Biochemistry","Endocrine Tests","Hematopathology","Drug Levels"])

What is happening is the requisitions in Group 2 = detail_id are generating but the orders in Group 1 = order_id are not displaying. If I remove the suppression for Group 1 = order_id I get what I want. Any ideas on how to get around this?

[DellSC]

If I'm reading this correctly, you are trying to suppress everything in Group 1 that is not in those four categories. Is that correct?

I think what you're running into is that the groups are hierarchical. So, when you filter Group 1, you also filter all of the Group 2 values that are excluded by the filter for Group 1. So, at the Group 1 level, you need to combine that filter with the Group 2 filter. Does that make sense? Here's what I think it will look like:

({vrbl_order_detail.po_cat} in ["Biochemistry","Endocrine Tests","Hematopathology","Drug Levels"] and 
  {vrbl_order_detail.po_desc} <> "molecular markers" and 
  {vrbl_order_detail.po_desc} <> "OncoType DX") OR
not ({vrbl_order_detail.po_cat} in ["Biochemistry","Endocrine Tests","Hematopathology","Drug Levels"])

-Dell

[DellSC]

In the order or order details, is there something like a location? For example, the ones you want to group together are all lab work. If you could group on or key off of that information, you might be able to get what you want. You'd probably have to set up a group like "if it's not lab work, group by detail_id, otherwise group by 0 to get all of the details in a single group."

-Dell

[former_member959984]

I tried grouping by po_catagory this didn't work as it still gave it to me on two pages as there are two categories for this order on the one requisition and it only gave me order detail number 10000083 for page one, it missed providing the X for details 10000084, 10000086.

I tried grouping by a formula {vrbl_order_detail.po_cat} in ["Biochemistry","Drug Levels","Endocrine Tests","Hematopathology"] but it only displays order detail number 10000083 or page 1.

How do i group 0 to get all of the details in a single group??