Prevent sub-reports from ruining your report in Crystal Reports

I like SAP Crystal Reports. Why? Because it helps gather the information needed and present them as I would like to, with just basic crystal reports design knowledge.

I never planned to learn it until I started to work for my current employer years ago.

Despite years of learning how to use it for business purpose, there is always something new that I learn when I create a new report.

Mostly I am self-taught on it, but I can with easy call myself an advanced user now.

My reports are not only drag and drop fields from the database and display in the desired format. There is plenty of formulas, parameters, exclusions and inclusions, and of course, on some of them have sub-reports.

I am not going into sub-reports when I can achieve what I want using a standard approach. Really, sub-reports are the last thing that I am going for if I cannot see all the data I need.

There is one main reason why I am not using sub-reports in practice very often. They tend to break the way how I want the final report to look, and display data in preview.

The other reason is when you need to create a report that people will export (to Excel or CSV). Then, apart from layout issues, you face problem with duplicated values.

Recently I created a report that works as a middle-man between our main program and our digital board, displaying customers’ orders on a designated screen for our warehouse staff.

Multiple different tables (live, in-progress and archive) are not designed to coexist on single report. This time it was not possible to achieve all what we need without going into sub-reports.

Knowing how bad reports looks with embedded sub-reports, I knew that I need to find a way, as in other case, output can cause a serious problem for the company (duplicated orders).

And the solution for that issue so simple that I was amused, that I haven’t fought about it before.

But, starting from the beginning.

I will not be explaining how to use sub-reports here. Let’s assume that you know this already.

When in sub-report you try to get a value into the main report, you use something like that in Formula Fields.

WhilePrintingRecords;
Shared NumberVar Items := DistinctCount ({sopdet.part})

You put this field into your sub-report.

Then you create another one in Formula Fields inside the main report like that.

WhilePrintingRecords;
Shared NumberVar Items

When you put this field into your main report you see, that records /or/ not showing /or/ are moved by 1 place (row 1 displaying figure for row 2 etc.).

To you need to put your sub-report one section above (for example, by creating Details section A and Details section B, where A contain sub-report and B contain your formula field from which you take specific data in the main report).

What you are interested about is to get specific data and display it in Details B section.

You not really bother with Details A and sub-report there. You really would like it to disappear. However, this section is needed to be able to work and bring data, but not needed for the purpose of displaying data in preview.

You cannot suppress Details A, as this will not bring any data into the field in Details B.

What you can do is to head to your sub-report and suppress every single section there.

Despite that sub-report, all sections have been suppressed, and the data are still passed through to the main report.

In Details A on the main report, where the sub-report probably will throw multiple output lines, you will have now an empty section (single line).

Because its theoretically empty, the main report still not showing correctly in Preview, as this section - Details A, is dividing your data in preview from Details B.

To get rid of it, you can, by right click on Details A section and selecting Section Expert, tick the box to Underlay Following Section.

Thanks to that, section Details A, that’s printing blank single line will be placed on top of Details B on preview.

You can also adjust Details A height to match Details B.

In such way, preview is neat.

Here how it looks on each stage on my example report:

Of course, on export of the report to Excel, you may still have empty rows, but that’s better than duplicated data. This can be easily removed in Excel (hidden) using the funnel option in the Data section.

In case you export to CSV, then you got clean data, without duplicates or extra empty lines. This was particularly useful for me, and my solution where I used it.

Thanks to that “trick”, working with sub-report become much easier and not breaking the way, how you would like your preview to looks like.

Regards.