Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How sum a field from _some_ earlier records? Post Reply Post New Topic
Author Message
pbengtss
Newbie
Newbie
Avatar

Joined: 18 May 2016
Online Status: Offline
Posts: 22
Quote pbengtss Replybullet Topic: How sum a field from _some_ earlier records?
    Posted: 22 May 2016 at 9:15am
I need to convert my Excel report to Crystal Reports XI.
I have a query that fetches orders from tables tblOrder and current stock from tblStock.

Desired result:

GroupA:
DelDate (planned delivery date), sorted ascending

GroupB:
CustCode (customer code), sorted ascending (the sorting will be the priority order for deliveries)

Details:
ArtNr (article number), sorted asc
OrdQty (order quantity)
and the challange - a field "NewStock" which is equal to the current stock for this record's article number, minus the sum of all previous order quantities for this article number, including the quantity for the current record. This field will show a negative if ordered quantity up until now is bigger than the actual stock.

So I want this done for each record, no summaries for groups.

This is easy if GroupA is ArtNr. But it isn't...

So any suggestions are welcome. I can change the query or use whatever necessary in Crystal Reports, but I can't figure out how to solve this.

In Excel, I solved it by sorting the table by OrdDate, then CustCode. Then this formula gives me what I want:

=[@Stock]-SUMIF(INDIRECT("$B$2:B" & ROW([@ArtNr]));[@ArtNr];INDIRECT("$C$2:C" & ROW([@ArtNr])))

where column B is ArtNr and column C is OrdQty.
Now, this goes into a Pivot Table where I do the grouping.

Here's the Excel file:
Stock_Order_Report.xlsx

Please help - I'm going nuts!
And be nice, this is the first time I use Crystal Reports, I'm more used to Excel and Access...


EDIT: CHANGED SUBJECT

Edited by pbengtss - 22 May 2016 at 10:38am
IP IP Logged
pbengtss
Newbie
Newbie
Avatar

Joined: 18 May 2016
Online Status: Offline
Posts: 22
Quote pbengtss Replybullet Posted: 22 May 2016 at 10:18am
I'm now thinking maybe some kind of array variable? The maximum number of different dates is 10, and the maximum number of article numbers is around 100.
IP IP Logged
pbengtss
Newbie
Newbie
Avatar

Joined: 18 May 2016
Online Status: Offline
Posts: 22
Quote pbengtss Replybullet Posted: 24 May 2016 at 12:48am
OK, solved it on my own by using two array variables. More questions:

1. Is it really not possible to have a two-dimensional array with different formats?
2. Should I declare the array variables as local? Or something else?
3. Isn't there a smoother way to do this?


First formula in details:

WhileReadingRecords;
NumberVar Array QtyVar;
StringVar Array ArticlesVar;
NumberVar i;

IF NOT ({Command.ArtNr} IN ArticlesVar) THEN
(
    i:=i+1;
    ReDim Preserve ArticlesVar[i];
    ArticlesVar[i]:={Command.ArtNr};
    ReDim Preserve QtyVar[i];
    QtyVar[i]:={Command.Stock }
);
""


Second formula in details:

WhileReadingRecords;
StringVar Array ArticlesVar;
NumberVar Array QtyVar;
NumberVar i;

FOR i:=1 TO Count(ArticlesVar) DO
(
    IF ArticlesVar[i]={Command.ArtNr} THEN
    (
        QtyVar[i]:=QtyVar[i] - {Command.OrdQty};
        EXIT FOR
    );
);
QtyVar[i]
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.