Results 1 to 10 of 10
  1. #1
    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

  2. #2
    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.

  3. #3
    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

  4. #4
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    Just a quick question starkmann....
    Can you edit the underlying SQL of a Crystal Report? Humour me
    George
    Home | Blog

  6. #6
    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

  7. #7
    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?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    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

  9. #9
    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

  10. #10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •