| Author |
Message |
__Yuri__
Newbie
Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
|
   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 Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
__Yuri__
Newbie
Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
|
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 Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
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 Logged |
|
__Yuri__
Newbie
Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
jkwrpc
Senior Member
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
|
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 Logged |
|
__Yuri__
Newbie
Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
__Yuri__
Newbie
Joined: 14 Jun 2007
Online Status: Offline
Posts: 6
|
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 Logged |
|
|
|
