Rolling Year to date total
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20456
Printed Date: 04 May 2024 at 3:08am
Topic: Rolling Year to date total
Posted By: marco007
Subject: Rolling Year to date total
Date Posted: 11 Feb 2014 at 10:54pm
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
|
Replies:
Posted By: lockwelle
Date Posted: 12 Feb 2014 at 5:18am
you could use variables, global or shared to keep track of this, though it would only work for 1 person at a time...
group header: reset
shared numbervar totalGoal:=0;
shared numbervar totalSale:=0;
""//hide the 0 output
group footer:
shared numbervar totalGoal:= totalGoal + 50000;
shared numbervar totalSale := totalSale + sum({table.field}, {group});//the group would be the period
then you could have display formulas like:
shared numbervar totalGoal
and
shared numbervar totalSale
it's an idea...
|
Posted By: marco007
Date Posted: 24 Feb 2014 at 3:45am
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.
|
Posted By: lockwelle
Date Posted: 24 Feb 2014 at 6:59am
believe it or not, the outline above is pretty much all that there is to variables. They come in 3 flavors: local (only the formula that they are declared in), global (available in the reports--just the report) and shared(available in the report and can be shared between reports--like a subreport).
declaring a variable is nothing more than writing a statement like:
shared numbevar x;
once declared, they can be used like any field as far as logic is concerned.
Comparator is = , assignment is :=
they typically come in groups of 3 formulas: reset, increment, and display, which have been demonstrated previously.
Have fun. Crystal's Help isn't that bad, that's where I learned most of what I know...besides hitting my head against a wall ;)
|
Posted By: marco007
Date Posted: 25 Feb 2014 at 12:39am
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
|
Posted By: marco007
Date Posted: 25 Feb 2014 at 5:23am
spent a while this afternoon reading more on this.
made some progress eventually by using a running total field(first time trying one)
the chart looks right anyway so now I'll go try put it all together.
|
|