Array Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22891
Printed Date: 29 Apr 2024 at 6:28am
Topic: Array Formula
Posted By: TuskJC
Subject: Array Formula
Date 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.
|
Replies:
Posted By: lockwelle
Date 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
|
Posted By: lockwelle
Date 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
|
|