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

Posted on 2006-05-12
Last Modified: 2012-08-13
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?
Question by:deBronkart
    LVL 100

    Expert Comment

    How are you suppressing?

    LVL 8

    Expert Comment

    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


    Author Comment

    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.

    LVL 8

    Accepted Solution

    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 :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now