AR Days by Month Calculation

[mdcson]

I need to convert an AR Days report that I did in Crystal Reports over to Cognos Report Studio. Most of what I need for the report is complete aside from the last calculation which was quite complex in Crystal. User lbass helped me with this great code for the Crystal report which worked very nicely. How would this be done in Report Studio>


Crystal Reports Code:

whileprintingrecords;
numbervar array rev;
numbervar array days;
numbervar i;
numbervar j;
numbervar x := 0;
numbervar y := 0;

if onfirstrecord then (
redim rev[1];
redim days[1];

) else
(
redim preserve rev [ubound(rev)+1];
redim preserve days [ubound(rev)+1]
);
j := ubound(rev);
days [j] := sum({table.days},{table.date},"monthly");
rev[j] := tonumber(sum({table.monthlyrevenue},{table.date},"monthly"));
if j < 4 then (
x := sum(rev);
y := sum(days)
) Else
(
for i := j-2 to j do (
x := x + rev;
y := y + days
);
y;
);
y

Then create a second formula to display the result (also in the group footer section):

evaluateafter ({@accum});
whileprintingrecords;
numbervar x;
numbervar y;
{@endingbalance}/(x/y)

I worked this out in a detail section and then adjusted it for a group footer section, so haven't fully tested it. This formula is adapted from one by bdreed.

If you don't want the calculation to display in the first two group footers, use a suppression formula like this:

groupnumber < 3

...assuming you only have the one monthly group.

-LB