[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 851
  • Last Modified:

Suppress Entire Group based on a Formula (or redesign my report)

Hi
Sorry this is a long description, but I thought it would be better to describe my whole situation, rather than just what I need to solve now, just in case there is a better way to solve the whole report. or whether you think there is a major flaw in my report.

I have an M to M data table which contains links between loan accounts and securities. So each loan can have many securities and each security can be attached to many loans.

These links are divided into 4 categories (to work out the percentage between loans and securities, to show the attached securities will cover the loan):
- One Loan to One Security
- One Loan to Many securities
- One Security to Many loans
- Many loans to many securities
For the first 3 the percentages can be worked out using a formula in the report, for the 4th one, someone will work it out manually.

In Access, I have run a few queries to flag which accounts belong to each of these categories.
In Crystal Reports 11, I have a report which groups by account, then in the details it shows each security it is attached to and each account that security is attached to.
eg.
Account 111
---Security 911, Account 111
---Security 922, Account 111
---Security 911, Account 222 (this security is attached to 2 accounts.)
Account 222
---Security 911, Account 222
---Security 911, Account 111
Account 333
---Security 933, Account 333

See how Account222 is listed in the detail of Account111?
I need my report to Not display the group Account222 because it is already been used in group Account111.
So the whole group and all its detail should not be shown if that account has already been displayed on the report.

What I have already tried is to add each account from the detail line into an array:
whileprintingrecords;
numbervar array MyArray;
numbervar Counter;
if not({dbo_tblAccountsBySecurity_1.accountNo} in MyArray) then
( Counter := Counter + 1;
  if Counter <= 1000 then (
    Redim Preserve MyArray[Counter];
    MyArray[Counter] := {dbo_tblAccountsBySecurity_1.accountNo}
  )
);
Because Crystal has a limitation of 1000 elements in an array, I have used 6 arrays (with very little performance decrease.)

In the section expert I can colour in the Group headers (or suppress them) based on whether the accountNo in the Group header is in the array at the time that section is printed. I have also created a formula which displays some text if it meets this criteria.

What I need to be able to do here, is to suppress the entire group, not just the header.

Any help will be greatly appreciated.
Any suggestions of how to do this completely differently will also be looked into.

Thanks
0
aimeec
Asked:
aimeec
  • 3
  • 2
1 Solution
 
Brian CroweDatabase AdministratorCommented:
I think you need to alter your query so that accounts are classified by (1-1, 1-M, M-1, M-M) before coming into Crystal.  Then group by that value before the Account.
0
 
mlmccCommented:
Try using the same formula that suppresses the group header to suppress the detail section.

You could also set a global variable to equal the suppression formula then use that to suppress the detail section.

mlmcc
0
 
aimeecAuthor Commented:
Thanks for responding.
BriCrowe: Could you explain how that would help?
I can see that it would be useful, but not how it will help solve my issue.

mlmcc: using the same formula to suppress the detail isnt working properly. It suppresses the details from all of the groups, not just the ones I want.
Is it possible to suppress the details based on whether the group header is suppressed(or coloured in)?

Im am trying to create a global var but its not doing what I want either.
I have added to my formula a variable called suppressMe:
whileprintingrecords;
numbervar array idarrayCS;
numbervar array idarrayCSII;
numbervar array idarrayCSIII;
numbervar array idarrayCSIV;
numbervar array idarrayCSV;
numbervar array idarrayCSVI;
booleanVar suppressMe;
if OnFirstRecord or {dbo_tblAccountsBySecurity.accountNo} <> Previous({dbo_tblAccountsBySecurity.accountNo}) then
    suppressMe := false;
if ( {dbo_tblAccountsBySecurity.accountNo} in idarrayCS
    or {dbo_tblAccountsBySecurity.accountNo} in idarrayCSII
    or {dbo_tblAccountsBySecurity.accountNo} in idarrayCSIII
    or {dbo_tblAccountsBySecurity.accountNo} in idarrayCSIV
    or {dbo_tblAccountsBySecurity.accountNo} in idarrayCSV
    or {dbo_tblAccountsBySecurity.accountNo} in idarrayCSVI
) then (
    suppressMe := true;
    "Already Displayed." & suppressMe
)else totext(suppressMe)

When This formula is in the group header, it works shows true or false when its supposed to, but when it is in the details, it always says true (even if the header says false).
If the detail can say the same thing as the header, then I should be able to use this formula to suppress both the details and header.

Any ideas of what I need to change in my formula?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
As far as I know you cannot directly get at the fact that a group is colored or suppressed.  You can however set variables in a formula and use them in other formulas.

For example in the group suppression
Global BooloeanVar IsSuppressed;
IsSuppressed := Your Suppression Formula

Then for the details suppression
Global BooloeanVar IsSuppressed;
IsSuppressed

mlmcc
0
 
aimeecAuthor Commented:
Its working!!
mlmcc:
I had that from your first response but because I had so many formulas and fields floating around from all the things I was trying it wasnt working. So I started a new report.

So the complete answer for anyone else looking:
In the group header, I put the formula from above, which declares the suppressMe variable(as global) and sets it to true if it has already been put into the arrays at the time that record is printed.
also it resets the variable to false onchange of record(not really necessary, but I will probably need this section for resetting a running total at some point).

Then in the details section I have another formula which just gets the value of the variable:
whileprinting records;
global booleanvar suppressMe;
suppressme;

Then in the section expert for both the group header and detail I put:
WhilePrintingRecords;
global booleanVar suppressMe;
if suppressMe = true
then true

Huzzar! (Me doing a small dance of joy)
0
 
aimeecAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now