Hello, I have defined a matrix in SSRS 2008 R2. The columns are obviously dynamic and based on my dataset. I have setup the report to be paged by the group row which in this case is a particular account. Now, unfortunately, every row is not going to have information for every column. So, as I am paging through my different accounts, there are many columns that are blank. How may I hide the blank columns? Just to be clear, in report designer, I can only see one column. But upon run time they expand as the data is fed in. Thanks!ddd

Please check this tread might helps you http://www.sqlservercentral.com/Forums/Topic1071514-150-1.aspxhttp://uk.linkedin.com/in/ramjaddu

Thanks for the reply. However, that link has me explicitly defining what columns to hide. I don't want to specify explicitly.ddd

Thanks for the reply. However, that link has me explicitly defining what columns to hide. I don't want to specify explicitly. ddd Hi dbansal, Matrix's column is dynamical, this is by design, but we could type some expression to specify them explicitly. From your statement, I get that you do not want to display the column in which all the value is null, right? If so, I would recommend you follow below detail steps: 1. Right-click the ColumnGroup Name in Column Groups pane located in bottom-left corner, and then select Group properties... 2. Move to Visibility tab, then select the Option of Show or hide based on an expression in the right diagram. 3. click FX button to type in below expression =IIF(IsNothing(SUM(Fields!Data.Value,"ColumnGroupName")),true,false) Note: Data and ColumnGroupName is your data field and Column group name, you should replace them based on your report. 4. Click OK. After these steps, I think you could get what you want. Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Challen, thank you for your reply and I apologize for my late response. I only have one column group called category, and the data is derived from the category datafield. So I have set my expression to: =IIF(IsNothing(count(Fields!category.Value,"matrix1_category")),true,false) I could not use sum since the category column is not numeric so i set it to count. The report runs, but my columsn still show! ddd

Hi dbansal, Where have you set the expression? could you give the steps?Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Challan, Here are my steps: I move down towards the lower right hand side of my screen. There I see Row Groups and and Column Groups. 1. I click on advanced mode via the drop down to right of column groups. 2. I see "matrix1_category" and then indented to it right below I see (static) 3. I right click on matrix1_category 4. I select group properties 5.I click on visibility 6.I click on the fx 7. I put in: =IIF(IsNothing(count(Fields!test_name.Value, "matrix1_category")),true,false) I wish I could take a screenshot of my report and paste it here, i think it would give you a better idea... Thanks!ddd

Hi dbansal, To Count function, you should modify your expression to =IIF(Count(Fields!test_name.Value,"matrix1_category")=Cint(0),true, false) I have test it, it should work for you, please have a try and give the feedback. Thanks, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.

Challen, still not good, I have put the following for the expression: =IIF(count(Fields!test_name.Value,"matrix1_category")=Cint(0),true,false)ddd

this is how my matrix looks.... done with my best ability (space) [category] a c c (test name) o u n t n u m b e r ddd Thank you!

Hi dbansal, This expression works fine in my report, I think there must be some misunderstanding between you and me, so in order to help you furtherly, could you please give some sample data and RDL file, I will do more troubleshooting for you.Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Challen, How may I send you my .rdl files and some sample data? I can send you the .rdlc file. But how would you want sample data?? Excel?ddd

Hi dbansal, Sorry for missing the Email address sqltnsp@microsoft.com please send your RDL report and sample data to this mailbox, with the beginning "Hi Challen", please attach this thread link as well. Once I receive your email, I will do some further troubleshooting and give you a feedback as soon as possible. Besides this I also create a sampe report with sample data, with the expression =IIF(Count(Fields!value.Value,"col2")=Cint(0),true, false) it could hide the blank column very well, you could download it to see its effect. Which is available at this link http://cid-45bfd76b0f2c3808.photos.live.com/self.aspx/picture/SSRSDEMO.rdl Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi dbansal, I get your email, and do a test based on your RDL and sample data, I get your meaning completely, at your last two page, there are some blank columns, you want to hide the blank columns on that page, right? Actually, it can not be achieved at this scenario, as in top three pages these columns are not blank, you just set a pagebreak for this matrix, in fact these pages are from one control, we could not hide some columns on some page while keeping these columns at other page, we could only hide these columns which are blank on every pages. E.g. if your first column are blank at all your pages, we could hide it using the my expression. However here I still would recommend you to submit a feedback to Microsoft Connect at this link at this link https://connect.microsoft.com/SQLServer/Feedback This connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release. Regards, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.