SharePoint List using Choice field with checkboxes/multiple selections - to Excel for Reporting

Question

I work for an organization that allows us to create SharePoint Sites / lists, etc. I have NO backend access to SharePoint, nor do I have access to SQL, SSRS, etc. I do have Access and Excel.

That said, my list in SharePoint is a list of "unique" projects. Each project has a choice field, with an option for multiple check box selections for "product". So - one to many. One Project may have 1 or MORE Products. I have been asked to generate graphical representation/report - using Excel - of the count of PRODUCTS in our PROJECTS.

When I export data to excel - and attempt to create a pivot table, each project has the PRODUCT lists sorted by a ;#. Which makes it impossible to get an aggregated count by product.

I have multiple fields like this - and hundreds of projects and products - so it's not conducive to manually break them all out - and I'll have to run the data on demand or at a minimum monthly.

IS there a way to process this through Excel with limited manual intervention to group / count the individual PRODUCTS?

Answer 1

This is how the lookup data is stored internally. Your best option is to create some client side JavaScript code which reads and formats the data in the required format and dump it into a CSV file. Then open the CSV file in Excel.

You can execute the JavaScript code by opening browser console window.

1. Querying data in JavaScript
2. Expand lookup fields
3. Create CSV file using JavaScript

Another Option

Create a REST query and execute it in a new browser tab. This will give back the JSON/XML format. Now convert this to CSV format using online tools -

1. https://konklone.io/json/
2. http://www.convertcsv.com/xml-to-csv.htm