How do you suppress a Group based on a Running Total?
My report takes an initial value from a subreport and then adds it to the values (sorted by a date field) within a group on the main report using a Running Total. If the Running Total goes negative then I increment a numeric flag using the WhilePrintingRecords function. At the end of the group my flag tells me if the running total has ever gone negative during the group. I then reset the flag before starting the next group.
Now I want to suppress (not show) the group when the flag has never been set (ie is still zero) within the group. I tried doing this using the select Group function but Crystal didn't like it because it says the field has to be evaluated later. Makes sense to me, but not what I wanted!
Does anyone have any alternative solutions, please?
PS I'm using Crystal v9 reporting on SQL Server 2000 data
Are you showing the entire detail of the group? I don't think you will be able to suppress the detail based on a value that may or may not occur during the detail.
If you are just showing the header, move it to the footer and do it there.
I was afraid you would say that! But thank you for your input.
My subreport for the initial value is shown in the header and then every detail line is shown with the running total as it happens. So the running total may go negative during one of the detail lines and then my flag is set accordingly (using WhilePrintingRecords).
I would like to see all of the detail lines for any group when the flag is set, but I guess I could, at a pinch, sacrifice the detail lines before it is set. The difficulty I foresee is Crystal needs to evaluate and print the entire line before it can decide to suppress it or not and by then it's too late!
I wonder if there are any alternative approaches thinking outside the box, however I also recognise that it is difficult to get your head round someone else's problem based on a short description. If anyone has ideas and would like to probe the problem then please ask for more details.
my immediate thought was to run the data through access and have an access query run the same calculation. Then have Crystal report on that data and use the already calculated number as the deciding factor.
My solution at this stage is to run a 2nd copy of my main report as a subreport in the group header and return the marker flag which is then used to decide whether to show the lower sections and detail or not.
The only downsides with this approach are:
speed (extra subreport in each group)
small blank line left for each suppressed line (which becomes a big empty space when multiple consecutive lines are suppressed!)
Starkmann - I cannot suppress the narrowest possible line because I need to extract the flag marker from the subreport in order to decide what to do next and if I suppress the section then the subreport is not run and I get no marker!
Unless you know of a sneakier approach? I liked your Microsoft Access approach, but my customer doesn't have it on all their client PC's and anyway the Crystal report is called by another application which isn't capable of running more than one thing at a time, so I need to deliver a single Crystal solution (which, apart from the blank bits, they are happy with).
I spoke too soon. You can't change the underlying SQL. What you can do is create an SQL Expression. You can also use SQL like structures in other formula fields.
You can do join, distinct and all the usual functions.