If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Crystal Reports > How do you suppress a Group based on a Running Total?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
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?

Thanks,
Caspar

PS I'm using Crystal v9 reporting on SQL Server 2000 data
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2007
Posts: 348
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
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.

Many thanks,
Caspar
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2007
Posts: 348
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.
Reply With Quote
  #5 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,000
Just a quick question starkmann....
Can you edit the underlying SQL of a Crystal Report? Humour me
__________________
George
Home | Blog
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
Many thanks for your suggestions.

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:
  1. speed (extra subreport in each group)
  2. small blank line left for each suppressed line (which becomes a big empty space when multiple consecutive lines are suppressed!)
Cheers,
Caspar
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2007
Posts: 348
Quote:
Originally Posted by georgev
Just a quick question starkmann....
Can you edit the underlying SQL of a Crystal Report? Humour me
yes sir, you can.

Caspar, why do you have a blank line for a suppress row? Aren't you suppressing it with the section expert?
Reply With Quote
  #8 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,000
How do you change the underlying SQL?
It seems to me that many problems could be solved if you wrote the correct SQL in the first place!
__________________
George
Home | Blog
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
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).

Thanks again for all your wisdom.

Caspar
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2007
Posts: 348
George,
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On