We can generate pivot reports directly from SharePoint list items.
Step 1: Create a new excel file and go to File> Options > Add- INS
Step 2: Go to Manage Dropdown> COM Add-Ins > Click on Go Button
Step 3: select Microsoft Office Power Pivot
Step 4: Now go to go Power Pivot section and click on manage from the ribbon.
Step 5: from Manage window click on “Get External Data”, then “From Data Service”, then “From OData Feed” as shown below:
Step 6: Provide the SharePoint List Web service URL
http://000051D:7777/sites/uat/_vti_bin/ListData.svc/<ListName>
Step 7: you will get next window listing your list. click on finish button.
Step 8: it will import all the list items as shown below, then close the window.
Step 9: choose pivot table from ribbon as shown below:
Step 10: Select new worksheet to proceed with your Pivot table.
Step 11: Drag and Drop Rows, Columns, Filters and Values Based on Requirement from right side field lists.
Step 12: to arrange all the fields, follow below steps:
Right Side Pivot Table Rows Panel >Right Click on Column Properties > Field Settings > follow the bellow Screen shots:
Step 13: now you can upload this document into a document library and provide the document link to end user. End user can download the pivot which will get updated dynamically with list items available in the list.
Prasanta Barik 
