Suppress printing only on some groups

[kthacher]

Using CR 8.5

I have a report with several groups: Accounting_Period, Sub-Account, Account, and Customer_Type. Accounting_Period and Sub-Account are hidden. For presentation purposes, the report needs to show Accounts within Customer_Type for the first two Customer_Types but hide the Accounts for the last two Customer_Types.

I made a formula in the group format section "Suppress" area that effectively suppresses the Account group footers for these last two Customer_Types. However, I have a variable collecting in @accum formulas and resetting in the Account group header and I also have a running total resetting at Accounting_Period change. With the Account group suppressed, these two formulas don't accumulate and reset properly.

Is there a way to keep a group footer from printing (conditionally) but still have it create the groups I want?

 

[synapsevampire]

OK, using the term printing is probably a bad idea, I think you mean displaying.

And stating that you have a formula but not what's in it isn't helpful, we need to know.

If you want only the values in the first 2 detail rows of a given group, then consider the NEXT function within formulas for display and for creating aggregates.

So in the group header you'd use something like the following formula, although you would suppress the group header and the details;

whileprintingrecords;
stringvar MyString:="";
if next({table.groupfield}) = {table.groupfield} then
MyString := {table.stringfield} + chr(13) + next({table.stringfield});
else
MyString:= {table.field}

The output display would be in the group footer as
whileprintingrecords;
stringvar MyString:

And format it to can grow.

To accumulate different aggregates, create 2 fields as in:

whileprintingrecords;
numbervar MyValue1:=0;
if next({table.groupfield}) = {table.groupfield} then
MyValue1 := MyValue+{table.value} + next({table.value})
else
MyValue1 := MyValue+{table.value}

Duplicate this with it's own running Total or whatever the aggregate is using unique variable names.

Now you can display the values in the group footer as:

whileprintingrecords;
numbervar MyValue1

This should give you the basic premise of the NEXT function and manually creating aggregates.

-k

 

[kthacher]

Thanks for the help on aggregating, but I think my problem is that the order of my data changes in the section where I have suppressed the Account grouping so things aren't aggregating correctly.

Here is how the report is organized. These are all Group Footers. Details are hidden. This is for illustration purposes only. On the real report Period totals (GF4) and Sub-Account totals (GF3) are hidden as well.

Period 1 total
Period 2 total
Period 3 total
Sub-account 1 total
Period 1 total
Period 2 total
Period 3 total
Sub-account 2 total
Account "Big Homes" total
Period 1 total
Period 2 total
Period 3 total
Sub-account 1 total
Period 1 total
Period 2 total
Period 3 total
Sub-account 2 total
Account "Small Homes" total
Customer Type "Homes" total
Period 1 total
Period 2 total
Period 3 total
Sub-account 1 total
Period 1 total
Period 2 total
Period 3 total
Sub-account 2 total
Account "Big Business" total
Period 1 total
Period 2 total
Period 3 total
Sub-account 1 total
Period 1 total
Period 2 total
Period 3 total
Sub-account 2 total
Account "Small Business" total
Customer Type "Business" total
//Here is where the report must not display Accounts
Period 1 total (for all "Big Farms" and "Small Farms")
Period 2 total (for all "Big Farms" and "Small Farms")
Period 3 total (for all "Big Farms" and "Small Farms")
Sub-account 1 total (for all "Big Farms" and "Small Farms")
Period 1 total (for all "Big Farms" and "Small Farms")
Period 2 total (for all "Big Farms" and "Small Farms")
Period 3 total (for all "Big Farms" and "Small Farms")
Sub-account 2 total (for all "Big Farms" and "Small Farms")
Customer Type "Farms" total

My formula to suppress the Farms Accounts is in the group format section area as: If Customer_Type = "Farms" However, by doing this, Account Big Farms and Account Small Farms are lumped together in each Period.

In addition to the data shown above, there are actuals and budgets for each detail record which are totalled in these footers. Because there are multiple actuals but only one budget per Sub-account per Period, the budget is retrieved multiple times from the database--once for each actual record. In order to aggregate the budget, it needs to add the new value at each change in Period within Sub-Account. This works fine in the "Homes" and "Business" Customer_Types, but in "Farms" the grouping for Accounts is suppressed so they are lumped together within a Period/Sub-Account grouping. I am missing the collection of some budgets because the Period didn't change but the Account did.

I am using aggregating formulas (provided previously by llbass) similar to the ones shown above in your posting.
//@accum
whileprintingrecords;
numbervar sumbudg :=
sumbudg + maximum({@CurrentBudgetRev},{DBGLGLT.ACCT_PERIOD})

However, this depends on there only being one Account in each period. It works fine for "Homes" and "Businesses" but not for "Farms" where multiple Accounts are lumped together.

Bottom line question is--how can I aggregate when the groupings aren't the same throughout the report? OR...How can I make the groupings be the same but only DISPLAY what I want to see?

 

[synapsevampire]

Now you reference another formula and don't share what's in it:

{@CurrentBudgetRev}

Perhaps the original thread was the way to go, as it's frustrating to have you come and change the requirements each time and reference unknowns.

Initially you asked to accumulate the first 2 rows of a group, which I supplied, now it all changed.

I would suggest that you show example data and expected output rather than trying to explain it or use mock outputs without knowing what the underlying data was.

-k

 

[kthacher]

No, I did not change any requirements. I did not ask to accumulate 2 rows of a group. I want to display Account footers (GF2) for the first two Customer_Types (GF1) but not for the last two Customer_Types, as I stated in my original post above and my second post. I want to do this while still maintaining the actual groups so my data will accumulate correctly.

The formula behind @CurrentBudgetRev is irrelevant or I would have included it. It is simply a value to be accumulated.

I did re-post my original thread last week and received no response so I figured this would simplify by narrowing it down to this one request.

Here is my example data:

Account Sub-Account Period Actual Budget
Big Homes 1 1 100 400
Big Homes 1 1 200 400
Period 1 total 300 400

Big Homes 1 2 200 100
Big Homes 1 2 300 100
Period 2 total 500 100

Big Homes 1 3 100 300
Big Homes 1 3 300 300
Period 3 total 400 300

Sub-account 1 total 1200 800

Big Homes 2 1 100 400
Big Homes 2 1 200 400
Period 1 total 300 400

Big Homes 2 2 200 100
Big Homes 2 2 300 100
Period 2 total 500 100

Big Homes 2 3 100 300
Big Homes 2 3 300 300
Period 3 total 400 300

Sub-account 2 total 1200 800

Account "Big Homes" total 2400 1600

Small Homes 1 1 100 400
Small Homes 1 1 200 400
Period 1 total 300 400

Small Homes 1 2 200 100
Small Homes 1 2 300 100
Period 2 total 500 100

Small Homes 1 3 100 300
Small Homes 1 3 300 300
Period 3 total 400 300

Sub-account 1 total 1200 800

Small Homes 2 1 100 400
Small Homes 2 1 200 400
Period 1 total 300 400

Small Homes 2 2 200 100
Small Homes 2 2 300 100
Period 2 total 500 100

Small Homes 2 3 100 300
Small Homes 2 3 300 300
Period 3 total 400 300

Sub-account 2 total 1200 800

Account "Small Homes" total 2400 1600

Customer Type "Homes" total 4800 3200

Big Business 1 1 100 400
Big Business 1 1 200 400
Period 1 total 300 400

Big Business 1 2 200 100
Big Business 1 2 300 100
Period 2 total 500 100

Big Business 1 3 100 300
Big Business 1 3 300 300
Period 3 total 400 300

Sub-account 1 total 1200 800

Big Business 2 1 100 400
Big Business 2 1 200 400
Period 1 total 300 400

Big Business 2 2 200 100
Big Business 2 2 300 100
Period 2 total 500 100

Big Business 2 3 100 300
Big Business 2 3 300 300
Period 3 total 400 300

Sub-account 2 total 1200 800

Account "Big Business" total 2400 1600

Small Business 1 1 100 400
Small Business 1 1 200 400
Period 1 total 300 400

Small Business 1 2 200 100
Small Business 1 2 300 100
Period 2 total 500 100

Small Business 1 3 100 300
Small Business 1 3 300 300
Period 3 total 400 300

Sub-account 1 total 1200 800

Small Business 2 1 100 400
Small Business 2 1 200 400
Period 1 total 300 400

Small Business 2 2 200 100
Small Business 2 2 300 100
Period 2 total 500 100

Small Business 2 3 100 300
Small Business 2 3 300 300
Period 3 total 400 300

Sub-account 2 total 1200 800

Account "Small Business" total 2400 1600

Customer Type "Business" total 4800 3200


Small Farms 1 1 100 400
Big Farms 1 1 200 400
Period 1 total 300 800

Small Farms 1 2 200 100
Big Farms 1 2 300 100
Period 2 total 500 200

Small Farms 1 3 100 300
Big Farms 1 3 300 300
Period 3 total 400 600

Sub-account 1 total 1200 1600

Small Farms 2 1 100 400
Big Farms 2 1 200 400
Period 1 total 300 800

Small Farms 2 2 200 100
Big Farms 2 2 300 100
Period 2 total 500 200

Small Farms 2 3 100 300
Big Farms 2 3 300 300
Period 3 total 400 600

Sub-account 2 total 1200 1600

Customer Type "Farms" total 2400 3200

The Small Farms and Big Farms are grouped together because I have suppressed the Account Footer for the Farms Customer_Type.

I would like the report to look like this:


Account "Big Homes" total 2400 1600
Account "Small Homes" total 2400 1600
Customer Type "Homes" total 4800 3200

Account "Big Business" total 2400 1600
Account "Small Business" total 2400 1600
Customer Type "Business" total 4800 3200

Customer Type "Farms" total 2400 3200

Grand Total 12,000 9600

I am accumulating the Budget with the formula @accum as detailed in my previous post. However, this formula depends on there being only one budget per period. In the Farms section, there are multiple budgets per period because the Account grouping is suppressed. But if I don't suppress it, I don't meet the requirement of NOT displaying Farm Accounts.

If this is not clear, then I don't have a better way to explain it. I have tried to make it as complete and clear as possible.

 

[synapsevampire]

This doesn't say the first two of a group?:

"I have a report with several groups: Accounting_Period, Sub-Account, Account, and Customer_Type. Accounting_Period and Sub-Account are hidden. For presentation purposes, the report needs to show Accounts within Customer_Type for the first two Customer_Types but hide the Accounts for the last two Customer_Types."

They are groups, and you want the first two, how does that differ?

Anyway I doubt that your example data has summaries already in it, what you've posted is an example of some report output, and then an example of a prefered report output, when posting example data would mean showing what is in the tables, not after you've already applied groups, code, etc.

You can manually create summaries, and suppression won't prevent them from being used.

So if there's something that needs to be summarized in some group, use the 3 formula method:

group header:
whileprintingrecords;
numbervar MyTot:=0

Inner section (another group or the details)
whileprintingrecords;
numbervar MyTot;
MyTot:=MyTot+<some table.field or some inner group value>

Group Footer, you display here:
whileprintingrecords;
numbervar MyTot:=0

Note that within the inner group you can also conditionally qualify what is summarized (or counted or whatever aggregate you need) by using IF statements.

And you can also have an additional variable in the group footer that would accumulate this value into a variable used in an outer section.

Since you don't know how to accomplish what is required, I think that you should allow others to determine what is irrelevent as in "The formula behind @CurrentBudgetRev is irrelevant or I would have included it. It is simply a value to be accumulated.", and it probably took you longer to type that than to simply copy and paste it here.

If the theory explained doesn't help you resolve, perhaps lbass will pop by and recall your other thread and offer up something that resonates with you, I think that your requirements have been met though.

-k

 

[lbass]

You could try changing the accum formula. I'm unclear on what the group is that has field results "small farm" and big farm", so I'll just refer to it as {table.grplevel} to:

//@accum
whileprintingrecords;
numbervar sumbudg;

if {table.grplevel} in ["Small Farm","Big Farm"] then
sumbudg :=
sumbudg + maximum({@CurrentBudgetRev},{DBGLGLT.ACCT_PERIOD})+
minimum({@CurrentBudgetRev},{DBGLGLT.ACCT_PERIOD}) else
sumbudg :=
sumbudg + maximum({@CurrentBudgetRev},{DBGLGLT.ACCT_PERIOD});

This assumes that there are only two possible budget figures per period for the farms category.

-LB