How do I insert a column into another so it substitutes the viewed column when selected?

[fabio_moreno]

Not sure I phrased this question very clearly.

I want to have two dimensions, with one being subordinate of another.  So what I want is a table that initially shows one dimension and its results, but when i click on a line of that dimension, it breaks down the results for the sub-items within that selected dimension, substituting the column and now showing only the results for the sub-items, hiding the dimension initially shown.

Here's an illustration of the table I have and the table I'd like to show:

For instance, i have the 3 columns below:

And I want to show a table that contains the middle column, but initially displays two columns like this:

But when I click on a line of this dimension - such as "Bologna" - the column of the dimension is replaced with a column of its subgroups that are related to "Bologna", like this:

I have a demo application that uses this feature, but I can't see inside to figure out how it's done.  I'm thinking about perhaps a container box of some sort, but it seems like there should be an easier solution.

If anyone could assist me with this, I would greatly appreciate it. 

[kaushiknsolanki]

Hi,

I guess what you need is a pivot table.

Just change the type of chart from straight table to pivot table.

If this is not what you need then please share the demo app with us.

Regards,

Kaushik Solanki

[#3]

A pivot would be lovely for this, as Kaushik suggests, as it can expand or hide the sub-items automatically and you need do nothing.

However, to do what you want...

On the ProductSubGroup dimension, select "Enable Conditional" and use the expression,

GetSelectedCount ( ProductSubGroup ) <> 1

Now, on the Item dimension, enable conditional and have the expression

GetSelectedCount ( ProductSubGroup ) = 1

[fabio_moreno]

Mark and Kaushik, thanks for the responses.

The pivot Table doesn't quite do what I want.

Mark's answer gets me halfway there - it bring the right info with the right action.

However, I'd like to have the "Item" column substitute the "ProductSubGroup" column.  Using the "GetSelectedCount" command expands the table instead.

Do you know a way to keep the table at two columns instead of expanding it into a third?

[#5 · Accepted solution]

Did you put the Enable Conditional on both columns?

The way I've described it, the ProductSubGroup will be hidden if there's a single selection made against it, and the "Item "will only show when there's a single selection against the ProductSubGroup.  Given that the conditions are mutually exclusive,

Just to clarify my understanding, you've two dimensions:

• ProductSubGroup
• Item

One expression sum(something)

The conditional thing I described needs to be applied to the two Dimension columns.

I don't see how you'd end up with both showing at the same time.

[fabio_moreno]

Mark, thanks again for helping out.

Yeah, I inserted the Enable Conditional on both dimensions.

The result I get is this -

If there's no selection, it shows up as I wanted, with two columns:

However, when I select a line of the shown dimension (for example, "Bologna"), the table shows this:

So I get the item breakdown, but the final result that I want is for the "ProductSubGroup" dimension to disappear, so it shows only two columns.

It seems like an easy thing to correct, but I've played with some of these conditionals and haven't achieved the desired result. 

Can you see a way to get the columns to behave the way I want them to?

[fabio_moreno]

Ok, I figured out where I went wrong, and I was able to do what I wanted. 

Thanks!