Search code examples
sqlmatrixreporting-servicesssrs-2008ssrs-tablix

SSRS MAtrix/Tablix

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)
Solution

  • 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.