[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combine two groups maybe Array ?

Posted on 2011-04-19
16
Medium Priority
?
377 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 ?


0
Comment
Question by:mowit
  • 7
  • 5
  • 4
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35429991
Are you looking to get the overall total in the main report or in the subreport?

mlmcc
0
 

Author Comment

by:mowit
ID: 35430038
I'm looking to get totals by groups in the sub report
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35430259
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
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 

Author Comment

by:mowit
ID: 35431682
I'm already getting totals for both groups I want to aggregate both groups they share the same titlegroup.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35431754
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
0
 

Author Comment

by:mowit
ID: 35432034
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
0
 

Author Comment

by:mowit
ID: 35432065
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35433269
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
0
 

Author Comment

by:mowit
ID: 35433321
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35434863
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
0
 
LVL 35

Expert Comment

by:James0628
ID: 35438810
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
0
 

Author Comment

by:mowit
ID: 35439840
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}
));

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35441065
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

0
 
LVL 35

Accepted Solution

by:
James0628 earned 1500 total points
ID: 35449918
You said "collect the descriptions for charges" and then "collect charges".

 Assuming that you actually have a separate set of charge _descriptions_ and you want to collect those like the cost descriptions, the obvious thing would be to use a separate array for the charge descriptions, with similar code to put the charge descriptions in the new array.  The new code could be in a separate formula or in the same formula.

 But if you meant that you want to collect amounts (charges) to go along with those cost descriptions, that's a bit different.  I think that's what mlmcc was going for, but that solution is probably incomplete.  It will only save the first amount for each description.  But since you're checking for (not ({ListOfProfDetailCost.InvTitleGroupRel__Description} in DESC)), the assumption is that the same description can be found in multiple records, in which case you presumably want to accumulate the charges from each of those records.  Try something like this:

whileprintingrecords;
stringvar array DESC;
NumberVar Array Charges;
numbervar counter;

Local NumberVar i;

if not ({ListOfProfDetailCost.InvTitleGroupRel__Description} in DESC) then
(
    counter := Counter + 1;
    if counter <= 1000 then
    (
      redim preserve DESC[counter];
      redim preserve Charges [counter];
      DESC[counter] := {ListOfProfDetailCost.InvTitleGroupRel__Description}
    )
);
for i := 1 to UBound (DESC) do
  if DESC [ i ] = {ListOfProfDetailCost.InvTitleGroupRel__Description} then
  (
    Charges [ i ] := Charges [ i ] + {your charge field};
    exit for
  );


 James
0
 

Author Closing Comment

by:mowit
ID: 35512045
Thank you
0
 
LVL 35

Expert Comment

by:James0628
ID: 35688307
You're welcome.  Glad I could help.

 James
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question