I have a Matrix report with some columns that are grouped by Month and Year in the Column Group. Now, these columns are being toggled by the Month Column as the Visibility Property is set to hide but toggled by the Month Column. But when the report is rendered, the hidden columns come out as blank and this defeat the purpose of the report, as seen in the images below. How do I do away with the blank spaces or which is the best way to meet this requirement?
I'm assuming you tried to set the individual column visibility properties...
You actually need to set the group visibility of your EnglishMonthName group to be toggled by [CalendarYear]. This will not hide the column completely (as you would not have anything visible to click on to get it back again) but it will collapse the data down and aggregate at the year level.
What you have done to hide your columns is actually working. If you look at that WIDTH of each of the month then you will see they are of the same width. So according to your query you have the months appearing regardless of the corresponding data for that month.
I would do in two way depending on how the users would like it.
If possible and for better performance, I would alter the SQL query and do one of the following: 1. The months for which there is no corresponding data - I would remove the month names (or ID) as well 2. Rather than returning NULLs I would return a valid value like 0 or 'N/A' this will how the month as well as what the data actually is
To do it in the matrix you will need to alter the visibility of the month row as suggested by Alan Schofield
