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?
David ToddConnect With a Mentor Senior 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 ...

Jim HornConnect With a Mentor Microsoft 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.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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 ToddConnect With a Mentor Senior 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.