SSRS 2005 Matrix Column Break

So I have spent way too much time on this problem. I am using SSRS 2005 and need to put a page break in between columns in a matrix. I want to show data by year. I have a year row and an origin row with a page break after the year row. Which works great, except that the columns for that year still show up but with blank values.

I have attached the report for clarification.

Basically I only want the corresponding 'WeekStart' columns to show up in the matching Year row.

I feel like this should be quite easy and I am just not getting it :(

Thank you.

-- Lisa

Shipments-by-Week.xls
Lisa CallahanBusiness Intelligence AnalystAsked:
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.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
What i understood from your explanation is, You want your data grouped by year and only columns related to that year.
E.g. if your row is showing year 2014. Columns must have the data related to 2014 not others.

In your report You have used same grouping column in both row group and column group.

When you run your report, ssrs will decide first how many columns will be in the report.
You have column group on Year and Date i think. It will first group all the data and decide number of columns and same for row.

In your case you have same grouping column for Row and column. So when data is grouped by year for the row, tablix can only have data for that year and so in the column you can see data related to that year and rest are blank.

Same for year 2015. It will show data for that year and rest will be blank.


We can not have dynamic number of columns in the report.

In your case columns are different for years, So we can not even think any custom logic in the Query.
Like for 2014 First Column is 1/5/2014 and for 2015 it's "1/4/2015"

I would suggest to have only row groups for this. So you can have the data only related to that year and date.

Hope it will help you.

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
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
SSRS

From novice to tech pro — start learning today.