Manual Cross Tab

[ganjass]

CR 8

Hi this is a problem that i have reported previously but have not manged to crack yet.

A manual cross tab has been created to reflect performance of batch accounts over a 12 month period. This is within the date created (dt-datinstr) by month group. The details section within the group has 12 formulas for the date diff as so:

@Placement Month Start:
Date(Year({debt.dt-datinstr}),Month({debt.dt-datinstr}),1)

@month1
if Datediff("m",{@Placement Month Start},{debt-trans.tx-date})=0 and
{debt-trans.tran-code} startswith "DR1","DR3","DR4","DR5","DR309" then {debt-trans.tx-amount}
else
0

etc until the last month 12.

The report has to be cumulative with the sums of the datediffs in the group footer. Ie. if there are no new DR codes for that month\ datediff the value keeps going until the max date diff is reached afterwhich it should be suppressed so a batch for say Aug-03 that has a date diff of 6 has to stop at 6. But this is the headache. I havn't managed to accurately suppress by the datediff value. SO certain months keep going on past the datediff max, and some go no where near the date diff max.


0 1 2 3 4 5 6 etc
Jul-03 177.67 537.84 937.12 1757.48 2346.77 2642.15 2642.15
Aug-03 0 0 33.00 257.10 284.10 284.10 284.10
Sep-03 0 0 20.00 44.00 44.00 44.00 44.00
Oct-03 10 110.00 110.00 110.00 110.00 110.00 110.00
Nov-03 15 105 105 105 105 105 105
Dec-03 10 10 10 10 10 10 10


Cheers

 

[synapsevampire]

I've blinked too many times at this post and it just keeps appearing more bizarre.

So you have data grouped by months, and then show months as 0 through 6 as well?

Or are those the batch accounts?

BTW, don't even create the @month start formula (adjusts to the 1st) as you're only doing a datediff by month, it makes no difference.

And why are you trying to suppress anything?

If you only want 12 months worth of data, filter the rows being returned in the Report->edit selection formula->record:

datediff(&quot;m&quot;,{debt.dt-datinstr},{debt-trans.tx-date}) < 13

You might require a SQL Expression to properly pass this to the database, but this would be database dependent, which you didn't share.

If you require additional help, please post example data and the database being used.

-k

 

[ganjass]

Sorry about that! the spec has unraveled in a cryptic twin peaks kinda way.

DB progress 9.1C

in a nutshell:

The grouping is by datinstr. (the date of creation)

The datediff is between the datinstr (date of creation) and tx-date, which is the date of payments with the DR codes.

The payments are to appear by month like a cumulative running total across the cross tab. If there are no payments for that month then it repeats the previous months sum. Which is not a problem.

The problem is that the rows are to be formatted so that if say the report was run in january for the previous 12 months, then the month December should only display 1 months value, ie the date diff is 1, so it will only have 1 value in column 1, If the last new accounts were in october then the date diff would be 3 so it should have 3 values displayed and so forth eg



1 2 3 4 5 6 7 8 9 10 11 12 ->
Aug-03 5 5 10 10 15
oct-03 0 5 5
Dec-03 10

would be the ideal pattern so you get a pattern like:
XXXX
XXX
XX

using < and > with the diffs allows the values to be repeated across the rows where there are no payments as required, its getting it to stop precisely thats the issue.

but without control of the date diff values the report looks like

1 2 3 4 5 6 7 8 9 10 11 12 ->
Aug-03 5 5 10 10 15 15 15 15 15
oct-03 0 5 5 5 5 5 5 5
Dec-03 10


but trying to conditionally suppress, or make it more rigid to adhere to the logic without suppression is proving tricky.

I've tried suppressing the values by trying suppression with
Sum ({@CPM08}, {debt.dt-datinstr}, &quot;monthly&quot = 0 and
Sum ({@CPM09}, {debt.dt-datinstr}, &quot;monthly&quot = 0 and
Sum ({@CPM10}, {debt.dt-datinstr}, &quot;monthly&quot = 0 and
Sum ({@CPM11}, {debt.dt-datinstr}, &quot;monthly&quot = 0 and
Sum ({@CPM12}, {debt.dt-datinstr}, &quot;monthly&quot = 0

and the like but this is not an accurate solution.

Does this shed any more light on it

Sorry for the confusion on this one,I appreciate this has been a cryptic one, and i appreciate any efforts to help.

Thanks in advance.