Conditional Column Visibility - Groups/Page Breaks

Question

Conditional Column Visibility - Groups/Page Breaks

kschwid

Ten Centuries

Points: 1138
More actions
September 7, 2012 at 8:30 am

#261290

I have a report, it is grouped by a column and each new group has a page break, this way when it exports to excel each group has its own tab in the workbook. Depending on the group I need to hide/show certain columns.
Group A - Show Columns 1-5 - Page 1
Group B - Show Columns 1,2,4,5 - Page 2
Group C - Show Columns 2-5 - Page 3
Group D - Show Columns 1-5 - Page 4
I can get the columns to hide in the first place but cannot get it to reshow once I go to the next grouping of the report. As in I hide Column 3 for Group B but cannot get it to reshow for Group C. Any ideas? Its almost as though once its hidden its hidden.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Spiff SSCertifiable Points: 7826 More actions · Answer 1

Hi,

As far as I am aware it's not possible to dynamically hide a column based on groups in a single tablix due to how it is rendered. You can hide the textbox values by changing the hidden property expression based on group but the blank column is still there and will leave an empty column in the excel export.

It's a bit clunky but you can produce the effect you want using a table in a table.

Create a tablix and add a row group with a header for your groupings. Edit the group to add page breaks between each instance.

Delete the new grouped column and any extra columns on the right so you only have one left.

Then delete the details group and row. In your remaining textbox right click and insert a tablix. You can now add your columns 1-5 and then column hiding stuff in this tablix as normal.

When you run it a tablix is rendered for each grouping and the column hiding evaluated for each tablix and this should export into excel as required. I've attached a pretty rough example from adventureworks.

Hope this helps.

kschwid Ten Centuries Points: 1138 More actions · Answer 2

I am unable to open the example you attached.

It sounds like you want me to make a table

Then add a group (column or row?)

Then I am to delete the columns and detail lines. RS doesn't allow you to do this. You have to have a detail row if you have a group.

And maybe I am just not understanding your directions.

I am using the visibility property not hide/show and using page breaks. I can get the report to hide the columns I want for the first group/page, I just can't get them to come back for the next group/page.

Thank you though for trying to help.

Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI

Spiff SSCertifiable Points: 7826 More actions · Answer 3

I did say to add a row group, with a header row.

You need to delete the Details group from the Row Groups box at the bottom of BIDS (I only use BIDS so hope it's not different in report builder), you can right click the Details group and delete.

Please reread the post above, it does work.

I have attached just the report definition and now it doesn't need adventureworks, what was the issue with opening the file I attached last time?

kschwid Ten Centuries Points: 1138 More actions · Answer 4

Fix for my issue:

Create a separate table with the appropriate columns for each group

-- using the filter option in the properties of the table set which group (values) that table should include

-- set each group to start on a new page found on the general tab of the properties of the table

Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI

Spiff SSCertifiable Points: 7826 More actions · Answer 5

Spiff

SSCertifiable

Points: 7826

September 11, 2012 at 10:07 am

#1536369

Nicely done, it's not dynamic though 🙂