• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Subreport formulas are blank, but text boxes aren't, if no records found

This is the first time I've used subreports. I've got a linked subreport, which fetches data from another table based on a parameter (the current item code).

It's working fine, except for the case where the subreport gets no records. In that case, I have two problems:

1. The title line is a formula, like "N  kits use this item code". When there are no records, the formula doesn't display. (When the count of kits in the report is 0, It's supposed to say "*** No kits use this item. ***")

2. The column headings in the subreport always print, no matter what.  I can't suppress them no matter what I try.

Both are driven by the count of returned records (count(command.kitnumber)=0) so maybe there's something I don't understand about the interaction between the main report and the subreport.

Where should I look?
  • 2
1 Solution
How are you suppressing?

just a couple of thoughts/questions: (and likely what's happening is that the zero formulas are not working because the actual subreport record count is "null")
1) when you're referring to a "title line" - I'm assuming that this is in the Main report? If so, in order to "respond" to the results of the subreport, you will need 2 thinsgs:
a) the formula/label you'd like to display needs to be placed in a section after the section in the main report that contains the actual subreport, and
b) you will need to pass the subreport record count over to the main report (via shared variable) so that the message can "respond" to the subreport record count of 0... what this entails in your case is creating 3 formulas (2 in subreport, 1 in main report) as follows:
 subreport header = purpose to set shared variable to zero each time the subreport runs:
sharednumbervar subItemcount :=0

subreport footer = purpose is to determine the total number of records in the subreport so that you can then "pass this result" to the main report each time the subreport runs:
sharednumbervar subItemcount;
if Isnull(count({command.kitnumber})) then subItemcount := 0
else subItemcount := count({command.kitnumber})

now back over to the main report = purpose is to fetch the count value from the sub report. This formula will need to be placed in a section below the subreport:
sharednumbervar := sharednumbervar

Test these formulas initially by making them visible so that you can see the data flow, and then you can start suppressing them... Now, once you have the "zero" coming across t the main report, you should be able to refer to this formula result to get your "conditional message" to work...

Meanwhile, the column headers in the subreport are also probably not responding to your count({command.kitnumber})) = 0 formula, once again because of the "null" value, so try changing their condition to now be based on your new formula (which should now return the zero that yyou;re looking for, even if the record count is "null"

hope this helps! :) Pat K

deBronkartAuthor Commented:
mmlmc, I'm suppressing conditionally using the Section Expert.


> actual subreport record count is "null"

Arg!  I never would have thought of THAT!  I think you put your finger on it - I'll explore it.  Thanks.

yeah, those crazy "nulls" will get you every time! (who would think that a whole lot of "nothing" would cause such grief?)

meanwhile, you'll want to Not conditionally suppress the subreport if you want to vary a condition based upon the subreport results... the reason that this is the case is that the subreport must actually "run" in order to return the results (even if the count is zero) to be able to allow the main report to respond accordingly...

Now, assuming that you don't want to just "waste space" if there are no records int he subreport, what you can do is:
1) make the subreport as "thin" as possible in the design view of the main report (it will look like it's only about 1/2 row tall)
2) then <right click>on the sub report (from main report design view) and format it to "can grow" so that it will expand if there actually are records, and then
3) <edit> the subreport itself so that the various sections (easier than individual fields, though that would work also) all conditionally suppress if the count equals zero (once again, nulls can give you grief)

what these 3 steps will do is:
1) still allow the sub report to run & thus pass the results to the main report
2) if there are no records in the subreport, it will not "grow" and the fields will be "conditionally supressed" so that it will simply look like you chose to include 1/2 row of spacing when there are no records in the subreport

hope this all helps :)

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now