Create summary in report footer by omitting suppressed records

Question

I have a group header section in a report which displays a summary total from a formula field called Personal,

if not IsNull({AirBilling.BillingCode}) and {AirBilling.BillingCode} = 'P' then
    {AirBilling.Gross}
else
    0

Now at the end of the report in the Report footer I display a summary total of all the Personal totals calculated in the group header section. Now I have a subreport that filters certain records and depending on the count returned by the subreport, I suppress records in my main report. My problem is that, my main report considers counts of suppressed records also in the Footer total. When I try to put a condition in formula field logic above that says if RowCount>0 then..(if not IsNull....), but when I try to run the report it gives me an error that says 'A summary has been specified on a non-recurring field'. Is there a way I can omit the suppressed records to be calculated in the footer section. RowCount is the shared variable value returned by subreport which I am using to filter records in main report.

this sounds pretty hideous- can you maybe step back and explain what the subreport is doing and we can suggest how you maybe able to achieve the same result without it? using shared variables for this sort of task can really complicate even the most trivial of tasks — Lee Tickett
Actually, the subreport is comparing masked data from the database and the parameter the user is allowed to enter in the report. There is no workaround for not using a subreport as that is how a stored procedure is created for unmasking data. So if there is any other way to fix this other than not using a subreport that would be great. — developer
The not IsNull({AirBilling.BillingCode}) condition is redundant in the above expression - if {AirBilling.BillingCode} is 'P', then by definition it is not null. — user359040
In Crystal Reports he probably wrote the 'not IsNull' first because a typical C# routine would throw an 'Object not set...' if it attempted to evaluate {AirBilling.BillingCode} while it was null. Even if Crystal doesn't fail when evaluating nulls I can appreciate why he did that. <smile> — Dylan - INNO Software

Unknown Unknown · Accepted Answer · 2012-05-04T06:44:17

As I understand it, you are trying to run a subreport to determine whether certain rows in the main report should be excluded (both from display and from totals). There are two approaches you could use to resolve this:

Replicate the logic from the subreport in the query / stored procedure for the main report, so that the relevant records are excluded before they reach Crystal. No subreport will be required.
Swap the subreport and the main report around (assuming that no data from what is currently the main report is required in what is currently the subreport, apart from the user-entered parameters) and pass the relevent data from what is now the main report to what has become the subreport, as a parameter.

I recommend the former approach, as Crystal is better as a tool for formatting data than for manipulating it - SQL is generally a much better data manipulation tool.

Create summary in report footer by omitting suppressed records

2 Answers