SSRS 2005 Matrix Column Break

Lisa Callahan
Lisa Callahan used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial