Drill Down to multiple fields

[praveenak]

Hi All,

I have a situation here.

I have three fields coming from two different tables.My situation is like when I drill down a field in Straight table how can I display two fields in the next hierarchy level of the group.

For example:

A Table shows like below,

Name	Region	Sum(sales)
xxx	ppp	###
yyy	qqq	###
zzz	rrr	###

When selected/drill down on region, it should look like below,

Case-1: provided the State and City are coming from same as table as that of Region

Case-2: provided the State and City are coming from different table as that of Region

Name	State	City	Sum(sales)
xxx	ttt	mmm	###
yyy	sss	nnn	###
zzz	ggg	ooo	###

Thanks in Advance

[MK9885]

I'm not sure I correctly understand you...

Help me understand more please?

You want

Name

     State

        City    

          Sum(Sales)

In the above way? If that's the case then add dimensions in same way

Name, State, City and then expression as Sum(Sales). You'll get that view.

I don't think it will matter if the fields are coming from different table. As long as there is a link between these fields, the data will populate and you will be able to make selection.

[praveenak]

Hi,

I want like below,

Name     ------------------------------------level-2 hierarchy

     State,City     -------------------------level-1 hierarchy (both state and city are at same level)

          Sum(sales)      -----------------level-0 hierarchy

and this to be happened when I selected a Region in table like below

Name     ------------------------------------level-2 hierarchy

     Region     -----------------------------level-1 hierarchy

          Sum(sales)     ------------------level-0 hierarchy

i.e., The state and city are to be at same hierarchy level instead of city being the next leaf level for State

Hope this gives clear picture.

[MK9885]

State&' '&City as StateCity

You can use the above syntax to join 2 dimensions into one. Use that in script editor.

and in front end use

Name as first dimension

StateCity as 2nd Dimension

Sum(Sales) as expression

For 2nd table

Name as first dimension

Region as 2nd dimension

Sum(Sales) as expression

[praveenak]

Thanks Arvind for the solution. But am looking for the State and City to be different fields rather making them as single field.

[MK9885]

You'll still have State and City as different fields and then you'll have a new field StateCity.

You can't have 2 fields as one in a Pivot Table. They are separate dimensions, you can use above syntax I mentioned and create a new field instead and only use that field in Pivot Table.