How to prevent group by row labels

Question

I have an issue and do not know dose the power pivot is the best tool for this issue , or there is another Microsoft product I have to use

I need to allow the user to build their own report , But most of them are not analysis report , Ex

I need the user to generate a dump for customer by drag the drop customer Name , Customer Status , Customer Code ,......

No further analysis will be made ,

When I use power pivot and Do not place any column in values list box  But placing all columns in row label list , It generat the report and allow user to Drip down from first Name to Second Name , to status , Which I do not want I want prevent group by those column as it is not logical dril down .

---

khaled Mohamed

Answer 1

Hi Khaled -

Pivot table isn't really the best choice for dumping out lists.  But if your user already has the model and pivot table built in Excel 2010, you can just choose the Tabular format for the pivot table (PivotTable Design tab -> Report Layout -> Show in Tabular Form).  That will break each field out into its own column more like a list. 

Also, depending on your user's Excel savvy-level, you could also look into using Cube functions directly in Excel (no pivot required).  Or just let them use the data import functionality to pull the list into Excel.

If you have an instance of Reporting Services and would benefit from a web approach, you may also want to look at using Report Builder 3.0 for those type of user-built detail reports.  It has a pretty user-friendly, Office-type design environment that allows users to build their own web-based reports.

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Thanks for your reply

I try make a flattened pivot table , and It work good for me , 

But when I play with report layout options I was not able to convert a normal Pivot table to the Flattened pivot table, Is there a way to switch between normal pivot table and flattened pivot table ?

Regarding to Report builder , I try to give training for business users , But It is quite difficult for them ( It required knowledge of SQL statement , and it is better to use slicer than to use Report parameter )

---

khaled Mohamed