Formula results does not produce all records 1

[lndoan]

I have the following formula which works fine, but it does not produce all the records that I need:

This is in the detail section:

whileprintingrecords;
stringvar eqids;

If {TRIP_EQLINK_DTL.GROUP_ROW_ID} > 1 and instr(eqids,{RPT_TRIP_CONSISTS.Equipment ID}) = 0 then
eqids := eqids & {RPT_TRIP_CONSISTS.Equipment ID} & ", "
else
""

This is in the Group Header:

//{@reset}:
whileprintingrecords;
stringvar eqids := "";

This is in the Group footer where the data are displayed:

//{@display}:
whileprintingrecords;
stringvar eqids;

If the field {RPT_TRIP_CONSISTS.Equipment ID} is 'blank' or 'empty', this formula does not seem to print it on the report since i verify the records through the database, some of the records are not printing.

Any help would be greatly appreciated.

 

[lbass]

What do you want to happen if the ID field is null? You could set it to some text in a formula like {@ID} as follows:

if isnull({RPT_TRIP_CONSISTS.Equipment ID}) or
trim({RPT_TRIP_CONSISTS.Equipment ID}) = "" then
"No ID" else {RPT_TRIP_CONSISTS.Equipment ID}

Then use {@ID} in your accumulation formula instead of the field itself.

-LB

 

[lndoan]

Thanks Lbass, it's a good suggestion.

Currently, if this field is empty, the report does not print the entire record. For example: this is train 100 which prints all the data associated with the train because the field ({RPT_TRIP_CONSISTS.Equipment ID}) is populated on the database.

If train 100 has all the data, but nothing is entered into this field, i still want to print this train data with that field blank.

Could it be something in my joins? i have 4 tables join by the trip id by inner join =.

 

[lbass]

Absolutely it is your joins. You need to have a left join FROM the table that contains ALL trains to the Report_Trip_Consists table.

-LB

 

[lndoan]

Okay, i just ruled out the joins issue. I still do not see trains that have blank fields and they are the ones missing from the list.

I still think it is something in the formula. For some reason, the report is using the formula to calculate the fields, if the field is empty and there is nothing to calculate against, the report drops that train and only displays the ones it can calculate...

 

[lbass]

In order for the joins to work correctly, you also cannot have any record selection criteria on the right hand table. Also, try adding the null check formula {@ID} and use that in your variable formula.

-LB

 

[lndoan]

I placed the null check variable in a formula and checked but the records will not print to check.

The result i'm getting is still the same as before checking for nulls.

There is something preventing the report to print all the trains.

This is what i have in my selection statement:

if minimum({?Route}) = "[ALL]" then
{RPT_TRIPS.Trip Date} = {?TripDate}
else
{RPT_TRIPS.Route ID} in {?Route} and
{RPT_TRIPS.Trip Date} = {?TripDate} and
{TRIP_MAIN_OPS.SCHTYP_SCHEDTYPE_NO} = {?ScheduleType} and
not ({RPT_TRIP_CONSISTS.Equipment ID} like "SCAX8*")

** If i choose ALL routes, it should give me every single train in all the routes, but it doesn't, it only gives me the train with the field equipment.id that consist of data...

 

[lbass]

You have selection criteria on the RPT_TRIP_CONSISTS table. I can't tell what your main table is that includes all records, but any table that is joined by a left join to the main table because it might not have all the records cannot have any record selection criteria on it.

Instead of using the clause to exclude the records from the rpt_trip_consists table, use the opposite clause to suppress them in the section expert:

{RPT_TRIP_CONSISTS.Equipment ID} like "SCAX8*"

This still won't fix the problem if the other left joined table has criteria on it.

-LB

 

[lndoan]

My main table which has all the data is TRIPS_MAIN_OPS. I have join RPT_TRIPS and RPT_TRIPS_CONSIST together. The left join is from TRIPS_MAIN_OPS to RPT_TRIPS and i still see the same results with the missing trains with no equipment id.

 

[lbass]

What do you mean that you have joined the two tables together? If you have a left join from Trips_main_ops to Rpt_trips, and then a join from rpt_trips to rpt_trips_consist, that join MUST also be a left join--any join to the right of a left join must also be a left join. If instances of the train field in the trips_main table sometimes are not present in the rpt_trips table, then this also means that you cannot have record selection criteria on the report trips table and still get every train returned. You would instead have to use your parameters related to that table in formulas, as in:

if {RPT_TRIPS.Trip Date} in {?TripDate} and
{RPT_TRIPS.Route ID} in {?Route} and
not({RPT_TRIP_CONSISTS.Equipment ID} like "SCAX8*") then
{table.amt} //or whatever you want to calculate or display

-LB

 

[lndoan]

I went to my join logic and also my selection criteria and was able to make this work, however, another problem arises as a result.

Selection Criteria:

if minimum({?Route}) = "[ALL]" then
{RPT_TRIPS.Trip Date} in {?TripDate}
else
{RPT_TRIPS.Route ID} in {?Route} and
{RPT_TRIPS.Trip Date} in {?TripDate}

Before in my selection statement to have these two lines:

{TRIP_MAIN_OPS.SCHTYP_SCHEDTYPE_NO} = {?ScheduleType} and
not ({RPT_TRIP_CONSISTS.Equipment ID} like "SCAX8*")

so the report prints the result of this formula correctly:

whileprintingrecords;
stringvar eqids;

If {TRIP_EQLINK_DTL.GROUP_ROW_ID} > 1 and instr(eqids,{RPT_TRIP_CONSISTS.Equipment ID}) = 0 then
eqids := eqids & {RPT_TRIP_CONSISTS.Equipment ID} & ", "
else
""

Now, since I do not have the exclude for "SCAX8*", the report prints it in this field and I tried placing the not like statement but it goes to a evaluation status of True/False. The result of this formula i need it to NOT include the data that has "SCAX8*" in it... where can I do this now since the problem with all records printed is now corrected.

 

[lbass]

Try:

whileprintingrecords;
stringvar eqids;

If {TRIP_EQLINK_DTL.GROUP_ROW_ID} > 1 and
instr(eqids,{RPT_TRIP_CONSISTS.Equipment ID}) = 0 and
{TRIP_MAIN_OPS.SCHTYP_SCHEDTYPE_NO} = {?ScheduleType} and
not ({RPT_TRIP_CONSISTS.Equipment ID} like "SCAX8*") then
eqids := eqids & {RPT_TRIP_CONSISTS.Equipment ID} & ", "
else ""

As a separate comment, I don't understand why you would use:

if minimum({?Route}) = "[ALL]" then

...as opposed to:

if {?Route} = "All" then

I'm not sure why adding the minimum is necessary, although I'm not sure it hurts either--I haven't tested it.

-LB

 

[lndoan]

I greatly appreciate your help on this report. Your suggestion worked and it did mess up one of my other calculation but I was able to fix that formula and now everything looks good.

There is an XML page linked to this report to allow the users a GUI train route selection, they can either select a minimum of 1 route or all routes to display the data. I was looking at that when i receive the specs for the report and didn't see the difference when i changed it but i left it there since i'm not sure what it really does either.

Thank you