Create Chart of IC Monthly Stock Totals

[johnhugh]

Hi, Can anyone give me some advise on how to create a line chart for stock totals by month in Accpac 6 and Crystal 2010?
It has to be from the IC subledger because I want to show a seperate line for each item category. GL only has the monthly totals.

The problem is that I want to show a trendline for the last 12 months only, however in order to calculate monthly totals I have to add up all transactions in ICIVAL up until the period I'm after.
I have all the monthly numbers and they are correct but I struggle to create a line chart from them.

My monthly totals are calculated like this example for the previous month:
IF {@ICYearMonth} <= ToText(DateAdd("m",-1,currentdate),"yyyyMM") THEN
{ICIVAL.TRANSCOST}
ELSE
0

I have 12 fields for the 12 totals and no groups.
I believe creating a line chart from this does not work because these totals are not associated with any periods.
I cannot however group by period because then adding up ICIVAL.TRANSCOST does only calculate the net changes per month, not the totals.

Would really appreciate any help on this.

 

[ettienne]

Here's what I do:
Get the current balances from ICILOC = BAL12. Assuming that you are running the report for the current month or date.
If you want to run the report for prior months then the current balance is not the ending balance, so it becomes BAL13. You want to deduct the net movement from the report date to the current date to get BAL12 (see below).

Let's assume the report date is the current date.

You have the current balances in BAL12.
Get the net movement for month 12 from ICIVAL and deduct from BAL12 = BAL11.
Get the net movement for month 11 from ICIVAL and deduct from BAL11 = BAL10.
Repeat for each month until you get BAL01.

You could start with a beginning balance BAL00 and work forwards, whatever works for you.

I don't do this process in Crystal - I don't know if it is possible. I build a temp table in Access or SQL with BAL01 to BAL12 and report from that.

Good luck.

 

[johnhugh]

Thanks ettienne.
Wouldn't I then end up with the same problem?
Do I not also end up with 12 value fields and no periods?

Should I not have just one field for the values and one field for the periods wich I can then group in order to create a chart?

I have all my numbers already, but my issue is that I can't group anything which seems to be required in a line chart.
In your example, do you group your fields to generate a chart?

 

[ettienne]

So your questions is really how do you do a chart in Crystal?
I'm not doing a chart, but I need to get the balances for x months ago by item on a report.
You could do it in columns or in rows - same thing. But I think you will need them in columns for charts - I'm not a chart wizard.
I would suggest playing with the data in Excel and doing an Excel chart - that will give you some direction on what to do in Crystal.