Show consolidated data in Pivot table

[#1]

Hi All,

I have developed a report which shows different branches in a list box.

Now I want to add Group as an additional row in the same list box

and on selection of Group, it should data for all the branches in the Pivot table.

e.g. List box shows

Group

Branch1

Branch2

Branch3

Now on selection of Branch1, Pivot table will show data of Branch1

and on selection of Branch2, Pivot table will show data of Branch2

and on selection of Branch3, Pivot table will show data of Branch3.

But now on selecting Group, Pivot table should show consolidated data of Branch1, Branch2 and Branch3.

I have reloaded data for branch1, Branch2 and Branch3.

[#2]

Hi Apurvaptkar,

I may not be fully understanding your issue, but...

Instead of trying to include "Group" within the same field selection as the branches, have you considered creating a separate field called "Group" - and then associate the branches to the various groups...?

ie

Group                    Branch

North England          Branch 1

North England          Branch 2

North England          Branch 3

South England          Branch 4

South England          Branch 5

Therefore, on selection of "branch 1", pivot table will show data of Branch 1

by selecting only 1 group, say North England, pivot table will show consolidated data of branches 1 to 3...

With no selections made in either group or branch, the pivot table will return consolidated results for all branches...

Hope this helps...?

Kind regards,

Rich

[#3]

Hi Rich,

How to associate the branches with Group?

Do you mean to create to listboxes?

if yes, how to associate it? please can you tell me?

[#4]

Hi Apurvapetkar,

You could load an additional table into your QV doc that provides additional branch details ("group" they belong in, branch address etc...) as an example....

Have attached an example...

Now the user can do reporting by "Group" / a particular branch / or a total view (depending on what selections have been made)...

Hope this helps?

Kind regards,

Rich

[#5]

Hi Rich,

Sorry did not get what you are saying

Also could not open the file you gave as I have Personal edition.

[jagan · Accepted solution]

Hi Apurva,

Your Branch  dimension has following values.

Branch1

Branch2

Branch3

Now create an Inline/Excel like this

Region:

LOAD * INLINE [

    Group, Branch   

     Group, Branch1

     Group, Branch2

     Group, Branch3

];

Now your Branch dimension and the dimension from inline table is joined because of same dimension name.  Now provide Group list box in your Dashboard.  If Group is selected automatically all the Branches associated with that Group are automatically selected.

Hope it helps you.

Regards,

Jagan.

[#7]

Hi Jagan,

Thanks a lot, It worked and solved my issue.