Need help for grouping data.

[agni_gold]

I am attaching the data in excel , and i want result  like attached screenshot.

I have tried many ways but not able to do this.

Please help

[stigchel · Accepted solution]

Have you read the comments in that blog? One is from HIC explaining how he made that:

The pivot table is easy to make:

• Create a pivot table with Node1..Node7 as dimensions.
• Make the dimension columns narrower.
• On Properties - Style, check the "Indent Mode"
• Also check the "Use only first dimension label"
• Uncheck "Vertical Dimension Cell Borders"
• On Properties - Presentation, check the "Subtotals on Top"

Done!

HIC

[agni_gold]

Need one more help , i dont want these dashed (Null) fields.How can i do this

[agni_gold]

I dont want these -- symbol (Drill down )

[stigchel]

In the dimensions tab set suppress when value is null for the desired dimensions

[agni_gold]

Yes i have 5 dimensions , and i have check the suppress when value is null for my last 4 dimensions then it reduce my parent values also . You can see below...

[stigchel]

Is there a question in this?? When there is no data there is no data to display. You could add a total text for missing levels, something like this:

LOAD [HFM Account Parent],

     IF(len([Parent Description])>0,[Parent Description],'Total') as [Parent Description],

     Child1,

     IF(len([Child Description1])>0,[Child Description1],'Total') as [Child Description1],

     Child2,

     IF(len([Child Description2])>0,[Child Description2],'Total') as [Child Description2],

     Child3,

     IF(len([Child Description3])>0,[Child Description3],'Total') as [Child Description3],

     Child4,

     IF(len([Child Description4])>0,[Child Description4],'Total') as [Child Description4],

     Rowno() as Amount

FROM

(ooxml, embedded labels, table is Hirerchy);

[agni_gold]

Thanks friend , hope customer will like this thanks for this great help.

Can i get back to you if i will face any problem ...?

[stigchel]

Well you're welcome although, quite frankly, you are a difficult 'client'. I'm generally available for anyone in the community if and when I have time next to my normal job and other activities.

You need to think how you yourself can contribute e.g.:

- Searching and reading up on relative content related to your questions before posting one

- Posting clear questions (take some time and  thought into how others not familiar with your problem will perceive your question) with preferable attached sample documents that demonstrate your problem and expected outcome

- Mark answers as helpful and/or correct so that others can find answers to similar problems,. Contributing members will feel themselves more inclined to help you if you do.

[agni_gold]

yes , before posting any question to community , i always search and read maximum , but today is the quick requirement comes thats why i dont have time .

Thanks friend.

[stigchel]

That's great, so are any of these answers helpful or correct? Please mark them as such.