Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: need your ideas how to design a report Post Reply Post New Topic
Author Message
__Yuri__
Newbie
Newbie
Avatar

Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
Quote __Yuri__ Replybullet Topic: need your ideas how to design a report
    Posted: 15 Jun 2007 at 5:12am
Hi!
Can you help to a new member?
I need to create a report for using with an application which consists a Data Entry Form and work with MS SQL Server.

The application environment is the following:
There are a number of assets, for instance: site1, site2, site3, site4…
There are a number of contractors, for instance: Columbia Pictures, Halliburton, Aeroflot, Schlumberger…
Each contractor can make a number of operations of one type, for instance: pumping operations, slickline operations, e-line operations, … , additional activities.
The task is to create a Daily Operations Report like the following table:

|     |OPERATION &|                     |
|ASSET|CONTRACTOR |OPERATION DESCRIPTION| 24hr SUMMARY
+-----+-----------+---------------------+----------------
|              pumping operations                      
+-----+-----------+---------------------+----------------
|site2| PUMPING,  |Ongoing. Crew change.| bla-bla-bla
|     |
Columbia  +---------------------+ bla-bla-bla
|     | Pictures  |Open swab valve, 15  | bla-bla-bla
|     |           |min lubricator stop..| bla-bla-bla
|     |-----------+---------------------+ bla-bla-bla
|     | SLICKLINE,|Arrive on Location.  |
|     | Aerofloot |Connect hand pump.   |
|     |           +---------------------+
|     |           |Make up Function test|
|     |-----------+---------------------+
|     |
SLICKLINE, |Standbay for         |
|     |Halliburton|
Production to close  |
|     |           |well.                |
+-----+-----------+---------------------+----------------
|site4|
PUMPING,  |Open well and start  | bla-bla-bla
|     |
Columbia  |pumping brine        |
|     | Pictures  |                     |
|     |           |                     |
+----
-+-----------+---------------------+----------------
|                 e-line operations
+-----+-----------+---------------------+----------------
|     |Schlumberg.| No activites                                       |

+-----+-----------+---------------------+----------------
|               slickline operations
+-----+-----------+--------------------------------------
|site1|SLICKLINE, |On location,gas check|bla-bla
|     |Aeroflot   |                     |
+-----+-----------+---------------------+----------------  
....

The rule is the following:

to seek for the first operation for each asset and to place each asset to a suitable section, for example: the first operation for site1 is SLICKLINE, so it must be placed into “slickline operations” section, the first operation for site4 is PUMPING, so it must be placed into “pumping operations” section.

Has anybody got any idea how to realize it?

Thanks.


One, two, Freddy's coming for you...
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 15 Jun 2007 at 12:40pm
I have some thoughts on how to do this, but I need a little bit more information.
 
Can you give me some information about the structures of the tables you're using?  Do you have a field that in indicates what order the operations appear in for each site?
 
-Dell
IP IP Logged
__Yuri__
Newbie
Newbie
Avatar

Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
Quote __Yuri__ Replybullet Posted: 18 Jun 2007 at 11:16pm
Originally posted by hilfy

I have some thoughts on how to do this, but I need a little bit more information.
 
Can you give me some information about the structures of the tables you're using?  Do you have a field that in indicates what order the operations appear in for each site?
 
-Dell


hilfy, thanks a lot.
I tried  to simplify the above-mentioned description of the report as much as possible with the purpose to avoid all the minor details.
Indeed each operation has start and finish times, so there are two columns in my report with time values which are displayed to the left of the "Operation Description" column. I sort the operations by start time for each site.
As for the structures of the tables... Cristal works with XML files - ADO.NET objects. These XML files are created by a Java program which extracts data from views of Relational DBMS (MS SQL or Oracle).
Rude hierarchy of tables is the following:

<CD_PROJECT> // PROJECT may consist many SITEs
  <CD_SITE> // "
ASSET" (Site name - the 1th column of the Report) is described here, SITE may consist many EVENTs
    <DM_EVENT> //
an EVENT is a sequence of collocated operations for the SITE
       <DM_DAILY> // data for each EVENT are inputed daily in Daily Operations Data Entry Form, "24hr SUMMARY" (the last column of the Report) is  in this table
         <DM_ACTIVITY/> // sequence of operations, this table is linked with spreadsheet in Daily Operations Data Entry Form and described bellow
       </DM_DAILY>
     </DM_EVENT>
   </CD_SITE>
</CD_PROJECT>

Here is the main attributes of entity "
ACTIVITY":
<xs:element name="DM_ACTIVITY">
<xs:attribute name="activity_class" type="xs:string" use="optional"/>
<xs:attribute name="activity_code" type="xs:string" use="optional"/> // Type of operation (the 2th column and headers of sections)
<xs:attribute name="activity_duration" type="xs:double" use="optional"/>
<xs:attribute name="activity_id" type="xs:string" use="optional"/>
<xs:attribute name="activity_memo" type="xs:string" use="optional"/> // Operation description (the 3th column)
<xs:attribute name="activity_name" type="xs:string" use="optional"/>
<xs:attribute name="activity_phase" type="xs:string" use="optional"/>
<xs:attribute name="activity_subcode" type="xs:string" use="optional"/>
<xs:attribute name="daily_id" type="xs:string" use="optional"/>
<xs:attribute name="event_code" type="xs:string" use="optional"/>
<xs:attribute name="event_id" type="xs:string" use="optional"/>
<xs:attribute name="operation_code_description" type="xs:string" use="optional"/>
<xs:attribute name="service_company" type="xs:string" use="optional"/> // Contractor (the 2th column)
<xs:attribute name="start_activity" type="xs:string" use="optional"/>
<xs:attribute name="step_no" type="xs:string" use="optional"/>
<xs:attribute name="time_from" type="xs:dateTime" use="optional"/>
<xs:attribute name="time_to" type="xs:dateTime" use="optional"/>
...
Here is the Daily Operations Data Entry Form for clarity


Thanks again.


Edited by __Yuri__ - 19 Jun 2007 at 1:41am
One, two, Freddy's coming for you...
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 19 Jun 2007 at 1:20pm
I will try this post again. There are countless ways to get what you want and I do not know your data, so this may not work.  This would be a simple approach.  You could consider 3 levels of grouping. You could have a group for actvity type such as 'Pumping, then a group for the site, such as site#1,site#2 then one for the contractor such as Columbia Pictures, Slickline, etc. along with the detail information you want.

It make look someting like this:
'Pumping Operations
        Site#1
              Columbia Pictures - blah,blah,blah
         Site #2
               ABC - blah,blah,blah
               DEF -  blah,blah,blah
'Next activity
         Site #1 -
 
and so on.
 
There are also ways to do this with subreports if needed.
 
Regards
John
Regards,

John W.
www.CustomReportWriters.net
IP IP Logged
__Yuri__
Newbie
Newbie
Avatar

Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
Quote __Yuri__ Replybullet Posted: 19 Jun 2007 at 9:50pm
John,
thanks a lot,
but the situation is more complicated.
If we look on the above-mentioned example of the my Daily Report then we can see two nuances:
1. Records must be sorted by type of the first operation which has place on a Site, but all operations for same Site must be grouped together. Crystal doesn't support such sort&group semi-hierarchy.
2. The width of the "OPERATION & CONTRACTOR" column is less than width of the next one and one row of the "OPERATION & CONTRACTOR" column matches to a number of rows of the next column. So I want to have the minimum void space in the table:
WRONG:                                      CORRECT:
----------+---------------   -----------+-----------------
bla-bla-  |bla-bla-bla       bla-bla-   |
bla-bla-bla
bla-bla-  |                  bla-bla    +-----------------
bla-bla   |                  bla-bla    |bla-bla
          +---------------              +-----------------
          |bla-bla                      | bla
          +---------------   -----------+-----------------
          | bla
----------+---------------


As
far as I understand, I need to create a sub-report for
"OPERATION DESCRIPTION" column and put it to details section of the main report. But realization of above-mentioned simultaneous grouping and sorting requires using of sub-reports too :(
I've got experience of programming (FORTRAN, BASIC, C++, Java) so it is not a problem for me to develop Crystal formulas, but I'm beginner in
 Crystal, so I need to listen to skilful Crystal developers for any idea.
One, two, Freddy's coming for you...
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 20 Jun 2007 at 9:15am
Ok, I think I may have it.... (I may not have the "field" names correct, but I hope you'll understand what I'm getting at...
 
In the main report:
 
- Group on activity_code.
- Group on site.
- In the select expert, set it to only select records where the step_no = 1 (or 0 if that's your first step.)
- Put your subreport in the Site Group Header (NOT in the details!!)  Link the subreport on activity code and site  (or possibly only site - you'll need to play with it).
 
In the subreport:
- Put your column headers in one report header section and suppress the other.
- Group on Step_No.
- In the details section, put the Operation & Contractor data. For the field, turn on "Suppress if Duplicated"  Set the section to "Underlay Following" and "Suppress if Blank".
- Add a details section below the existing one and put the Operation Description information there.
- Add your lines.
 
The "Underlay Following" will solve the space issue in your last post, but it may cause problems if there is only one operation description and it isn't as "tall" as the Operation & Contractor field.
 
-Dell
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 20 Jun 2007 at 9:16am

Like anything it is easy to miss the desired output. I always think of CR as a top down report. Like you I find the left to right issue in the presentation of data problematic.

If your design is rigidly left to right, you are probably looking to subreports to move your data details across the page.  In truth I find subreports quite easy to work with. Using the proper data query you can get the specific data you want. The reports can be sized to fit the data display you need. The struggle is getting the look you want.

Again only you will know if I am still in "left field" but it might look something like this (Sorry about the formatting, it was lost when I pasted the reponse in from notepad. Hopefully, you will get the idea)

Activity (Pumping)
==============================================================
site# 1  |  Operator #1 | subReport Details Line 1  |  subReport Summary 24 Hours
                                 | SubReport Details Line 2  |  blah - blah - blah
            | -----------------------------------------------------------------------------------
            | Operator #2  | subreport Details Line 1  |   subReport Summary 24 Hours
                                                                       | blah - blah - blah
 ---------------------------------------------------------------------------------------------
site # 2 | Operator # 1  |subreport Details Line 1  | subReport Summary 24 Hours 
                                                                        blah - blah - blah
==================================================================
New Activity (Filling)
and repeat of above layout....

========================================================

CR Sections might look like this:

Page Header - Site | Activity | Details | 24 Hour Summary
Group 1 Activity
Group  2 Site
Group  2b Operator | subReport Details | SbReport Summary 24 Hours


Finally, there can be a performance hit when using subReports, but sometimes it is the only way to get data presented the way the users want.  You  may also be able to integrate some form of a crosstab report, to get what your want. I rarely use them, so there are  those that are far more experienced in the them than I would be. However they would be capable of of giving you most of what you want.  You would need to test them against the data to know for sure how close you can get.

While I am trying to be helpful, I know I may not fully understand the design requirements, data, etc. So if I have again missed the mark, my apologies. I do know this,  after working with Crystal for a number of years,  that with a bit of thought you will find it capable of meeting the requirements you have.


Regards,
John  W.
www.CustomReportWriters.net


 

Regards,

John W.
www.CustomReportWriters.net
IP IP Logged
__Yuri__
Newbie
Newbie
Avatar

Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
Quote __Yuri__ Replybullet Posted: 21 Jun 2007 at 7:39am
Thank you, buddies,
but I have to develop an another report :(.

Suppose, there are two contractors. The first contractor can make Pumping operations. The second contractor can make Slickline operations.
One day the first  contractor  made  Pumping operations on site#1,
then this contractor moved to site#2 and made  Pumping operations here.
In the meantime another contractor made Slickline operations on site#2,
then it moved to site#1 and made  Slickline operations here, tonight the second  contractor moved to site#3 and made  Slickline operations here.

You propose the following structure of the report:
Group 1 Type of Activity
   Group 2 Site


So I will have the following report:
Pumping operations // It is the first Type of Activity based on activity_code)
   Site#1   Details  of Pumping operation on Site#1
   Site#2   Details  of Pumping operation on Site#2
Slickline operations
// It is the second Type of Activity based on activity_code)
   Site#1   Details  of Slickline operation on Site#1
   Site#2   Details  of Slickline operation on Site#2
   Site#3   Details  of Slickline operation on Site#3

But the customer need the following Structure:
Pumping operations
   Site#1   Details  of Pumping operation on Site#1
                
Details  of Slickline operation on Site#1
Slickline operations
   Site#2   Details  of Slickline operation on Site#2
                 Details  of Pumping operation on Site#2
   Site#3   Details  of Slickline operation on Site#3

In other words the customer doesn't want to break Sites, all daily operations for a Site must be together. In the meantime each Site must be placed into proper Operation Type section accordantly with the first operation which has place on the Site.
 
 
One, two, Freddy's coming for you...
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 21 Jun 2007 at 7:44am
You'll use basically the same method as you're using for the other report, just drop the contractor information out of it.
 
-Dell
IP IP Logged
__Yuri__
Newbie
Newbie
Avatar

Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
Quote __Yuri__ Replybullet Posted: 24 Jun 2007 at 7:53am
Thanks,
I'm trying to realize your ideas.
I've just realized overlay of group header section with detail section - superbly done!
One, two, Freddy's coming for you...
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.