Culmulative Reports

Question

Post reply

Culmulative Reports

Landon-249235

Newbie

Points: 5
More actions
August 25, 2005 at 9:52 am

#168139

Hi all,
I'm working with a report made in excell, trying to automatie its generation with RS. Basically, it should take sales for a particular executive and culmulatively calculate their sales for each month. IE:
Month January February March April May ...
Sales: $500.00 $100.00 $250.00 $150.00 $100.00 ...
Culmultaive Sales: $500.00 $600.00 $850.00 $1000.00 $1100.00
...
So far, I havnt come up with a good way of doing this without using an iif() nested in a sum() aggregate function. I would prefer to have a more eliquent method of calculating the Culmulative sales as this "sum(iif())" trick doesnt work when drawing charts.
If anyone has ever made a culmulative report, could you please give me some insight. It would be much appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What query are you using to get the data?

How are you displaying the data on the excell sheet?

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Post the code you used to get your data set.

if you did your data set correctly you can just do a grouping in your table, or matrix

and it will be all good.

Landon-249235 Newbie Points: 5 More actions · Answer 3

This is the query I'm using

select year (i.Invoice_Date) as Invoice_Year,
month (i.Invoice_Date) as Invoice_Month,
datename(month, i.Invoice_Date) as MonthName,
i.Sales_Rep_Id as Sales_Rep_Id,
i.Sales_Rep_Name as Sales_Rep_Name,
i.Line_Amount as Line_Amount,
0 as Quota
from T4_Invoice_Lines_View i
-- We are searching for an account Executive. Does the Executive_Id match?
where i.Sales_Rep_Id = @Executive
-- We are searching for a fiscal year. Do we fall within the months?
and ( month(i.Invoice_Date) between 5 and 12
and year(i.Invoice_Date) = convert(int, @Year)
or month(i.Invoice_Date) between 1 and 4
and year(i.Invoice_Date) = convert(int, @Year) + 1
)

I also union with another query that is just joining every month with the correct year so there is data for every month. This way I can force RS to show numbers in every row when it generates the table. You notice I am selecting 0 as quota. This is because each month has to have a quota showing whether there was a sale or not, so quota is selected in the other query. I think if I can get culmulatives sales working I'll be able to figure it out from there, so just ignore the quota selection.

The data on the excell spreadsheet is being shown exactly like the table in my first post. with a few additional rows (Quota, plus/minus, culmulative plus/minus) Of course in excell, you can just higlight a few rows and generate the table with the given datapoints, but RS likes to draw its own datapoints dynamically, so I need a good way to get the culmulative numbers..

edit --

I should clarify the selection query..

The year () month () and datename () should be self explanatory..

The report filters by Sales_Rep_Id, so there will only ever be one entry in the data set, I only really need the name for displaying purposes, the ID is extraneus. Technically, I could just pull the name from the paramter for output..

the important data here is line_amount which refers to sales.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Here's an exemple of a running total :

declare @pointy table(

id int not null primary key clustered,

point int not null)

insert @pointy (id, point)

SELECT 1, 25

UNION SELECT 2, 10

UNION SELECT 3, 35

UNION SELECT 4, 30

UNION SELECT 5, 10

UNION SELECT 6, 15

UNION SELECT 7, 10

UNION SELECT 8, 15

UNION SELECT 9, 10

UNION SELECT 10, 10

Select P1.id, P1.Point, sum(P2.point) as RunningTotal from @pointy P1 inner join @pointy P2 on P2.id = @StartDate and Invoice_Date < @EndDate

This will allow the use of index seeks which are much faster than scans.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Not saying I'm proud of it, but the following is a single pass query that will produc the running total for you. It's not "perfect" in that you'll need to do some maths on your month numbers (ie this works in calendar order, you're going to need to mod your months so that they come out in fiscal order - this is important as running aggregate needs to be in that order, not the calendar order :S I have a way to do this let me know if you get stuck). The sample is from foodmart.

SELECT y.MonthNum, y.sales, SUM(z.sales)

FROM

(

SELECT MONTH(Orders.OrderDate) AS MonthNum, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS sales

FROM [Order Details] INNER JOIN

Orders ON [Order Details].OrderID = Orders.OrderID

GROUP BY MONTH(Orders.OrderDate)

)y

INNER JOIN

(

SELECT MONTH(Orders.OrderDate) AS MonthNum, SUM([Order Details].UnitPrice * [Order Details].Quantity) AS sales

FROM [Order Details] INNER JOIN

Orders ON [Order Details].OrderID = Orders.OrderID

GROUP BY MONTH(Orders.OrderDate)

)z

ON y.MonthNum >= z.MonthNum

GROUP BY

y.MonthNum, y.sales

ORDER BY 1

HTH,

Steve.