asked on

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?

Mike McCracken

How are you suppressing?

mlmcc

PATKIRSCH

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:
whileprintingrecords;
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:
whileprintingrecords;
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:
whileprintingrecords;
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

deBronkart

ASKER

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

PatK,

> 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.

Dave

ASKER CERTIFIED SOLUTION

PATKIRSCH

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial