Year over year reports

Question

Year over year reports

greenandgold52

Grasshopper

Points: 19
More actions
October 30, 2015 at 4:44 pm

#309359

I have a year over year running total sales report that works perfect except for if a branch has not been open for two years. For branches that opened in 2014 the issue is the first month is the month that they opened not Jan. Is there some way to force the first month to always be jan even if there is no data?

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

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

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 1

greenandgold52 (10/30/2015)
I have a year over year running total sales report that works perfect except for if a branch has not been open for two years. For branches that opened in 2014 the issue is the first month is the month that they opened not Jan. Is there some way to force the first month to always be jan even if there is no data?

Yes. Create a mini-calendar table as a Temp Table with all the months in it and do an outer join to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

greenandgold52 Grasshopper Points: 19 More actions · Answer 2

Thank you for the response. Unfortunately I am just the helpdesk who knows some programming at a small company. Granted I am in school and looking to become a DBA but right now I have little control over the database. Do you have any other suggestions or how would I go about implementing that from BIDS?

MMartin1 One Orange Chip Points: 27799 More actions · Answer 3

Inside of the SSRS development environment you can create a data set that has this information, that is, the calendar months that Jeff Mentioned. You want that table/dataset to have at least

month(like 2015-01-01), monthName (like 'January').

You join on month(yourdate) for a specific month or dateName(m, yourdate) for all Januarys in all years for example.

Hope this helps.

----------------------------------------------------