Multi level drill down

I'm wroking on 4 level drill down report. The first and the second level drill down, show and hide are working fine. But I have trouble to set drill/how/hide on the third level. It does not work as expected. I need help on how to set the third level correctly?

For an example, the following level, level State and City works fine, but get trouble while setting CountryID level. What is the right way to set the third level ?  Thanks.
 
ContryID
    StateID ...
        CityID ...
LVL 1
minglelinchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What are the common columns between levels 2 and 3?
0
minglelinchAuthor Commented:
There's a level id conected, but no common field between level2 and level3 shown in the report. Right now I use a store procedure to select all data. The selected data include ids of each levels. Thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So, no common columns, which means it's not truly a parent-child set of data, so explain in great detail how this 'drill down' between Levels 2 and 3 would occur.

Sounds more like two different levels of detail.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

minglelinchAuthor Commented:
One store procedure select all data for the report. Each level has relationship with PK/FK.

I build the grouping using the way that is shown in this link http://www.dailymotion.com/video/xspk60_reporting-services-grouping-in-tables_tech

Instead of using id field, I use the name field.

I attached three screen shots: Design time show, preview show and two levels expended show. for the third level, I have not built drill down yet, as I need to know how to do it.
DesignTime.png
ReportShowUp.png
InnerTwoLevelsExpended.png
0
minglelinchAuthor Commented:
I attch another design time screen shot which shows the grouping clearly.

'Instead of using id field, I use the name field.' I mean I use each level Name to display in teh report for grouping. In store procedure query, I have selected each id field for all levels.
designtime.png
0
ValentinoVBI ConsultantCommented:
Looking at ReportShowUp.png, as far as I can tell you've already got the groupings to work and you want to hide/show the grey area with a +/- icon in the brown area, right?

Here's how you can do that: select the first textbox in the brown area and take note of the value in the Name property (Properties window - F4).  Now open the Group Properties of the Row Group that corresponds to the grey area.  Switch to the Visibility page and check the Display can be toggled by this report item checkbox.  Select the value which you took note of earlier.  If you want the grey area to be hidden by default, activate the Hide radio button.

In the case that you didn't change the default radio button, so you kept it at Show, you'll notice that the +/- icon works incorrectly (inversed).  To solve that, select the first textbox in the brown area and set the InitialToggleState property to True.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
minglelinchAuthor Commented:
Great! Thanks for the post. I still need a little more help.

1. I changed the desin time a little. See the yellow circle in attached designtime file.

2. If I set Visibility of Group Properties at group level which is the Red Circle in attached designtime, when I preview the report, I get error:
The grouping 'GName' have 'GName' as a toggle item. Toggle item must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and cannot be contained within the current report item unless grouping scope has a parent.

3. So I changed to set Visibility of Group Properties at Static level which is the Green Circle in attached, the error is gone, and report shows. Great! See ReportShow file. But the Green Circle one is actually the second textbox. So When I clicked the first bule circle, instead of lower level hidding, the clicked group fields which belong to Static level hide, see the attached ClickedFirstOne file.

I need the lower level hide/show when the higher level is clicked.

I appreciate further help.
designtime.png
ReportShow.png
ClickedFirstOne.png
0
ValentinoVBI ConsultantCommented:
You're changing the Visibility settings for the wrong group.  The one indicated with the red circle is the top group, you don't want to hide/show that one.  Instead of that, you should be setting the Visibility settings for the second yellow item in the Row Groups window.
0
minglelinchAuthor Commented:
How to set Visibility settings for the second yellow item in the Row Groups window? This is what I'm looking for.

See the file designtime, the Row Groups window has title group and static group (red and green).  I have set either of the two, it does not work correctly.

I know I did not set Visibility settings on correct row. If I set Visibility at the lower level from the first cell of table row, which is the second row in yellow circle, the drill down works quite wrong. I can only set Visibility from Row Groups window. But where is it in Row Groups window?
0
ValentinoVBI ConsultantCommented:
See attach, I added a blue circle.
designtime.png
0
minglelinchAuthor Commented:
Great Solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.