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

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]