create "expand all" button.

Hello.

Is there a built in option of adding an Expand/Collapse all Button to my report?

I have a report with few groups and I want to give the user a simple way to expand all of them for easy view or collapse all of them so he can see only the groups header.

I thought about using an expression in the hidden property of the groups and try to control it with parameters but its looks like a lot of work.
I also predict problem with it if I'll try to sort the table and the page will refresh it self every time.

Is there something "of the self" for it?

Thank.
Roy.You want to enable drill down in the report.|||

Hi,

Did you find a way to do this?

Edmund

|||

Don't know of "off the shelf" for this.

I have gone down the Paarameter driven route and it is not that much work. the report I'm thinking of had three grouping levels and a report paramter that passed an iteger value (1,2 or 3) to an expression for the hidden property of the group header & footer and details rows. I used the switch() function rather than if() statements in my expression, just seems like less clutter once you are used to using it.

It wasn't that much work - half hour/hour. I also needed expressions for the appearance of the toggle items for the different groups because you will find that using the parameter approach you get a [+] when you should have a [-], but the expressions are pretty much the same.

I find it works well. My one problem is that after a user clicks "expand all" and the "view report" the report seems to "forget" any other parameters e.g a selected year or motnh. The report still displays okay but a textbox displaying the ReportItems!Parameteryear.Value goes blank and if the user clicks on a drillthrough link the it passes a nulll value through. Clciking refresh "reminds" the report of these other parameters and all is fine again. But if anyone has an explanation for this phenomenom I'll be grateful.

Another option would be to have a textbox on the report with text "Expand All" that jumps to a copied version of your report passing all the parameters, but you change the the properties of this report to display all detail. Report2 could have "Collapse All" textbox that drills back to report 1 where everything is nicely hidden again.

counting multiple values from the same column and grouping by a another column

This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.

TheTable
Order# Customer Status

STATUS has valid values of PROCESSED and INPROGRESS

The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.

I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?

Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customeryou can write a stored proc and use output parameters to return the values..and in your stored proc have multiple sql stmts to get the diff counts..

hth|||This might work for you, there is probably a better way, this can become very expensive with the 2 sub queries if there is a lot of data

 SELECT Customer,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'Shipped'
 ) AS Shipped,
 (
 SELECT COUNT(*)
 FROM TheTable
 WHERE Status = N'SomeOtherStatus'
 ) AS SomeOtherStatus
 FROM TheTable
 ORDER BY Customer