Revenue Projections For Matrix Report - View?

Question

Revenue Projections For Matrix Report - View?

Dexter L.

Valued Member

Points: 56
More actions
October 21, 2016 at 3:13 pm

#313994

I'm going to cross post this to the SQL 2012 area as well as I'm not sure where it best fits.
Imagine having the following columns:
Begin of Sales Date
Ramp Up Months
Annual Sales Projection
Annual Percentage Increase
Revenue Duration
Ramp Down Months
So basically an item can have a begin of sales date of 1/1/2016, with a 4 month ramp up. After that we hot the Annual sales projection which increases annually by the Annual Percent Increase percentage until we hit the Revenue duration at which point it will ramp down for the number of Ramp Down Months. The good thing is that the ramp up and down are evenly spread, ditto for the Annual % Increase. So if I have a $100,000 Annual Sales with a 10 month ramp up, I'd have $10k, $20k, $30k, etc. until I reach the $100k.
The challenge is that I need to build an SSRS report containing a matrix to show the projected revenue per month, in a matrix of course, based on a given start and end date. I'm having a challenge making it work in SSRS and am wondering if it would be easier to build a view and populate it with the sales amount by month and year, placing all the math in the view and making the report a piece of cake. I've made the matrix work and can place the (fixed) annual revenue in the cells from the begin of sales date through the end, I'm just stuck making the values increase per month / cell.
Any input is greatly appreciated!
Dexter

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

First thing is that you don't really seem to have an actual table incrementing or decrementing the data. Don't worry - there's a well-documented solution around here, called the Tally Table:

http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

Second part is that your description, while providing the high level is a bit bushy on details so I can't quite fill in the blanks. Here's my best stab at it.

If this isn't quite right - it might help to provide some test data with the expected results (in table form)

Declare @BeginofSales Date,

@RampUpMonths int,

@annualSales int,

@AnnualPercentageIncrease decimal(8,5),

@revenueduration int,

@rampdownmonths int;

Select @BeginofSales ='2016-01-01',

@RampUpMonths =4,

@annualSales =100000,

@AnnualPercentageIncrease =0.48,

@revenueduration=10,

@rampdownmonths=5;

With TallyCTE as (

select Row_number() over (order by sc.object_id) RN from sys.columns sc cross join sys.columns sc1)

select dateadd(month,rn-1,@BeginofSales) RptMonth,

@annualSales*(1+case when rn<=@RampUpMonths then @AnnualPercentageIncrease/12*(rn-1)

when rn<=@revenueduration then @AnnualPercentageIncrease/12*@RampUpMonths

when rn<=@revenueduration+@rampdownmonths then @AnnualPercentageIncrease/12*(@RampUpMonths-(rn-@revenueduration))

else @AnnualPercentageIncrease/12*(@RampUpMonths-@rampdownmonths)

end)

from tallycte where rn<25

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dexter L. Valued Member Points: 56 More actions · Answer 2

Thanks for the reply. Let me play with this a little bit, but I think I can make it work.

Dexter L. Valued Member Points: 56 More actions · Answer 3

I need to be able to do this as part of a query for multiple Products.

In other words, the SQL table would have these columns:

Product

StartofSalesDate

RevenueRampUpQuarters

RevenueDuration

RevenueRampDownQuarters

AnnualPercentageIncrease

So each product would have different data and I need to be able to have a StartDate and EndDate parameter to my report so that I can bookend the data. Does that make sense? Imagine a matrix report with a column for each product and columns for the dates, and of course the sales figures go in the cells.

This is why I was wondering initially if it would be easier to make a SQL view than trying to do it all in the query for the report.

Thank you again

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Still ultimately the same idea, except that you'd cross join your product table with the Tally table (which fills in each product by month in the same way as I did with flat variables.)

Once you have all of the cells represented - it simply becomes a PIVOT operation.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?