Rolling 12 month report - Very Difficult Version 8.5

[AlohaMichael]

I need to create a report where the user will enter in a date and then can see the quantity sold on their items from the prior 12 months. It must run under version 8.5.

Setup

Table has the following fields
Year, ItemNumber,
Period1, Period2, Period3, Period4, Period5, Period6, Period7, Period8, Period9, Period10, Period11, Period12.

Additionally the Jan 2004 data is store in the Year 2004 Period7.
Jul 2004 is stored in Year 2005 Period 1. Everything is stored six periods later than normal.

The report output should look something like this
User enters: 01/01/2004

Item# Period7 Period8 Period9 Period10 Period11 Period12
1234 1 7 8 22 3 9


PROGRESS SO FAR:
I think the only way I can do this is to create 12 variables named varMonth1, varMonth2, varMonth3....each with a shared numbervar called month1, month2, month3... and then as Crystal is processing have another variable add the totals to a shared numbervar and keep different subtotals.

PROBLEM:
How do you assign the value to the variables?
How do I ensure that I only select the proper year's data. For instance the period7 data holds Jan 2004 data and Jan 2005 data depending on what value is in the year field.

Suggestions, comments, follow up questions all will help

 

[MrBillSC]

I would use the following approach:
Create Parameter FiscalYear to prompt for the year.
Create Parameter Period to prompt for the period.

In the record select formula try:
({table.year} = {?FiscalYear} - 1 and
{table.period} >= {?Period}) or
({table.year} = {?FiscalYear} and
{table.period} < {?Period})

Next create a formula for each period:
// {@Period01}
If {table.period} = 1 then
{table.QtySold} else 0

Sum your 12 period formulas in the group and/or report footers.

MrBill

 

[AlohaMichael]

I guess I need to clarify something. The above solution wouldn't work because each period is its own field.

The field name list is:
ITEM, YEAR, PERIOD1, PERIOD2, PERIOD3, PERIOD4, PERIOD5, PERIOD6, PERIOD7, PERIOD8, PERIOD9, PERIOD10, PERIOD11, PERIOD12

The values stored would be something like this:
1234, 2004, 0,0,8,1,9,0,4,1,2,7,2,1

Any other suggestions comments would be most grateful.

 

[MrBillSC]

If the entire year's data is in one record, then you will only be reading in 2 records from your table, one for the year prompt {?FiscalYear} and one from the previous year.

In the record select formula try:
{table.year} = {?FiscalYear} - 1 or
{table.year} = {?FiscalYear}

Create 12 formulas for each period.

// example for {@Period1Amt}

If ({table.year} = {?FiscalYear} - 1 and
1 >= {?Period}) or
({table.year} = {?FiscalYear} and
1 < {?Period}) then
{table.Period1Amt} else 0

For {@Period2Amt} formula replace the "1" with a "2" and so forth. Place these formulas in the detail section and suppress the detail section. Then sum the formulas in the report footer.

MrBill