Solved

Expand/Collapse Grouping in Reporting Services

Posted on 2006-12-01
4
11,007 Views
Last Modified: 2010-05-18
I am having trouble understanding how to create drill down "expand/collapse" groups.

I want to have two groups:  Builder, Project, then list of plans under that
I want to have an option to show just the builder level, just the builder/project levels, or all detail

In the table I click Properties.  I then went to the Groups tab and created two groups. One for the Builder and one for the Project.  I see the 'Details Grouping' button but I don't understand exactly what is suppose to link to what.

Can someone please give some assistance with this.
0
Comment
Question by:UniqueData
  • 2
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
Yurich earned 500 total points
ID: 18064415
As the beginning, let's clarify that one builder can have many projects, and one project can have many plans. So, you can have something like this as a record:

Builder              Project           Plan            More Details
=====================================
Bob the Builder  Kindergarden  12340593    01/02/2006

Now, delete a table you created as it will be easier to create a new set up than to fix whatever you created.

* I'm giving you just an example - you can of course modify it as it better suits you.

1. Create a new table and drop all fields that you want to see at the lowest level (all details) - say two fields Plan and More Details. Go to preview and confirm that it lists all details as expected.

2. Add the first group - Projects. Select your table (click any cell in the table), and right click the grey tabs on the left of the table, select insert group and select Project field to group on projects. Drag and drop a project field into the group header. Sometimes it looks better when you have indents for drilling down, so you can add a column to your very left, merge it with it's right neighbours, and drag-n-drop your project in there. You can omit this step (indentations) if you don't feel comfortable in doing it.

3. Implement first drill down. Click the cell where you have your Project field, press F4 to go to properties, and note the name of the cell (at the very top of properties). You can alternatively, just point (without selecting) to the cell and a little pop will give you the name of the cell. Now, select your table details (click grey tab on the left side of your table to select a row with Plan and More Details field. Press F4 to go to properites, select visibility, select "Toggle Item" and select a textbox control you noted before. Set Hidden property to true.

4. Checking out. Run your project and see that you have only project listed now in this manner:

+ Project #1
+ Project #2
+ ...

If you click a plus sign, it will show you your details:

- Project #1
     10340234   01/02/2006
     20340203   03/06/2006

- Project #2
     20304020   ...
     ....             ...

5. Add the last grouping. If it was added inside your first group, select your table, properties, go to groups and click the up arrow once to put your second group to be on the top (to be the outer group, not inner). Again, right click left grey tab of your table and add a group, select this time a builder. In general, follow the same fashion as for the previous group. Drag and drop builder filed into the grouping header, note the cell name, select a group header of the inner group, go to properities, visibility, select a cell name in the Toggle Item and set Hidden to true...

6. Final check. If you run  your project now, you, first, should be able to see only builders:

+ Builder #1
+ Builder #2
+ Builder #3

if you click + sign, it will show projects for your builders

- Builder #1
   + Project #1
   + Project #2

+ Builder #2
+ Builder #3

you can expand projects to see the plans:

- Builder #1
   - Project #1
        020340023   02/03/2006
        020349930   03/04/2006

+ Builder #2

You can of course add all builder level details to the header of the builder and project level details to the header of the projects and you can play with visibility to set some stuff to show only in collapsed views and something only in expanded. The guide though should give you a fair idea of what you can do and what to expect from drilling-down features of MS Reporting Services.

Good luck,
Yurich
0
 
LVL 7

Author Comment

by:UniqueData
ID: 18071442
Wow, if there was a way to give more than 500 points I would!!  Thank you for such a detailed response.  It was exactly what I needed and extremely easy to follow!!!

The only other question would be how to start everything collapsed by default.
0
 
LVL 21

Expert Comment

by:Yurich
ID: 18071838
you're welcome ;)

if you follow as described above, you should everything collapsed (only Builder names, information), e.g.

+ Bob The Builder
+ Phil The Doctor
+ ...

If it is not, check your Hidden property for the rows you had to set.

Cheers,
Yurich
0
 
LVL 7

Author Comment

by:UniqueData
ID: 18071919
ah, yes, I forgot the Hidden step of your instructions.  

Thanks once again.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

15 Experts available now in Live!

Get 1:1 Help Now