Filter out a calculated formula in group header

wilpitz
wilpitz used Ask the Experts™
on
I have a crystal report that I am trying to see accounts that have balance changes from one date to another date by the Tax ID. On this report I have 4 group levels:

1. Department
2. Account Type
3. Tax ID
4. Account Number

I have the 1st balance and 2nd balance totals for the Tax ID on GH3 and a formula called @Tim Growth that will calculate the balance change between the totals. I want to now somehow filter the @Tin Growth for the records that are over $100,000 but due to Crystal I cant see this field in the record Expert or group Expert.

I know from reading books that crystal is multi pass and I think this is why I cant get this to work but I am fuzzy on the multi pass process.

Any help would be great!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:

Author

Commented:
The formula is a basic sum of the 2 fields:

Sum ({FDM_1.Bank Share Balance (Account)*}, {FDM_1.Primary Customer TIN})-Sum ({FDM.Bank Share Balance (Account)*}, {FDM_1.Primary Customer TIN})

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
In general formulas using summary fields can't be used in groups or the group selection formula.

You might be able to use conditional suppression with the formula from the Section Expert.

mlmcc
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I'm not entirely sure what you're trying to do, but another option might be to use group selection (Report > "Selection Formulas" > Group).  If you only wanted to see the Tax ID groups where the difference between those two Sum's was > 100,000, the selection formula would be:

(Sum ({FDM_1.Bank Share Balance (Account)*}, {FDM_1.Primary Customer TIN})-Sum ({FDM.Bank Share Balance (Account)*}, {FDM_1.Primary Customer TIN})) > 100000


 Note that group selection just controls which groups are shown on the report.  The other groups are still there and will be included in any regular totals and in the group tree, if you're displaying that.  They just won't be shown on the report.

 James
Hmm.  I just realized that mlmcc said that that won't work (I hadn't read his last post carefully).  I think I've used a group selection formula similar to that before, but I could be wrong.  Maybe it won't work.  You can always give it a try and see if CR complains about it.

 James
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you spell it out like you suggest (don't use the formula version) then I believe it can be used.

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Thanks for that.  I was thinking that I'd done something like that, but I couldn't remember the specifics and there was always the chance that I was thinking of something else.

 James

Author

Commented:
That worked!!! I did not think about a formua on the group select expert! I dont know which version this feature started in but it really has been a life saver.
Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial