Sum list of transactions with a variable date on Crystal reporting 11

[Smirre007]

Hi I am new to Crystal reporting and hoping somebody can help with a query.

I am writing a report to report rapid "in and outs" in cash accounts, but am having problems in getting my rapid outs of the account as I need to identify cash that have gone out of the account within five days of the date the cash came in. The trick is that I need to sum the "out transactions" in order to identify the sum of "out transactions" that make up 80% of the "in values" within the 5 day parameter (from date funds came in till the 5th day inclusive) and then need to report the list of outs and ins that meets the criteria above.

I have a list of transactions values and transaction dates. (Example need to sum the "out values" for 1st to the 5th of the month and establish if it makes up 80% of the "in value" on the 1st and then need to sum "out values" for the 2nd to the 6th and compare to the In value for the 2nd to establish if it makes up 80% of the In value etc etc. If the aforementioned meets the criteria the the report needs to through out the transactions that meet the criteria.

 

[lbass]

Please show some sample (mock) data. How do you know what is an "in" value vs. and "out" value? You have only mentioned two fields--value and date. Can we also assume that you would be grouping on account ID?

-LB

 

[Smirre007]

Ibass,

Thanks for the response, You are correct in that I will have different client codes and different cash account number. I have added the client code and Account numbers in the mock data. I distinguish between "Ins" as positive amounts and "Outs" as negative amounts.

I have attempted to put together some mock data. It is in Excel hope that is sufficient. I showed in column G the result I need to get sum of "outs" within five days the funds came into the account, column H show the % out and have highlighted everything in red that I will need to report.

Hope this helps

 

[lbass]

To do this in CR, I think you would have to sort the dates in descending order--is it an option to present the results in this way?

-LB

 

[Smirre007]

Yes, that will be fine

 

[lbass]

Okay, the following solution works as long as there is only one "in" value per day per Entity Code/Account Number. If there can be more than one, then if there is some other field unique to the value (e.g., transaction ID), that could potentially be used to address the resulting issues.

In the main report, you would group on Entity Code and on Account Number and then sort on date ascending (disregard my earlier comment) first and then on {@In} (or the {table.In} field if it is not a formula) descending.

Then save the report under another name and insert it back into the original report as a subreport in the detail section (on the right). Link the sub to the main report on Entity Code, Account Number, Value Date, and {@In}. Let [@In} connect to the amount field initially in the subreport. Then in the subreport, go to report->selection formula->record and change the selection formula to:

{table.EntityCode} = {?Pm-table.EntityCode} and
{table.CashAccountNo}={?pm-table.CashAccountNo} and
{table.valuedate} < {?Pm-table.valuedate}+5 and
{table.valuedate} >= {?Pm-table.valuedate} and
{?Pm-@In} <> 0

In the main report, select the subreport->format subreport->subreport tab->check "suppress blank subreport". Remove the borders from the sub.

Back in the subreport, insert a sum on {@Out} at the AccountNo group level, and drag the result into the group header. Also create a formula {@pct} and add it to the group header:

if {@In} = 0 then
0 else
sum({@Out})%{@In}

Suppress all other sections of the subreport.

I am unclear on your final display requirements. If you only display results where the pct exceeds 80%, then you would be excluding some rows that show additional "in" values during the 5-day period, which would seem to give a skewed view of the actual activity. I could assist with suppression, but need clarification of the desired results.

-LB

 

[Smirre007]

Thank you very much for the above and the result is almost what I need.

It comes down to the 80% and yes you are correct in that I do have additional "in" values during the 5-day period. The result I am getting at the moment is working fine the only thing I can't get rid of is the duplicates where I have more than one "in" on the same day aa well as where there are "ins" with no "outs".

 

[lbass]

Do you mean the ins on the same day should be combined?

If there are ins with no outs, then the result should appear as 0%--what are you seeing?

-LB

 

[Smirre007]

On the first point I am happy with the result; it shows the ins and outs as required, although if for example there are two ins on the same day then I get the same result twice on the report.

On the 2nd point I get the In line with the 0%, although was hoping not to see Ins if there are no Outs.

 

[lbass]

Okay, insert another detail section in the main report and toggle the sections so the blank one is detail_a. Move the subreport up to this section. Format detail_a to "underlay following sections".

In the subreport, go into the section expert->group header->suppress->x+2 and enter:

shared numbervar outx := sum({@out});
outx=0

In the main report, go into the section expert->detail_b.

-LB

 

[Smirre007]

Thanks, the above seems to take care of point 2 above "Not seeing Ins if there are no Outs, although still show blank gaps in the report. Not sure if there are any way of getting rid of the blank spaces. The formulae seems hise all the data but leaves blank gaps on the report.

Point 1 is still a problem where I have for example three Ins on the same date and therefore see the same result three times on the report.

Noticed you started your last line on your reply, although not sure if you finished it or have I missed something.

 

[lbass]

Oops. I meant add the same formula to detail_b->suppress->x+2.

I also said to check "suppress blank section" for both detail_a and detail_b (in my intended reply--not sure what happened).

On Point #1--please show in this thread what you are seeing for three in's on the same day. Are the percentages actually correct? I don't see how they could be unless all in's were the same value.

-LB

 

[lbass]

To address the issue of multiple 'ins' on the same date, I think you should use a group sum for the date for ins.

Make these changes:

In the main report, insert a group on date (on change of day). This will be group #3. Insert a second group section, and move the subreport to group header #3_a, and move the groupnames for group 1, 2, and 3 to GH3_b. Insert a sum on {@In} at the date g roup level and drag the result to GH3_b.

Format GH3a to "underlay following sections" and for mat both GH3a and GH3b to "suppress blank section". Format GH3b to suppress with a formula:

shared numbervar outx;
outx = 0 or
sum({@In},{table.date})=0

Suppress other main report sections except the detail section.

In the subreport, remove the percentage formula. The sub should now only show the sum of {@out} in GH#2. All other sections are suppressed.

Format GH#2 in the sub to suppress with this formula:
shared numbervar outx;
outx = 0

In the main report, create a percent formula like this:

shared numbervar outx;
if sum({@In},{table.date}) = 0 then
0 else
outx %sum({@In},{table.date});

Place this in GH#b to the right of where the underlaid sum of {@Out} will appear from the sub.

-LB