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.

 

[Seth21]

So I followed the guide(atleast I think I did) but I keep getting asked for 'Pm-@OnChangeOf' and 'Pm-@Show value) as if they were parameters when I try to look at the results of the subreport chart.

UnitSN onchangeof formula

whileprintingrecords;
stringvar strOnChangeOf;
StrOnChangeOf := strOnChangeOf + {failurereport1.UnitSN} +"^";


TotalFailTime showvalue formula
stringvar strShowValue;
strShowValue := strShowValue + totext({@TotalFailTime}) + "^";



Subreport selection formula
{failurereport1.UnitSN} in split({?Pm-@OnChangeOf},"^")


SubShowValue formula (in subreport)
ToNumber(Split({?Pm-@Show value},"^")[RecordNumber])


Anything glaringly wrong with one of these? And also, I'd like to do two 'changeof' fields in the chart, will that be as easy as just duplicating the changeof formula?

 

[lbass]

You need to link the sub to the main report on your first two formulas, and then these links are used in your subreport formulas--so the names have to correspond, i.e., the main report formulas should be names "OnChangeOf" and "ShowValue".

-LB

 

[Seth21]

My form names seem to be correct and are shown below along with the formulas. When I try to preview the chart, it still ask me for the values of 'Pm-@OnChangeOf' and 'Pm-@Show value' like they were parameters.

'OnChangeOf' formula

whileprintingrecords;
stringvar strOnChangeOf;
strOnChangeOf := strOnChangeOf + {failurereport1.UnitSN} +"^";

'ShowValue' formula

stringvar strShowValue;
strShowValue := strShowValue + totext({@TotalFailTime}) + "^";



Subreport selection formula
{failurereport1.UnitSN} in split({?Pm-@OnChangeOf},"^")


'SubShowValue' formula (in subreport)
ToNumber(Split({?Pm-@ShowValue},"^")[RecordNumber])


I feel like the problem maybe in the subreport link. The fields to link are '@OnChangeOf' and '@ShowValue'

Both have the 'Select data in subreport based on field:' checkbox unchecked. Thanks for any help!

 

[lbass]

The checkbox shouldn't be checked. Both formulas in the main report should start with 'whileprintingrecords;'.

After you have created the links, you would get the prompt for parameters if you tried to run the subreport on its own. Instead, try refreshing the main report, then the prompts won't appear.

-LB

 

[Seth21]

So I added the whileprintingrecords to the 'ShowValue' formula and made sure the boxes were unchecked.

So that all looks good.

Refreshing in the main report works better and I am not prompted for those values. A graph does not show up though.

I subbed in another field in place of the 'SubShowValue' formula and then I got a graph as expected. I tried to display the values of the formula by putting the formula in the details section of the sub-report and it would not let me refresh the report saying the formula was non-numeric.

ToNumber(Split({?Pm-@ShowValue}, "^")[RecordNumber])

The above is that formula again. This could be the problem because shouldn't the 'ToNumber' function make that a numeric field?

 

[lbass]

Can {@TotalFailTime} be null?

-LB

 

[Seth21]

I dont think it is. I have the results showing in the detail section on the main report and each result has a number value showing.

The results of 'OnChangeOf' and 'ShowValue' are both showing in the Group footer section A.

On the first group, 'OnChangeOf' displays '93^' -93 is the serial number.
'ShowValue' displays '34.03^' - 34.03 is highest value and also the last value of 'TotalFailTime'

On the second group, 'OnChangeOf' formula displays '93^104^' and 'ShowValue' displays '34.03^61.58^'

Does that all seem normal? I have a pie graph on another report I need to do with the same print-time charting so I can try that and see if I can get different result.

 

[lbass]

Try adding display formulas in the report footer of the main report:

whileprintingrecords;
stringvar strOnChangeOf;


whileprintingrecords;
stringvar strShowValue;

Then in the sub, try placing the linking fields in the report header:

{?pm-@OnChangeOf}

{?pm-@ShowValue}

In the sub, you should also have a group on ID, and change the subshowvalue formula to use groupnumber instead of recordnumber. Place the chart in the sub report footer.

See if any of that helps. I always have to play around with this to get it to work.

-LB

 

[Seth21]

She finally works!

The group and group number change is what made the difference!


The only thing that seems a bit off is that it plots all the points for unit 93 as 34.03. Which makes sense because that is the number that 'ShowValue' displays but in reality, the other points range from 0 - 30.

So basically the formula calculates total fail time but the graph is only plotting points at the largest fail time which is 34.03 for unit 93.

 

[lbass]

Remind me--Is the subreport in the report footer of the main report? And is the chart in the report footer of the subreport?
-LB

 

[Seth21]

Subreport is in the report footer of main report.

Chart is in the report footer of the subreport.

 

[lbass]

Okay, then how have you set up the chart exactly--I mean in the main screen for the advanced chart?

-LB

 

[lbass]

PS. Make sure you used "whileprintingrecords" in your formulas.

-LB

 

[Seth21]

On Change Of:
Unit Serial Number

Show Values:
@SubShowValue


This will show 2 Serial Numbers on the X axis (which is correct) but only one point for each Serial number. I think what is actually the case is each has multiple points being plotted at that exact point.


I did another plot that was:

On Change Of:
idComments

Show Values:
@SubShowValue


This then showed that each serial number had 4 points being for plotted but were all at a single point.

The points are consistent with the ShowValue display of '34.03^61.58^'

 

[lbass]

You set up the {@onchangeof} formula based on this field:{failurereport1.UnitSN}. This should be the same field you are grouping on in the main report and charting on in the subreport, and there should be only one value per instance of this field. If you wanted to show more points, then you should have used the field unique to those points as your group field and onchangeof field in the main report.

-LB