Skip to Content

Displaying Different Data by Group

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Oct 03, 2018 at 06:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Oct 03, 2018 at 03:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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??

  • Oct 04, 2018 at 04:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Dec 11, 2018 at 09:25 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 21, 2018 at 09:32 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded