multiple counts (different values) - subreports?

[lookers]

Using crystal report 9

I have a number of tables e.g. ( factory table, line table, product table, steps table, history). I want to find the percentage of the total number

of steps and the total number of passed steps in each group. (I have to group on several; factoryID, lineID, productID, step , etc.) Is it possible to do this in one report a subreport or a link subreports. (in sql I would do it with select in select but how to do it here.) I try to avoid to connect to the database 2 times for this information. The tables have handreds thousands of records or millions. I have to get this information from one selection.

Thanks

 

[lbass]

Your first step should be to experiment. Insert the four groups, and add the step field to the details section and also a formula {@passed} for the passed steps (if that's how passed status is recorded):

if {table.field} = "Passed" then 1

Then right click on {table.step} and insert a summary, checking "Show as a percentage of the grand total". Repeat for the {@passed} formula. If you don't have record inflation, this should work. Otherwise you could use running totals instead, and then use them in formulas to obtain the percentages.

-LB

 

[lookers]

Hi Ilbass,
thanks for your reply. I want to make things little more clear:I am using Crystall reports 9 with mysql (this may have no importance.). I did try what u suggested, even I started with the run total but I coulnd't manage yet either way.
With the fo
If I choose "Show as a percentage of the grand total" then this will be the percentage for the entire total, but I need the percentage for each group in part (not only per total). And if I try to define a new variable to put it as summary in another group then I can choose show as percentage only for a group above in the hierarcy.

 

[lbass]

Please show some sample data with percentages so we can see what you are trying to do at each group level.

-LB

 

[lookers]

Hi,
We have Five table: tblPlant tblLine, tblproduct, tblProcessStep , tblMoveHistory

The relation between the table are
tblPlant -tblLine : 1 -*
tblLine - tblproduct *-*
tblline - tblProcessStep 1 - *

tblMoveHistory contains the productID, stepID and lineID, moveTime and the result (which is pass or failed).

now we have to calculate the totalNoOfPass/totalNoOfprocessed (pass+failed) as a percentage % and group by /filter by: date from-date to, bucket (intervals), plant, line, product, processStep. We did a report that calculate per intervals in part and per all these grouping the number of pass , and another report that calculate the total number process again at each group plantID, lineID, productID, stepID, dataInterval

now the problem was to link these two reports together or to modify one: so I can have per each of this group the percentage total Pass/ total process.


Here is what we need achieved using sql

SELECT processed.plantName,
processed.plantID,
processed.lineName,
processed.lineID,
processed.Product,
processed.productID,
processed.stepName,
processed.stepID,
processed. dateInterval,
passed. passNO/processed .processNO AS FINAL RESULT
FROM

(
SELECT p.PlantID,
p.PlantName,
l.LineID,
l.LineName,
pd.Product,
pd.ProductID,
ps.StepName,
ps.StepID,
count(mh.result) AS processNO,

@intv:= TRUNCATE((DAY(mh.MoveTime) – DAY(@fromD))/@bucket,0) AS dateInterval

FROM tblMoveHistory AS mh INNER JOIN tblProducts.ProductID = mh.ProductID
INNER JOIN tblprocesssteps ps ON ps.StepID = mh.OldStep
INNER JOIN tblLine l ON l.LineID = mh.lineID
INNER JOIN tblPlant p ON l.PlantID = p.PlantID

WHERE mh.moveTime BETWEEN @fromD AND @endD
p.plantIDlike '%%' AND
ps.stepID like '%%' AND
l.lineID like '%line%' AND
pd.productID like '%%'



GROUP BY p.plantID, l.lineID, pd.productID, ps.stepID, DateInterval
)
AS processed
INNER JOIN (
SELECT p.PlantID,
p.PlantName,
l.LineID,
l.LineName,
pd.Product,
pd.ProductID,
ps.StepName,
ps.StepID,
count(mh.result) AS passNO

@intv:= TRUNCATE((DAY(mh.MoveTime) – DAY(@fromD))/@bucket,0) AS dateInterval

FROM tblMoveHistory AS mh INNER JOIN tblProducts.ProductID = mh.ProductID
INNER JOIN tblprocesssteps ps ON ps.StepID = mh.OldStep
INNER JOIN tblLine l ON l.LineID = mh.lineID
INNER JOIN tblPlant p ON l.PlantID = p.PlantID

WHERE mh.moveTime BETWEEN @fromD AND @endD
p.plantIDlike '%%' AND
ps.stepID like '%%' AND
l.lineID like '%line%' AND
pd.productID like '%%'
mh.result like 'pass'


GROUP BY p.plantID, l.lineID, pd.productID, ps.stepID, dateInterval
)
AS passed

)
ON (passed .plantID = processed.plantID AND passed .lineID = processed.lineID AND
passed .productID = processed.productID AND passed .stepID = processed.stepID, passed .dateInterval= processed.dateInterval)
/*this line is not essencial cause they are already grouped*/
GROUP BY processed.plantID, processed.lineID, processed.productID, processed.stepID, processed.dateInterval

Thanks

 

[lbass]

Please show some sample data with percentages so we can see what you are trying to do at each group level.

-LB

 

[lookers]

Hi lbass,
I managed to make it work in a way but not sure the right one. I have two running totals per each group and I calculate the the percentage of each group based on these two variable (one increments if it process passes, the other counts the number of records in that group). This is similar to what you recommmened above but I do this for each group.

Now my concern is how i will see these separate running totals in a viewer (jsp) and how I will know for each record which running total to access.

here is a sample of the tables that we have

tblMoveHistory
MoveHistoryID CircuitID MoveTime Result OldStep NewStep UserID StationID
------------- --------- ------------------- ------ ------- ------- ------ ---------
1 1 2004-12-11 11:01:04 Pass 1 2 2 1
2 1 2004-12-11 11:01:03 Failed 2 8 4 2
3 2 2004-12-12 11:04:03 Pass 1 2 3 1
4 1 2004-12-11 11:05:10 Pass 2 4 4 2
5 4 2004-12-11 11:01:02 Pass 4 3 4 4
6 1 2004-12-11 11:08:12 Pass 4 3 3 2
7 2 2004-12-13 11:07:10 Pass 2 2 3 3
8 3 2004-12-11 20:01:02 Pass 1 2 5 1
9 3 2004-12-11 20:01:10 Failed 2 4 3 2
10 1 2004-12-11 11:20:10 Failed 3 4 3 3
11 2 2004-12-13 11:07:59 Pass 4 3 4 2
12 1 2004-12-11 11:07:59 Failed 3 4 3 2
13 6 2004-12-12 23:23:23 Pass 3 4 2 3
14 3 2004-12-11 11:23:25 Pass 4 2 2 2


ProductID
---------
1
2
3
4


tblPlant

PlantID PlantName
------- ---------
12 C
14 L
11 T




tblCircuits

circuitID productID
--------- ---------
1 1
8 2
7 2
4 2
5 2
6 3
2 1
3 1


tblLine
lineID plantID LineName
------ ------- --------
4 12 Line2
5 12 Line1
6 11 Line1

tblProcessStep
StepID stepName
------ ------------
1 Start
2 Next
3 Another Step
4 Rework
6 Debug
8 Scrap


tblStation
stationID lineID
--------- ------
1 4
2 4
3 4
4 6
5 4
6 6
7 4

tblStationSteps

stationID stepID
--------- ------
1 1
1 2
1 3
2 1
2 2
2 4
3 1
3 2
3 3
4 2
4 4
5 2
7 2

Result!!!!!!!!!!!!!!!!!wanted


Plant Line Product Step Date Interval BeginTime EndTime Process Yield
T 100.00%
T Line1 100.00%
T Line1 2 100.00%
T Line1 2 Rework 100.00%
T Line1 2 Rework 0 2004-12-10 2004-12-17 100.00%
C 66.67%
C Line2 66.67%
C Line2 1 66.68%
C Line2 1 Start 100.00%
C Line2 1 Start 0 2004-12-10 2004-12-17 100.00%
C Line2 1 Next 66.70%
C Line2 1 Next 0 2004-12-10 2004-12-17 66.70%
C Line2 1 Step4 0.00%
C Line2 1 Step4 0 2004-12-10 2004-12-17 0.00%
C Line2 1 Rework 100.00%
C Line2 1 Rework 0 2004-12-10 2004-12-17 100.00%
C Line2 50.00%
C Line2 1 50.00%
C Line2 1 Start 100.00%
C Line2 1 Start 0 2004-12-10 2004-12-17 100.00%
C Line2 1 Next 0.00%
C Line2 1 Next 0 2004-12-10 2004-12-17 0.00%
C Line2 100.00%
C Line2 3 100.00%
C Line2 3 Step4 100.00%
C Line2 3 Step4 0 2004-12-10 2004-12-17 100.00%


P.S. This is something like mysql is doing for group WITH ROLLUP

 

[lookers]

Hi lbass,
I managed to make it work in a way but not sure the right one. I have two running totals per each group and I calculate the the percentage of each group based on these two variable (one increments if it process passes, the other counts the number of records in that group). This is similar to what you recommmened above but I do this for each group.

Now my concern is how i will see these separate running totals in a viewer (jsp) and how I will know for each record which running total to access.

here is a sample of the tables that we have

tblMoveHistory
MoveHistoryID CircuitID MoveTime Result OldStep NewStep UserID StationID
------------- --------- ------------------- ------ ------- ------- ------ ---------
1 1 2004-12-11 11:01:04 Pass 1 2 2 1
2 1 2004-12-11 11:01:03 Failed 2 8 4 2
3 2 2004-12-12 11:04:03 Pass 1 2 3 1
4 1 2004-12-11 11:05:10 Pass 2 4 4 2
5 4 2004-12-11 11:01:02 Pass 4 3 4 4
6 1 2004-12-11 11:08:12 Pass 4 3 3 2
7 2 2004-12-13 11:07:10 Pass 2 2 3 3
8 3 2004-12-11 20:01:02 Pass 1 2 5 1
9 3 2004-12-11 20:01:10 Failed 2 4 3 2
10 1 2004-12-11 11:20:10 Failed 3 4 3 3
11 2 2004-12-13 11:07:59 Pass 4 3 4 2
12 1 2004-12-11 11:07:59 Failed 3 4 3 2
13 6 2004-12-12 23:23:23 Pass 3 4 2 3
14 3 2004-12-11 11:23:25 Pass 4 2 2 2


ProductID
---------
1
2
3
4


tblPlant

PlantID PlantName
------- ---------
12 C
14 L
11 T




tblCircuits

circuitID productID
--------- ---------
1 1
8 2
7 2
4 2
5 2
6 3
2 1
3 1


tblLine
lineID plantID LineName
------ ------- --------
4 12 Line2
5 12 Line1
6 11 Line1

tblProcessStep
StepID stepName
------ ------------
1 Start
2 Next
3 Another Step
4 Rework
6 Debug
8 Scrap


tblStation
stationID lineID
--------- ------
1 4
2 4
3 4
4 6
5 4
6 6
7 4

tblStationSteps

stationID stepID
--------- ------
1 1
1 2
1 3
2 1
2 2
2 4
3 1
3 2
3 3
4 2
4 4
5 2
7 2

Result!!!!!!!!!!!!!!!!!wanted


Plant Line Product Step Date Interval BeginTime EndTime Process Yield
T 100.00%
T Line1 100.00%
T Line1 2 100.00%
T Line1 2 Rework 100.00%
T Line1 2 Rework 0 2004-12-10 2004-12-17 100.00%
C 66.67%
C Line2 66.67%
C Line2 1 66.68%
C Line2 1 Start 100.00%
C Line2 1 Start 0 2004-12-10 2004-12-17 100.00%
C Line2 1 Next 66.70%
C Line2 1 Next 0 2004-12-10 2004-12-17 66.70%
C Line2 1 Step4 0.00%
C Line2 1 Step4 0 2004-12-10 2004-12-17 0.00%
C Line2 1 Rework 100.00%
C Line2 1 Rework 0 2004-12-10 2004-12-17 100.00%
C Line2 50.00%
C Line2 1 50.00%
C Line2 1 Start 100.00%
C Line2 1 Start 0 2004-12-10 2004-12-17 100.00%
C Line2 1 Next 0.00%
C Line2 1 Next 0 2004-12-10 2004-12-17 0.00%
C Line2 100.00%
C Line2 3 100.00%
C Line2 3 Step4 100.00%
C Line2 3 Step4 0 2004-12-10 2004-12-17 100.00%

 

[synapsevampire]

If you have the SQL, just paste it in as an Add Command and replace the parms from the SQL with Crystal parms and you're done.

An alternative would be a stored procedure (probably the fastest), or creating a view for each of the derived (inline) tables in your posted SQL and then joining them in Crystal accordingly so that Crystal can effectively pass parms to each as required.

I wouldn't waste time trying to kludge the Crystal interface, you're functional as is.

-k

 

[lookers]

Hi synapsevampire,
I cant use stored procedure because the version of mysql dose not support it. could you tell me how to use Add Command. I have looked around and cant find this feature/button. I am new to crystal reports

Thanks

 

[synapsevampire]

When you open the MySQL datasource in the data explorer it should be the first choice below (Add Command).

I seemed to recall that MySQL doesn't support SPs, however if it supports Views, you can still address it that way as well.

Add Command is great for converting existing SQL to Crystal, but I prefer tohe more database oriented approach of creating reusable database objects.

-k

 

[lookers]

Hi synapsevampire,
Thanks for your help. The version of mysql that we are using also dosent support Views. Anyway the sql is working. One more question. When you add the params what syntax do you use. Is if @param or do you use the {&param} syntax used in crystal

Thanks