Cross Tab Report

[mikeycan]

I have data in an amortization table that list amounts to be recognized each
period for a given invoice. I would like a report that would show how much
revenue by invoice per month for next twelve months based on when its run and
any amounts over 12 months summed in a “Long Term†column. This report would
be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120
buckets, but I need the “buckets†to be dynamic.

Invoice 4/1/07 5/1/07 6/1/07 7/1/07 8/1/07 9/1/07 10/1/07 11/01/07 12/01/01 1/1/08 2/01/08 3/1/08 LT


The fields I have are
Field Name Field Type Sample Data
InvoiceNumber Text I23037
RevenueMethod Text Ratable
Amortization Period Date/Time 4/1/07
Amortization Invoice Amount Number 7,258.58
Amortized Cost Amount Number 1,458.58

Can this be done? I am not a programmer, so I can only do very simple
things with SQL or VB. Thanks in advance for your help.

 

[Duane Hookom]

There is a sample solution for dynamic monthly crosstab reports at
http://www.tek-tips.com/faqs.cfm?fid=5466.

 

[mikeycan]

Hello Duane,

Thanks for the Tip/Link. I understand how it works, but it does not provide
the information I desire. The process offered looks at a date and goes
backwards. I am looking for a method that looks forward. Again I would like
to see starting from the current month what Amortization would be for the
next 12 months and a sum any amortization over 12 months until it runs out.
For example, I have an invoice for $150K and it is amortized for 15 months
($10k a month), starting in November 2007. When I run my report in December
it should show me the following:

“Mth1†to “Mth12†= $10K each
“LongTerm†= $20K (months 14 & 15)

Since the amortization started in November and I am running the report in
December the November amount would be excluded. Is this possible?

Thanks in advance for you help.

 

[Duane Hookom]

Do you actually have the values in a table that you would like to return in
your query or are these all calculated on the fly?

You should be able to apply either future or past dates by simply changing
the minus to plus or vice-versa.