Skip to Content
author's profile photo Former Member
Former Member

How to put detail records in fixed formÉ


I am trying to build a crystal report based on an existing printed form. The form includes elements that look like the following:


Hot Oiler - 4 mbtu 7mbtu

Pressure Pumping - 5K 10K 15K

Travel - 5K 10K 15K

Standby - 5K 10K 15K

Each Activity also has a quantity, a rate, and a total (quantity * rate) associated with it.

The user used to circle the applicable value on the paper form (for example, if it was a 4 mbtu hot oiler vs. a 7 mbtu hot oiler).

In the application and database the user will enter line items in the work order that specify if it is a Hot Oiler 4 mbtu or a Hot Oiler 7mbtu. Similarly, the user will create a record that is either Pressure Pumping 5k or Pressure Pumping 10K or Pressure Pumping 15K. And so on.

The application is capturing all of this information. I want the crystal report to still display each of these elements as they are laid out on the current paper form however because these are detailed records I do not know how to place them in the report properly. If we take the first line in the form, I created the formula:

+if {field_tickets_has_resources.resources_id} in [150, 151] then

"Hot Oiler - " & "4 mbtu" else

if {field_tickets_has_resources.resources_id} = 163 then

"Hot Oiler - " & "7 mbtu" else

"Hot Oiler - "+

I created another formula for the second line in the form:

+if {field_tickets_has_resources.resource_roles_id} = 15 then

"Pressure Pumping - 5K" else

if {field_tickets_has_resources.resource_roles_id} = 18 then

"Pressure Pumping - 10K" else

if {field_tickets_has_resources.resource_roles_id} in [22, 24] then

"Pressure Pumping - 15K" else

"Pressure Pumping -"+

And so on. However, I cannot place each of these formula beside each other in a details line because it places the values horizontally rather than vertically in the report. I cannot place these in a header because they are actually detailed records.

I hope this makes sense. Any help, as always, is appreciated!


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Feb 08, 2012 at 05:27 PM

    Hi Mark,

    I recommend a similar apporach but the first formula will be created so we can group the records by the Activity type. For example all the Hot Oilers then Pressure Pumping, etc.

    We group on this formula then a second formula will build a list of the quantities.

    One final formula in the Group Footer will display everything on a single line. We can then hide the Group Header and Detail section because we only want to see one line per actvity type.

    Create a group on this formula:


    If {field_tickets_has_resources.resources_id} <> 0 Then 
       "Hot Oiler" 
    Else If {field_tickets_has_resources.resource_roles_id} <> 0 Then 
             "Pressure Pumping";

    This formula will define and reinitialize a string variable for each group. This goes into the Group Header and can be suppressed.


    StringVar myQuantity := "";

    This formula goes into the Detail section. Based on your first formula I'm guessing the Activity Type is based on which field is populated. I assume only one field per record can have a value that isn't 0.


    StringVar myQuantity; 
    If {field_tickets_has_resources.resources_id} <> 0 Then 
       (Select {field_tickets_has_resources.resources_id} 
          Case 150  :myQuantity := myQuantity & "4 mbtu "
          Case 151  :myQuantity := myQuantity & "4 mbtu "
          Case 163  :myQuantity := myQuantity & "7 mbtu ";)
    Else If {field_tickets_has_resources.resource_roles_id} <> 0 Then 
       (Select {field_tickets_has_resources.resource_roles_id} 
          Case 15   :myQuantity := myQuantity & "5K "
          Case 18   :myQuantity := myQuantity & "10 "
          Case 22   :myQuantity := myQuantity & "15K "
          Case 24   :myQuantity := myQuantity & "15K ";)

    This formula goes into the Group Footer and builds your string.


    StringVar myQuantity; 
    {@ActivityGroup} & "-" & myQuantity;

    All the sections except for the Group Footer can be formatted to hide.

    Hope this helps,


    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Brian,

      Thanks for this. As I work through this I'm realizing that this approach looks like it will just produce a result set if there are values present in the work order document. I'd like to present all possible data values even if they do not exist in the work order/database.

      It's a way for workers to look at the report/form like a checklist. So, in the event, they forgot to include something in the screen of the application the printout will reveal that to them.

      The form values look like the following:

      Hot Oiler Working - 4mbtu 7mbtu

      Pressure Pumping - 5K 10K 15K

      Travel - 5K 10K 15K

      Travel Hot Oiler - 4mbtu 7mbtu

      Standby - 5K 10K 15K

      2nd Operator

      Support Unit

      Chemical Pumping

      Next to each one of the options above will be 3 column values: quantity (hours); rate/hour; and total

      So, a guy could enter, on a work order, a Hot Oiler (first field) Working (second field), 4mbtu (3rd field), 10 hours (4th field), $100/hr (5th field), $1,000 (calculated value).

      A second record on the work ticket could be Pressure Pumping (first field) and 5K (second field), 8 hours (third field), $50/hour (fourth field), $400 (calculated field).

      And so on. But I'd like to have the above list at least displayed on the report even if there isn't a value in the database. For example, say a "2nd Operator" and "Standby" options weren't used in the work order in the application at least they'd still be displayed in name but there wouldn't be any related quantity, rate, or totals for them.

      Does this clarify things a bit more as to what I'm hoping to achieve?



Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.