Report Matrix filler

Question

I have a matix in my report (VS2005) i would like the matrix to display a default of 12 rows even if no data is available. i have not figured out how to do this so i create a group of rectangles below the grid and each row of these rectangles has is visibility changed based on the row count in the matrix.

the problem with this is that these rectangles stil exist when invisible and therefore create a second page of the report that is blank because the matrix has pushed them down.

so my question is how do i remove these rectangles that are invisible or how do i tell the matrix to have a minimum row count of 12.

Answer 1

You can add filler by using a left outer join to a table with the required dimensions.

In this example not all the months have sales but the month table contains all the months. given tables

month
------------
month_id -- 1-12
month_name -- Jan - Dec

Sales
------------
month_id  --not every month has sale
product
amount

select
    month_name
    ,product
    ,sum(isnull(amount,0))
from
    month
    left outer join sales
        on sales.month_id = month.month_id
group by
    month_name
    ,product