SSRS cycle through Months

I'm still learning a lot within SSRS, so bear with me. Currently, if I want to break down month by month info in a report I do a datepart for month on each cell for each expression and set it equal to the month which corresponds with the column heading. So Jan=1 Feb =2, and so on.

I end up with a large table and numerous rows. I'm trying to understand how to loop through the data and build out these tables on the fly, if possible.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Can you mock us the schema of what the source data is, and a pretty picture of how you want the output to display?
LCNWAuthor Commented:
Yes. Give me some time to do that.

David ToddSenior DBACommented:

I'm guessing that by now you'll have come across the preferred method for removing time from a datetime

ie select dateadd( day, datediff( day, 0, getdate()), 0 )

Now what do you suppose happens if you change the day to month? That's right, you get midnight on the start of the first day of the month.

So, if you group by the above said function, then all those transactions will get grouped by month. And guess what else? No issues with wrapping around over years, and financial years which don't map to calendar years, or current_year vs previous_year.

So all that to say this: I've found a solution which has worked for me, with no need to loop, often no need for intermediate tables ...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LCNWAuthor Commented:
I've determined it's hard for me to mock up, but I'll try.

I pull in a dataset in SSRS. Then, I have 12 columns named Jan, Feb , Mar... and so on.

In order for these columns to display the proper month's data, I do the following in each text box's expression:

IIf(DATEPART("m", Fields!DateTime.Value) = 1 THEN <some value>

So that would be January.

Then I have have a StartDate and EndDate Parameter that handles the Year portion.

So, I have to enter the expression 12 times per row and change the DATEPART value incrementally as I go.

I was hoping there was an easier way to handle this as this is a lot of data entry.
LCNWAuthor Commented:
I'm starting to think I need to build this out in a SP somehow.
David ToddSenior DBACommented:

Unless it is something that has a fixed starting point like a 12month profit and loss or balance sheet, I find its easier to just label the columns 0, 1, 2, 3, 4, 5 ... where 0 is current, 1 is 1 day/week/month ago ...

LCNWAuthor Commented:
This pointed me in the right direction. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.