Best way to do this report?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20881
Printed Date: 06 May 2024 at 9:29pm
Topic: Best way to do this report?
Posted By: Hwach
Subject: Best way to do this report?
Date Posted: 14 Jul 2014 at 5:55am
|
Hi Everyone,
First post here! Basically I have to make a report which uses the same table and the same fields multiple times. The general structure is like so; Machine Name--------Group 1 Operator Name---- Group 2 Total Operation length, Total Operation length for Maintenance, Total Operation length for Break, etc So these come from my table. As an SQL query, I can do: SELECT * FROM Operations WHERE Operations.Operation like '%maint%' This is what I need but for the different operations. What is the best way to go about this? I thought of subreports but I have the feeling it may cause serious performance issues as the Machine is in another table so I have to link through another table to get to it; machinetable -> recordedTime -> Operations Extremely Confused with it all at the moment, so any response would be great. Thanks |
Replies:
Posted By: DBlank
Date Posted: 14 Jul 2014 at 7:00am
|
you can add a select statement to include all of the 'types' you want,
group on the the type as the 3rd group and add your operation lengths for at group 3
or consider a Cross tab in the the group level 2 using the 'type' as the row grouping and the calculation as the sum of the time
|
Posted By: Hwach
Date Posted: 14 Jul 2014 at 9:14pm
|
Hi,
This is what I'm going for, it needs to show the totals of each operation on the same report. I have tried it with subreports, but its excruciatingly slow. [IMG]http://s27.postimg.org/tf4t647lf/crystal.png" /> So the first suggestion wont be possible unfortunately. W̶h̶a̶t̶ ̶i̶s̶ ̶a̶ ̶C̶r̶o̶s̶s̶ ̶T̶a̶b̶?̶ EDIT: Looked at cross tabs, however, I'm not sure how to use this to select different rows based on another field. |
Posted By: Gurbs
Date Posted: 16 Jul 2014 at 11:23pm
|
You could try to make a formula for the different totals that you are looking for. Say you want to count the number of times where operations.operation like '%maint%'.
if {operations.operations} like '%maint%' then 1 else 0 Then create a sum of this formula. You can create a formula like this for every field that you need the totals for. |
Posted By: Hwach
Date Posted: 16 Jul 2014 at 11:56pm
| Let me try that, seems like a good way to do it! |
Posted By: Hwach
Date Posted: 17 Jul 2014 at 12:15am
if {Operations.Operation} like '%maint%' then {Operations.OperationLength} else 0
Tried this and yours however they are all showing as 0 I thought this might work; if instr({TimeSheetEntries.Operation},"%maint%",1)>0 then {TimeSheetEntries.OperationLength} else 0
However it shows random quantities for everyone... |
Posted By: Gurbs
Date Posted: 17 Jul 2014 at 12:22am
|
Place the field {operations.operation} directly into your report to see the exact values. The first formula should work, as I am using it myself in several reports as well.
Edit: Make sure that the values is exactly the same (case sensitive) |
Posted By: Hwach
Date Posted: 17 Jul 2014 at 12:51am
|
I think I have got this working now using the crosstab, only issue is the lack of customisation that it offers.. I want to add an extra summary to the table showing the hours as a percentage of their total time, is this do-able?
Edit: Nevermind, found out how to do this 
|
Posted By: Hwach
Date Posted: 17 Jul 2014 at 11:32pm
| Okay I seem to be having an issue where it is not pulling in all of the columns. I have it setup by X=Operation, Y= OperatorName and they are summarised by length. For some reason, it pulls 2 or 3 per machine when there is actually more operations with time recorded against it stored in the database... |
Posted By: DBlank
Date Posted: 18 Jul 2014 at 3:30am
|
1. if you are doing a table join in your data source the join may limit your data set. Note that crysal joins are not enforced unless you set them as so or you use fields from both tables (or end points in daisy chained joins).
2. check your select statement
|
Posted By: Hwach
Date Posted: 25 Jul 2014 at 4:22am
Okay I have a problem as it is only showing one of my operation names/one record per person. This cross tab would be perfect if it showed them all but I understand you cannot place the crosstab into a details section.
As you can see in the image, it isn't bringing through all the operations or more than one record per person.... Not sure what the solution is for this. |
Posted By: Hwach
Date Posted: 31 Jul 2014 at 5:44am
| Bump |
Posted By: DBlank
Date Posted: 04 Aug 2014 at 8:42am
|
Not really sure I understand your question.
If the values are not in the report at all then it is a data selection or join issue.
If they are not in the 'correct' cell of the crosstab then your report grouping or crosstab row and/or column sections are the issue.
since the crosstab is in a GH, the first filter applies to that instance of the crosstab is to only include a row that falls into that one group.
Most likely your issue is that you want to show cells for records that don't exist. That can be done but it is more difficult at a group level like you have it.
Basically a crosstab only shows a column (or row) when that value exists in the data set creating it. By breaking things into groups if no row exists in that group with the value of 'Excel 105 run...' it can't be includeding in that CT in that group.
|
Posted By: Hwach
Date Posted: 04 Aug 2014 at 9:06pm
|
Originally posted by DBlank
Not really sure I understand your question. If the values are not in the report at all then it is a data selection or join issue.
If they are not in the 'correct' cell of the crosstab then your report grouping or crosstab row and/or column sections are the issue.
since the crosstab is in a GH, the first filter applies to that instance of the crosstab is to only include a row that falls into that one group.
Most likely your issue is that you want to show cells for records that don't exist. That can be done but it is more difficult at a group level like you have it.
Basically a crosstab only shows a column (or row) when that value exists in the data set creating it. By breaking things into groups if no row exists in that group with the value of 'Excel 105 run...' it can't be includeding in that CT in that group. Basically I have a field called operationlength and a field called operation which can be linked to operator. I need to be able to show all the operations and their lengths for that operator. Since there is only one field, I am not sure how to select each individual operation. The cross tab worked, but it only pulls through data for one operation instead of every operation that person had done... |