Trying to do calculation based on different rows

[Seth21]

I am trying to have a failure report that accesses information from MySql. So far my link to MySql is fine and I used a Sql command in the database expert to get only the fields I needed.

I can do calculations within a row but I don't know how to call another row and specify which rows I want. What I'd like to do is be able to basically query the data I have in CR or just do a SQL query on my datasource. Is anything like that possible?

StudentID StartedStudying StoppedStudying StudyTime(hr)
73 2011-03-11,06:31:05 2011-03-11,09:31:05 3
75 a timestamp a timestamp
76 a timestamp a timestamp
73 2011-03-12,06:31:05 2011-03-12,09:31:05 6

On this simple table above, what I would want to do is populate the StudyTime field which is like total study time up to that point. On StudentID 73, getting that first field to populate is simple, I just don't know how to write the code where the second instance of studentid 73 would work and include the first row for StudendID 73. I hope I have been atleast somewhat clear.

 

[Turkbear]

Hi,
I do not know MySql syntax but a Command could be used to sum the times, like:

Select StudentID,sum(StoppedStudying-StartedStudying) as StudyTime
from
Table
Group by StudentID


Link this by StudentID to your original table or create a UNION ( if MySql does that) to use a single command.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Seth21]

So a sql commands can be placed in the formula ? That'd be great

 

[Turkbear]

Hi,
It is not a formula but a view of the data that can be used to join with your other command object like it was a table.

Ideally, you would create a Command that did both queries and UNIONed them together, something like ( Your syntax will be different, this is just an example):

Select StudentID,StartedStudying,StoppedStudying
from Table
UNION
Select StudentID,NULL,NULL,Sum(StoppedStudying - StartedStudying) as StudyTime(hr)
from Table
Group by StudentID,NULL,NULL






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Why not just group on student ID in the body of the report? Then you can right click on your calculation and insert a summary on it at the ID level.

-LB

 

[Turkbear]

Hi,
As always LB, you cut to the heart of the matter..That
should work if the issue is as described..I got caught up in the seeming complexity and lost sight of the objective!





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Seth21]

Thanks for the helpful responses guys!

LB,

I like the group idea will do that. The only thing that throws a wrench in the system is that I don't always want to sum everything.

I made another table that more accurately represents my problem. I made the other simpler table because I was just trying to find my fundamental error but I imagine ya'll will be much better equiped to help me if you know all the details. I'm running CR 12. My database actually has timestamp in it but I haven't had any trouble changing two timestamps into elapsed time. I'm trying to record the total time a system has to run before a failure occurs. So if a failure occurs in run number 2, then the total time would be the elapsed time before failure in test run 2 plus the time in test run 1.



TestRun(hr) = length of testrun
FailureFound(hr) = testrun elapsed time when failure is found
TotalRunTime(hr) = total system runtime when failure is found


ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)
100 1 10 .5 .5
100 1 10 6 6
100 2 20 3 3+10
100 3 5 5 5+20+10


Hope all this make sense. Thanks so much for helping out. I've been able to write this code in a php website but am still learning how to work in CR.

 

[lbass]

Please show the content of your actual formulas for testrun and failurefound. I also am unclear on why there are two instances of runnumber 1.

-LB

 

[Seth21]

For time difference between two time stamps:

({JoinedComments.CommentTimeStamp} - {JoinedComments.RunStartTimeStamp})*24

A formula like this has been working for me. Honestly it seems to simple to me to work but it does.

Also, there can be multiples of any test run because a system can have a failure and not shutdown. For instance, a water leak that is considered minor and we still finish the test run with intention of fixing the water leak after the test run is finished. A loose connector is another example of something we would consider a failure but that typically does not cause a shutdown.

So I guess the fundamental problem is doing a calculation between rows and selecting a specific row and field to work with.

 

[lbass]

You didn't show both formulas, and you didn't identify which one corresponds to the formula you are using. Please try again.

Also, what field distinguishes a minor incident from a shutdown?

-LB

 

[Seth21]

Sorry, I thought you just wanted to see the general code to check how I was doing it.

Time til Failure
({JoinedComments.CommentTimeStamp} - {JoinedComments.RunStartTimeStamp})*24
-Failures logged in a table called Comments

RunTime
({JoinedComments.MilestoneTimeStamp} - {JoinedComments.RunStartTimeStamp})*24
-Shutdowns are logged in a table called Milestones


No field distinguishes a minor incident from a shutdown. Any failure will be logged in the comments table as a failure and failure code describes the failure. If a shutdown occurs, it is logged in the milestones table. All that is done pretty well through PHP forms.

 

[lbass]

Then is it the case that the MilestoneTimeStamp will be null for minor failures, and the commenttimestamp will be null for shutdowns?

Also, what I am trying to do is understand the basis for your columns TestRun and FailureFound. I thought you would be showing me the formulas for these--so I still don't know how the two formulas you have provided relate to these.

I was trying to figure out how you were choosing which value of testrun to add in the TotalRunTime column. But all of this is irrelevant if testrun is constant even if there are multiple failures. If this is the case, then ignore my previous questions, and instead please answer what you would expect to see in the following case for the TotalRunTime:

ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)
100 1 10 .5 .5
100 1 10 6 6
100 2 20 3 3+10
100 3 5 5 5+20+10
100 4 10 2
100 4 10 5
200 1 5 2
200 2 3 1

-LB

 

[Seth21]

ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)
100 1 10 .5 .5
100 1 10 6 6
100 2 20 3 3+10
100 3 5 5 5+20+10
100 4 10 2 2+5+20+10
100 4 10 5 5+5+20+10
200 1 5 2 2
200 2 3 1 1+5


And as a reminder, TotalRunTime=total run time until a particular failure is found.

I'm experimenting right now with SQL expressions. Although, I had to ditch my SQL command and just made a 'view' in MySql database with all the relevant fields so I could import that table.

 

[lbass]

Not sure what you mean by "until a particular failure is found". Don't you mean until the last failure in the run is found?

This is not a difficult problem to solve--once I understand what the rules are--and I don't think a SQL expression or command is the answer really. One more variation here:

ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)
100 1 10 .5 .5
100 1 10 6 6
100 2 20 3 3+10
100 3 5 5 5+20+10
100 4 10 2 2+5+20+10
100 4 10 5 5+5+20+10
100 5 10 7 __________
200 1 5 2 2
200 2 3 1 1+5

-LB

 

[Seth21]

ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)
100 1 10 .5 .5
100 1 10 6 6
100 2 20 3 3+10
100 3 5 5 5+20+10
100 4 10 2 2+5+20+10
100 4 10 5 5+5+20+10
100 5 10 7 7+10+5+20+10
200 1 5 2 2
200 2 3 1 1+5


What I mean is, If a unit runs for 40 hours and has a failure, the totalruntime would be 40 hours because it is the total runtime until a failure is found.

So a unit could have 3 or 100 test runs, but if it runs for a combined 40 hours and fails, then totalruntime = 40hrs.

Thanks for trying to understand this! So do you think this is possible in CR?

 

[lbass]

Insert a group on ID (you can suppress it if you wish). In the detail section, add this formula for TotalRunTime:

whileprintingrecords;
numbervar x := {@FailureFound};
numbervar y;
numbervar z := z + y;
if onlastrecord or
{table.runnumber} <> next({table.runnumber}) then
y := {table.testrun};
x + z

In the ID group header, add this formula:

whileprintingrecords;
numbervar x;
numbervar y;
numbervar z;
if not inrepeatedgroupheader then (
x := 0;
y := 0;
z := 0
);

-LB

 

[Seth21]

Thats fantastic! So it works almost perfect. The one flaw is that on multiple test runs, it keeps adding the 'Testrun' time.


ID RunNumber TestRun(hr) FailureFound(hr) TotalRuntime(hr)100 1 5 1 1
100 2 10 2 2+5
100 2 10 4 4+5+5
100 2 10 9 9+5+5+5


I've been working with it but can seem to get it just right. Any ideas?

Also, I tried to plot 'totalruntime' and it does not show up in my chart expert although other formulas do. Can I not plot this field?

 

[Seth21]

whileprintingrecords;
numbervar x := {@FailTime};
numbervar y;
numbervar z := z + y;
if
{failurereport1.RunNum} = 1 then
y:=0
else if
{failurereport1.RunNum}>1 and previous({failurereport1.RunNum})<>{failurereport1.RunNum} then
y:=previous({@TotalTestRunTime})
else if
{failurereport1.RunNum}>1 and previous({failurereport1.RunNum})={failurereport1.RunNum} then
y:=0
else
y := {@TotalTestRunTime};
x + z + y

This code here appears to be working. I'm still testing it for issues.

 

[Seth21]

So the above formula seems to be checking out!

Any thoughts on why formula field isn't showing up in the chart expert data sets?

 

[lbass]

Yes--It's because you are using variables whileprintingrecords (sequential accumulation). You would have to use a technique called "charting on print-time formulas" described here:

https://www.sdn.sap.com/irj/sdn/go/...ary/uuid/40e7130d-aa1e-2b10-ce9f-eee2a54ff4cd

Just say okay when asked for the certificate.

-LB