Hi folks

hope you can help.

I'm working on a sales report which is grouped per salesperson and grouped again by fiscal period. (The report user will run it by fiscal year)

I've created formulae fields to set a year to date target for each period. This increases incrementally per period so, Salesman A's target is 50k per month therefore his year to date target for period 1,2,3,4, is 50k,100k, 150k, 200k etc etc.

The object of this is so that I can graph his progress so far this year. I will have a graph with a diagonal line from 50k to 600k and his monthly sales figures plotted against this then.

The problem I'm struggling with is how to calculate his sales year to date in each period. I have got the Sum(Invdtl.Extcost), when I use this in my group footer for period I get the sales persons sales total per period. What I need to show now is that total increasing per month (added to all the previod months)How do I write a condition that looks at the period and sums all previos periods on the report?

what I'm trying to get is something like the following.

period.....sales total....yr-to-date-target...yr-to-date-sales

1....45,000....50,000...45,000
2...60,000....100,000...105,000
3...30,000...150,000...135,000

hope I'm explaining myself clearly

thanks

thanks lockwelle

i havent done any training on using shared variables so it looks like I've a bit of reading to do. thanks for your suggestion.

Hi lockwelle.

thanks again for your help, I'm making progress but am still bangin my head against the wall..

Using what you suggested and also what I could see this guy doing http://www.youtube.com/watch?v=DNJfG0Af3wM , I managed to hack my way to get something working

I was create a formulae and declare a variable which referenced thesales total per month and adds it to the previous. It all seems to work fine. THe totals look correct. As you mentioned it will only work for one engineer at the time so I was going to create a subreport for each engineer and then call them all into the master report, so this should be fine I think

only problem I'm meeting is when I try to plot my new dataset, I'm using a 'line chart with markers at data points' I can select the fiscal period on the X access and on the y axis i can only select the target (which is a diagnoal line plotting the increasing target throught the year) and the sum of InvDtl which only plots the monthly sales total (same as my original problem)

I dont seem to have my new formulae listed in the available fields to plot. any suggestion as to why this may be?

My fomulae is
currencyvar SalesTotal;
SalesTotal:= SalesTotal + Sum ({InvcDtl.ExtPrice}, {InvcHead.FiscalPeriod})

I didnt see how to apply the header rest as you mentioned, maybe this is something to do with it?

At some stage, you can tell me take a hike and go figure for myself!
thanks