Supressing Sub Report when No Records Present

[Rog]

I need to supress a sub report when no data is present. How do I do this. I have one report that has multiple sub reports. When the report is run, I don't always get data for some sub reports in this report. When this happens I would like to supress that sub report, but how?

Thanks,

Rog

 

[emcevoy]

Hello

A subreport is an object, so even when it is blank it still exists as an object in the main report section. This means as long as that section contains a subreport object, it will never be blank.

To get around this, create a duplicate subreport and place it in a section above the section containing the original subreport. Create a formula in the duplicate subreport that returns a true if the subreport is blank. This value can then be shared with the main report, and used to conditionally suppress the section containing the original subreport.

1. Create a duplicate subreport based on the same tables as the original subreport, but only place one field on the Details section of this duplicate subreport. This field will be used later to check if the subreport is blank.

2. Place the duplicate subreport in a section above the original subreport.

NOTE:

If the original subreport is already in a header section (such as the Report Header, Page Header or Group Header) insert an additional section above it. For example, if the original subreport is in the Group Header section:

· On the 'Format' menu, click 'Section'. This opens the Section Expert.

· In the 'Sections' list, click 'Group Header' then click 'Insert'. This creates another Group Header section, so the existing Group Header is now Group Header 'a' and the new section is Group Header 'b'. Group Header 'b' is highlighted (if it isn't, click it now to select it).

· Click the up arrow (above the 'Sections' list) so the new section is now above the original section, as Group Header 'a' and 'b' switch places.

· Click 'OK' to return to the report.


3. In the duplicate subreport, create a formula that checks whether the subreport is blank. The result of this formula is stored in a variable X, which can then be shared with the main report.

· In the main report, right-click the duplicate subreport and click 'Edit Subreport'.

· On the 'Insert' menu, click 'Insert Formula'. This opens the Formula Editor.

· Type this formula:

@CheckForNull

Shared BooleanVar X;
If IsNull(Table.field) then
     X:= True
else
     X:= False

· Save the formula and close the Formula Editor.

· Place this formula in the Report Header of the duplicate subreport.


4. In the main report, create a formula to retrieve the variable X.

· On the 'Insert' menu, click 'Insert Formula'.

· Type this formula:

@SharedValue

Shared BooleanVar X;

· Save the formula and close the Formula Editor.
 
· Place this formula in the section containing the original subreport.



5. Conditionally suppress the section containing the original subreport.

· On the 'Format' menu, click 'Section'. This opens the Section Expert.

· Click 'X-2' beside the 'Suppress' checkbox. This opens the Conditional Formatting Formula Editor.

· Type in the following formula:

  {@SharedValue}= True

· Save the formula and close the Formula Editor.

· Click 'OK' to return to the report.


6. Preview the report and verify that SCR is suppressing all the blank subreports.

TIP:

To distinguish between the section containing the duplicate subreport and the section containing the original, you can format one of the sections with a different background colour:

· On the 'Format' menu, click 'Section'.

· In the 'Sections' list, click the section containing the duplicate subreport, then go to the Color tab.

· Select the 'Background color' check box, and click a color from the color list.


7. Once you've verified that the conditional suppression is working correctly, you can format the duplicate Subreport to minimize its presence on the main report.

· If you followed the tip in step 6, clear the background colour option.

· In the duplicate subreport, suppress all the sections, but do not suppress the main report section containing the duplicate subreport.

· In the main report, right-click the subreport and click 'Change Border'. Under 'Line Style', click 'none' for the 'Top', 'Bottom', 'Right' and 'Left' boxes.

· In the main report, re-size the height of the section containing the duplicate subreport so it's as small as possible. Alternately, you can go into the Section Expert and select the 'Underlay Following Section' check box.

hope this helps

cheers E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[boflexson]

an easier way is to create a field in your main report that can be used to determine if the subreport will be blank.
For example, your main report uses:
SELECT id, Name
FROM Authors

And your subreport uses:
SELECT BookTitle, PubDate FROM Books WHERE AuthId = ??

You can add to your main query to make it like this:
SELECT id, Name
,(SELECT COUNT(*) FROM Books WHERE AuthId = Authors.Id) AS Count_Books
FROM Authors

Then select Format Subreport and enter:
{Authors.Count_Books} = 0

Then suppress the blank section.

-Adam T. Courtney
Stratagem, Inc.

http://www.Stratagemnet.com