Block some categories [SOLVED]

[qvixote]

Hello!

I have a three levels hierarchy and I need to "block" some categories in the middle level, so the users can't get to the bottom level by those categories. Is there any way to do this?

Thanks.

[cognos810]

Hello qvixote,
One way that comes to my mind.....
You will have to create SETS of members. So in your case, for your middle level, create a SET expression which could have an expression like
Middle level set expression:
EXCEPT([MIDDLE LEVEL NAME],
SET([MEMBER YOU WANT TO HIDE 1], [MEMBER YOU WANT TO HIDE 2]...,[MEMBER YOU WANT TO HIDE N])
)

Next, go to Drill behavior and set the Drill up and drill down behavior for the the Top LEVEL data item as "Change Expression" and select the Data item with your set expression for the filtered middle level in it.

Similarly, define a member set for your bottom level and apply the "Change Expression" drill behavior to the Middle level data item.

Let me know if it works,

-Cognos810

[cognostechie]

WOW ! Thanks cognos810.  It's nice to know that technique for doing it in RS.

qvixote - If you want to do this Transformer, then once the categories are generated, you can go to the diagram mode (there is an icon on the toolbar) and apply security to the middle category there which will prevent going below that. That being said, this should be done only for certain users/groups. If you want to do this for everybody then why even have those levels in the hierarchy?

[qvixote]

Hello again! Thanks both for the answers, but I guess I haven't been understood. I'll try to explain it better. My three levels are something like Country -> State -> City, with only one Country named "Country" (it's really the top level), and Sales as a column. Now imagine a crosstab in a report with Country in the rows and drilling activated. If I clic on a Country, I'll drill down to States and see total sales for every State. In this point, I need every state to be shown, but for some specific ones I need to deactivate drill down, so users can clic to see City detail for all states except those specific ones.

Trying to exclude the lowest level children of those states in Transformer result in no showing the states at all and not counting the sales of the excluded lower-level categories in the Country total.

I haven't tried the solution proposed by Cognos810 yet, but I think it woud hide middle level categories, and that's not what I'm trying to achieve.

Any solution (Transformer or Report Studio) will help.

Thanks again.

[cognostechie]

What you are describing can definitely be achieved in Transformer and instead of writing the details in your 2nd post, you should have described the problem in your original post. It is always best to describe the problem with examples which will get you the answers fast

You tried Exclude which is not the correct option for your scenario. What you need is Summarize.

These are the options available in Transformer

Exclude - Omits a category, its descendants, and their data.

Cloak - Omits a category and its descendants, but retains the rollup values in ancestor categories.

Summarize  - Omits descendants, but retains their rollup values.

Suppress  - Omits a category from reports based on the cube, but retains its rollup value in ancestor categories.

[qvixote]

Thanks for the explanation, and sorry for not explaining my problem from the begining, I thought I was been clear enogh. My fault.

But I still have a problem: the Summarize option is disabled in the contextual menu; the only options enabled from the list you gave me are Exclide and Suppress, and I don't know why.

[cognostechie]

You are trying to assign security to the entire dimension. You have to create a View and assign security to the View.

When you are in the Diagram mode, on the left pane you will see the dimensions. There are two tabs in the left pane. 1st one is Dimension, 2nd one is Custom Views tab. Click on the Views tab, right click to create a custom view, give it a name and click 'Ok'. Right click on the view you created and then click on the dimension , then you will see Suppress enabled.

[qvixote]

Many thanks again, cognostechie.

I created a view and could apply Summarize to categories, that's great. But now what? I've created the cube again, published it again, but in report studio I still have only the original dimension. Is there any additional step I'm missing?

Thanks.

[cognostechie]

Yes, there are some more steps but I thought you knew it because you had already applied the exclude option earlier.

If you have already applied a group/role/user name  to the View then go to step 6 directly:

1> Double-click on the View you created
2> Click on the 'Assign Security' button and choose a group/role. Choose the group in which you belong and follow the rest of the screen
3> Click on the Country Dimension and assign the Suppress option
5> Close the Diagram mode and go to the Default View where you will see the Powercube and the Custom View windows.
6> Drag the view from the View window into the Powercube (Not just inside the powercube window but actually into the cube itself). After this, you should get a + icon on the cube so that you can expand the cube and see the view under the cube.
7> Generate the cube again.

This will apply the security on the view and since the view is assigned to a group/role so anybody belonging to that group would inherit that security. Now you won't see the lower levels in Report studio but the data will be included in the higher levels.

Hope it helps !

[qvixote]

Yeeeeeeeeeees, that made it! Thanks a lot, I was so stuck here. Every day you learn something new.

Again, thank you so much