Subreport pulls null value not in database

[jlkjr]

Crystal Reports 8.5 and Access97

Using a subreport on a transcript to calculate the credits received at a particular school for a given school year. Subreport is linked to the students name, academic year and school name. When a particular combination of year and school has only one item entered, the subreport "invents" a second null value and only reports that one.

Where is this null value coming from and how do I get rid of it?

 

[Turkbear]

Hi,
Where in the main report is the sub inserted?

Is it a combination of name,school and year or just school and year?





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

 

[jlkjr]

The subreport is in the group header for the school, which comes after year and student.

Not sure I understand the second question. The sub report is linked to all three (student, school and year).

 

[Turkbear]

Hi,
I was confused by this:

particular combination of year and school

No mention of Name so I just wondered.

Does the main report have linked tables ( or the sub)?



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

 

[jlkjr]

Yes there are several linked tables throughout both main report and the sub.

 

[lbass]

Not sure how you know that the subreport is returning a null value but really is pulling one value that is not shown. If you run the subreport by itself, does it return the expected values?

-LB

 

[jlkjr]

The subreport does return the expected values when run by itself.

 

[lbass]

So your main report if grouped (in this order) on year, student, and school, with the sub in the school header, and with links on all three groups. Please go into the sub->report->selection formula->record and copy the formula and paste it here. It might help to see the database->"show SQL query", also (for the sub).

-LB

 

[jlkjr]

Order of groups in main report is student, year, school.

Record selection formula for main report: {StsGrdsGrds_1.StsGrdsGrds_1_Gradelevel} in ["08", "09", "10", "11", "12"]

record selection formula for subreport:{StsSt.StsSt_Studentname} = {?Pm-StsSt.StsSt_Studentname} and
{StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} = {?Pm-StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} and
{@School Name} = {?Pm-@School Name}


formula referenced in above record selection:
if isnull({StsGrdsGrds_1OSchl.StsGrdsGrds_1OSchl_Name}) then
"Our School Name" else
{StsGrdsGrds_1OSchl.StsGrdsGrds_1OSchl_Name}

show SQL query option is grayed out.

 

[lbass]

Can you please show an example of what you see in the main report and what you see in the subreport when it is not performing correctly? Please identify each field.

-LB

 

[jlkjr]

Both the main report and subreport are blank when it is not working correctly.

The detail section of the subreport contains a formula changing the credit earned field from the database to a number. It is suppressed. When it is unsuppressed, I get an error stating that the field is non-numeric. Browsing the data on the field shows a null first line and the correct data on the second. The subreport correctly interprets that there should be just one entry, but uses the null, which did not come from the database. In the subreport footer, there is a sum field giving the total of the details and a suppressed shared variable which passes the total to the main report in order for it to be summarized later in the report.

 

[Turkbear]

Hi,
You state that the field comes from the database and the first line ( not sure about how a multiline field exists, is it a RAW or MEMO field?) is NULL , yet you say the NULL in the report does not come from the database.

It appears that there are multiple matching records in the database table that has the credit earned field that match the value in the table linked to it.

On the table with the credit earned field try using a selection criteria of
NOT IsNull(credit earned field )



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

 

[jlkjr]

Problem solved. The formula for school name did not match between main report and sub causing the parameter not to match the data.