SSRS MAtrix/Tablix

Question

I have a report in SSRS report builder 2008 that has a chart that is a definitive size. I would like to make the matrix table fit neatly under the report as it would in excel. However it expands way past the report. I have tried turning can grow can shrink to false did not work. I have tried putting a rectangle and placing the matrix in there with no success. I tried setting the size in the tablix properties but it extends or shrinks below based on the data in the tablix. The tablix is just 6 lines the top line is a expression for the date and then there is previous year previous cumulative current yeat and current year cumulative and receipts %to last year. i have included the expresssions I used in the tablix not that I think it is causing the issue Any help will be greatly appreciated

    =Sum(Fields!ID2013_Inventory.Value) 
    =Sum(Fields!ID2013_Inventory.Value) 
    =RunningValue(Fields!ID2014_Inventory.Value,Sum,"RECEIPTS_IND_DEC")   
    =SUM(Fields!ID2014_Inventory.Value)/SUM(Fields!ID2013_Inventory.Value)

Why did setting can grow to false didn't work? What happened? — Joao Leal
it just expands as it is based on the dates IF it did not expand there would only be one column each column is a date — lance9877
Try adding one row to the top of the matrix, merge it so that it has the width of all the columns, and then place the chart inside it. — Joao Leal
I am not sure how you plan on merging a row to the matrix can you give me some more information on this as every time I try I get a ERROR — lance9877
First you create a header row, outside any group, and merge all the columns in that row, and then you insert a chart (I can't send you an example, because I don't have SSRS installed on my current pc). You can also try something like this: stackoverflow.com/questions/22658791/… — Joao Leal

Alan Schofield Alan Schofield · Accepted Answer · 2015-08-03T18:06:50

Sorry I didn't follow up your comment earlier, it's been busy here... There is a sledgehammer approach to this that will work, not sure if there is a more elegant way but here goes.

Fist some basics... Determine how wide your matrix needs to be, let's say 25cm Let's assume you have a couple of columns on the left that are both fixed at 2cm each. That gives us 21cm to put our varying columns in. For now assume we only have 28 days, so we need to set the column width of the column group to 0.75cm (21/28). Test and make sure everything looks OK.

Now here's comes that sledgehammer!

Make another 3 copies of your matrix and adjust the column widths for the next three number of days (so 29,30 & 31).

Now set the visibility of the of each matrix so that it only shows when the related number of days are returned in the dataset.

I don't know what your dataset looks like so I can't give much advice on determining the number of days returned but I often create another dataset that gets the value, in your case something like

SELECT COUNT(DISTINCT MyDateColum) as DateCount FROM MyDates

Or I guess you could probably base visibility on the month and whether its a leap year or not.

Hope that helps, if any of it is unclear let me know and I'll try to get back a little quicker this time.

SSRS MAtrix/Tablix

1 Answers