Summary in Subreport won't pickup all formula results. 2

[NBVC]

Hi all.

I am having difficulty trying to get a subreport to summarize based on a formula.

In the main report I have a field in my Details section called @HTSCode. This is the formula:

If Len(Trim({SalesOrderLines.uomlAlternateHTSCode}))= 0 then
    IF len({PartRevisions.imrTariffID})= 0 then 
        formula = "8413.91.00"
    else
        formula = {Tariffs.urmtHarmonizedCode}
    End if
else
        formula = {SalesOrderLines.uomlAlternateHTSCode}
end if

It works well in the main report.

Now in the Subreport, I am using the same exact formula and want to summarize in my Footer, grouped by @HTSCode

For some reason, even though my main report picked up and displayed results of "8413.91.00" (which should display when the {SalesOrderLines.uomlAlternateHTSCode} and {PartRevisions.imrTariffID} are blank), my subreport refuses to pick it up. It will only summarize the lines that have those database fields filled in by the user.

Is there anything anyone can suggest as to what may be the possible reason? I tried all sorts of revisions to the formula to no avail.

Thanks.

 

[kenhamady]

The subreport can only summarize records within the subreport, so the results in the main report are of limited usefulness when troubleshooting. To see clearly what is going on, I suggest showing the details band of the subreport and putting the formula on it. Also show the fields that the formula references. That way you can see the input and the output of the formula for each record in the subreport.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

Hi Ken,

Thanks for the quick reply.

Actually, I did put the same formula in the details section already as part of my debugging trials. I got the same results. The only one that did not show is the one I expected to result in my default "8413.91.00" string.

I also just tried putting the fields that the formula references in the details. That too shows expected results only for the info that came from the database fields. I don't see blank fields, rows, etc.

The reason I mentioned the main report just to show that the same formula there yielded my expected results. The tables in the main report and in the subreport are the same.

 

[kenhamady]

Try this, at the top of the formula editor change the setting for default values from "Exception for Nulls" to "Default Values".

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

Hi,

No, that did not seem to do the trick, unfortunately.

 

[kenhamady]

Can you test your two LEN() conditions separately and see if they are both returning true in the formula? One has a TRIM and the other doesn't.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

Hi Ken,

I added the Trim to both conditions and still getting same results.
Also, I changed the results to show the LEN() results not getting any 0's which I would expect for the line that I want the "8413.91.00" returned.

Note: I am not using any suppression formulas either, in case you are wondering.

 

[kenhamady]

Did you test the LEN lines separately?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

I think so, but maybe I didn't quite understand. Can you tell me exactly what to do so we are definitely on the same page?

For example first I tried this (at the detail level)

If Len(Trim({SalesOrderLines.uomlAlternateHTSCode}))= 0 then
     Formula = true
else 
     formula = false
end if

then I replace that with:

If len(trim({PartRevisions.imrTariffID}))= 0 then
     Formula = true
else 
     formula = false
end if

 

[kenhamady]

Perfect - when you preview these, do they both show TRUE on the record that should show "8413.91.00"?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

Hi Ken,

The problem is that record doesn't even come up. Almost like it is being suppressed for some reason.

My report should have a total of 16 items, but I am getting 15. The missing item is the one that I am apply that default code.

 

[kenhamady]

OK, we somehow got WAY off the mark. There is no point talking about summaries and formulas if the record is missing. Go into the Subreport and use "Database > Show SQL Query" to show the SQL being used to generate those 15 records. That will give us a clue as to why #16 is missing. If you want to skip ahead you can read my December blog post about missing records:

http://kenhamady.com/cru/archives/2702

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[NBVC]

Hi Ken,

I am too embarrassed to admit it, but that got me in the right direction. I don't know why I didn't think about it, but it was the Joins.... I just changed the Inner Joins to Left Outer Joins, and voila, the record came up. That was the one difference with the sql in the main report. It just never dawned on me to check that. I was too focused on the formula being the issue.

Thank you so much, and sorry for the confusion.