MS reporting services toggling

Posted on 2005-04-28
Last Modified: 2008-03-03


I have a report that drills down purchase information by region, then the different types of products purchased in that region, then further into the different products themselves with the counts and revenue totals for each

here's the dilema: some of the types illustrated are freight and tax that account for some of the revenues, but since this level is expandable, it just expands into one more level deep with the repeated information, for example:

+televisions             27   42,000
     plasma tv           3    10,000
     HDTV                 1     20,000
     old tv                 23    12,000

+frieght                  27   400
     freight               27   400

how can some formatting to the toggle to only expand those rows that aren't freight??

thanks in advance!!
Question by:animated405
    LVL 14

    Expert Comment

    As far as I know you can't manipulate the toggle control for one row/grouping, it's either view all the toggle controls or view none.  You could surpress detail rows being shown by using something similar to:

    On the Detail Row, set the Visibility->Hidden property to the expression =IIF( Count(Fields!MYFIELD.Value)>1,FALSE,TRUE)

    This will hide your      freight               27   400

    Although the toggle will still display, it won't do anything.

    Author Comment


    I figured as much that's good to know. that's going to confuse my users so I'm just going to leave it as is.

    What if I wanted to ignore those rows alltogether? What would I put in the expression to do that? I know how to manipulate the data by checking the values, but haven't seen anything for ignoring a row all together

    LVL 14

    Expert Comment

    To ignore the rows, its probably best to filter them out at the dataset level.  You could use a filter, but its a bit slower.

    Author Comment


    thanks. the requirements I've been given wil not permit me to pull them out at the dataset level. Since I do not want to show the toggle I was thinking that I could show one group minus these rows with the toggle then another group immediately below it without the toggle. do you know of a good tutorial somewhere to show me how to filter these rows?

    thanks again
    LVL 14

    Accepted Solution

    These 2 books are worth the £s:

    Hitchhiker's Guide to SQL Server 2000 Reporting Services (Paperback)
    by Peter Blackburn, William R. Vaughn

    Microsoft SQL Server(TM) 2000 Reporting Services Step by Step (Pro-Step by Step Developer) (Paperback)
    by Hitachi Consulting, Stacia Misner

    If you want more of a 'hands on' approach, there are some free training courses provided by MS, goto:

    Learning Center: Hands-On Labs Online

    There's 4 courses that you can do, and MS provide a virtual PC connection so that you can actually use RS and follow the training.

    Microsoft SQL Server 2000 Reporting Services: Managing Content
    Microsoft SQL Server 2000 Reporting Services: Manipulating Datasets
    Microsoft SQL Server 2000 Reporting Services: Exploring Data Regions
    Microsoft SQL Server 2000 Reporting Services: Authoring Basic Reports

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Hi, In my previous Article  ( discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now