SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Challenge: Toggle column visibility


SSRS Challenge: Toggle column visibility

Author
Message
gbritton1
gbritton1
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 864
Say I have a simple table in my database. it has just four columns:

foo, bar, foe, fum



Say that the data is grouped on foo, and that column fum is a dollar amount I want to sum. Columns bar and foe have a 1x1 correspondence with column foo and are not grouped under foe (no sense in that, given the 1x1 correspondence). Further, say that I don't want to see columns bar and foe in the initial display. that is, when I run the report at first, I see just two columns:


foo fum
abc $100.00



What I want to do is set up the report so that, when presented with the initial view, I can click on an item in the foo column (e.g. 'abc') and then see all four columns:


foo bar foe fum
abc more stuff still more stuff $100.00



In other words, I want columns bar and foe to be hidden in the report until I click on a item under foo, whereupon they would be revealed, then hidden again if I again click on foo. So, in essesnce, foo acts as a toggle for the visibility of columns bar and foe.

I can't figure out how do to this. So....can it be done, and if so, how?
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4938 Visits: 13189
Open the report in design view.
Right-click a column, show properties.
Click on the Visibility tab.

When the report is initially run HIDE

Display can be toggled by this report item (choose a textbox). It helps to rename the textbox so that it is called something other than "Textbox55", so rename it to something like "txtProductName".

Does that answer your question?
gbritton1
gbritton1
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 864
pietlinden (12/2/2013)
Open the report in design view.
Right-click a column, show properties.
Click on the Visibility tab.

When the report is initially run HIDE

Display can be toggled by this report item (choose a textbox). It helps to rename the textbox so that it is called something other than "Textbox55", so rename it to something like "txtProductName".

Does that answer your question?


I wish it did! When I try that I get an error message:

"The defintion of the report "test report" is invalid. The "test tablix" has "txtfoo" as a toggle item. Toggle items must be in boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and cannot be contained in the current report item unless the current grouping scope has a Parent."

I tried to wade through this message but I'm not sure I get it. Column foo is the only grouping item. Columns bar, foe and fum are the details. So how do I make columns bar and foe conditional on column foo?

Steps to reproduce my problem:

1. Start a new report and set up some datasource
2. Define a new datataset with this query:


select 'foo' as foo
, 'bar' as bar
, 'foe' as foe
, 'fum' as fum



3. Put a table report on the design surface
4. Make column foo a row group and call the text box "txtfoo"
5. put the other columns in the details
6. On column "bar", set column visibility to
a. Initially hidden
b. Toggle on text box "txtfoo"
7. Preview the report
8. Observe the error.
gbritton1
gbritton1
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 864
Just wondering if anyone has come up with a solution to this problem....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search