We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Combine two groups maybe Array ?

mowit
mowit asked
on
Medium Priority
417 Views
Last Modified: 2012-05-11
here is my break down:

I have a sub-report with two  groups using two formulas.

Groups 1
if isnull({somefield1.titlegroup}) then
{somefield1.default}
else
{somefield1.titlegroup}

Group 2
if isnull({somefield2.titlegroup}) then
{somefield2.default}
else
{somefield2.titlegroup}

header // Resetting the amounts costamt = 0 and chramt = 0

I'm using these formulas to calculate amounts for both groups
Detail section has  numbervar chrgamt := chrgamt + {somefield.amt}
same for the other group
numbervar costamt := costamt + {somefield.amt}

In the group footers I'm displaying the result of the group formula and the result of the calculations
i.e in the footer of
\\footer of group 1
whileprintingrecords   and @costamount
numbervar costamt;

\\footer of group 2
whileprintingrecords  and @chargeamount
numbervar chrgamt;

I would like to aggregate both groups based on the grouping formulas and summarize the amounts according. What the best way to accomplish this ?


Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you looking to get the overall total in the main report or in the subreport?

mlmcc

Author

Commented:
I'm looking to get totals by groups in the sub report
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Same basic way you are getting the group totals.  You need variables for the aggregated total

When you say there are 2 groups do you mean you have
Group Header 1
Group Header 2 - reset totals to 0
  Details
Group Footer 2 - Display totals
Group Footer 1

Are you looking for total for group 1 or for the entire subreport?

mlmcc
mlmcc

Author

Commented:
I'm already getting totals for both groups I want to aggregate both groups they share the same titlegroup.
CERTIFIED EXPERT

Commented:
Do you have multiple groups in the subreport?  Something like:

Group 1 - A
  Group 2 - 1
  Group 2 - 2
  Group 2 - 3
Group 1 - B
  Group 2 - 2
  Group 2 - 3


 If so, what, exactly, are you trying to "aggregate"?

 Are you trying to combine the Group 2 totals from the different Group 1's, like combined totals for G2-1, G2-2 and G2-3 from G1-A and G1-B?  Or maybe a list of the totals from each group, like a list of all of the Group 1 totals or all of the Group 2 totals?  Or something else?

 James

Author

Commented:
James,

Yes i have groups I would like to see combined into one group based on the title group. I have attached screenshots. Let me know if you have any further questions.
Capture.JPG
Subreport.PNG

Author

Commented:
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Add variables to the

Global NumberVar GroupTotalCost;
Global NumberVar GroupTotalCharge;

Reset them in the appropriate group header

IN the formulas doing the totals
GroupTotalCost := GroupTotalCost + {SOmeField};

similarly for the other one

Just display them in the appropriate footer.

mlmcc

Author

Commented:
mlmcc,

not sure if this answers my question entirely. I'm already  summarizing the groups. Both groups can have a common title therefore I would like to aggregate them if the titles are the same.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Group 1 - Other Charges
Group 2 - Charge or Cost group
Group 3 - Expense type?

Will the expense types always be together like that?

Can you regroup as
Group 1 - Other Charges
Group 2 - Expense type?
Group 3 - Charge or Cost group

With that grouping my aggregation will work.

mlmcc
CERTIFIED EXPERT

Commented:
Is it just "Travel, Meals & Lodging", or could one of the other lines (groups), like "Outside Services", also show up more than once and need to be combined into one line?

 If it could be more than one, will there just be a small number of them, and do you know what they will be?

 If there could be a lot of different groups that need to be combined, then maybe arrays would be a solution (as you originally suggested).  For example, you could save the group names, like "Outside Services", in one array, and a total for each name in a second array.

 Depending on exactly where you want these figures, and how much data you have, another option might be another subreport.  Create a new subreport that reads the same data and produces similar totals, but only groups by the second group field, so that all of the "Travel, Meals & Lodging" records, for example, are in one group.  That might be a simpler option, as long as it doesn't hurt the performance too much, which would depend mainly on how much data the subreport is reading.

 James

Author

Commented:
James,  I was able to create an array to collect descriptions for cost, is there away to extend this array to collect the descriptions for charges as well ? Also I'm having some difficulty with the second array, can you provide a code snippet ?

This works as expected but would like to extend to collect charges as well.

whileprintingrecords;
stringvar array DESC;
numbervar counter;
if not({ListOfProfDetailCost.InvTitleGroupRel__Description}in DESC) then
(
    counter := Counter + 1;
    if counter <= 1000 then (redim preserve DESC[counter];
    DESC[counter] := {ListOfProfDetailCost.InvTitleGroupRel__Description}
));

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Add a second array and update it at the same time

mlmcc
whileprintingrecords;
stringvar array DESC;
NUMBERVAR ARRAY COST
numbervar counter;
if not({ListOfProfDetailCost.InvTitleGroupRel__Description}in DESC) then
(
    counter := Counter + 1;
    if counter <= 1000 then 
    (
      redim preserve DESC[counter];
      DESC[counter] := {ListOfProfDetailCost.InvTitleGroupRel__Description};
      REDIM PRESERVE COST[COUNTER];
      COST[COUNTER := {YOUR COST FIELD};
));

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 James
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.