Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Error when trying to hide a column.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Flowing Fount of Yak Knowledge

2780 Posts

Posted - 12/06/2013 :  09:06:21  Show Profile  Reply with Quote
Say I have a simple table in my database. it has just four columns:

foo, bar, foe, fum

Say that the data is to be 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 tried to conditionally hide the columns, but it won't run. 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 valid datasource (any SQL Server instance will do)
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.
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000