Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Array Formula Post Reply Post New Topic
Author Message
TuskJC
Newbie
Newbie


Joined: 03 Sep 2020
Online Status: Offline
Posts: 1
Quote TuskJC Replybullet Topic: Array Formula
    Posted: 04 Sep 2020 at 8:47am
All,

I'm having trouble finding a way to do what I want in a group.

I currently have a group list of customer names (we'll say ("AA", "BB", "CC") for example).

I have another group above that that shows invoicing date by month so it looks kind of like this

July 2020
   "AA"
   "BB"
   "CC"

August 2020
   "AA"
   "CC"
   "DD"
   "EE"

In the above case, customer "AA" and "CC" were invoiced July and August, "BB" dropped after July and "DD" and "EE" were added in August.

What I'm trying to find a solution for is how to show a count of how many customers were dropped or added in any given month (aka August would show +2 adds, -1 drop). I feel like an array with formulas in the header, details and footer would do this, I just don't know where to go with it.

Any help is appreciated.

Edited by TuskJC - 04 Sep 2020 at 8:54am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 09 Sep 2020 at 5:27am
My solution is to use shared variables and strings. You stated that there is a grouping by the month. I would put something like:
shared stringvar thisMonth := "";
""
in the group header. this initializes which entries were added this month.

In the detail, my formula would look something like this:
shared stringvar thisMonth;
shared stringvar overall;
if instr(overall, {table.field})=0 then(
thisMonth := thisMonth + {table.field};
overall := overall + "," + {table.field};
);
""

This finds the new/added entries, and updates what the list of entries is.

Finally, in the group footer, we need to find the drops:
shared stringvar overall;
shared stringvar thisMonth;
shared stringvar dropped := "";
local stringvar array allEntry := split(overall, ",");
local numbervar i;
for i=1 to ubound(allEntry)( //I don't remember the syntax off the top of my head
if instr(thisMonth, allEntry) = 0 then(
    dropped := dropped + "," + allEntry;
    allEntry := "";
)

);

overall := join(allEntry, ",");
""

this will find the dropped and reset the list to start the next month.

Finally in the displays it would be simply:
shared stringvar thisMonth //this shows the adds

This should give a roadmap of how I would approach the issue. It should work. It might need some tweaking.

HTH
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 09 Sep 2020 at 5:29am
all of the allEntry in the solution should have a open bracket i close bracket...they might be parenthesis...the display took the "" to be italics

better yet, change all the allEntry to allEntry[j] and the local numbervar i to a j
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.