Need help with a Pivot Table thought

[geospatial]

I am looking at creating a good amount of statistics from a block of data. For instance if I wanted to look at employee sales stats in a business that expands multiple locations, how could I go about looking looping through each location and creating a new table and chart for each location.

I have tried googling this, but I am not coming up with any ideas. I have an idea of creating a statement that will create an array of all the different locations. Then automate the creation of the pivot table using the cycling through the array as the Report Filter. Once the first pivot table is done, find the row the pivot table was in, go down two rows, and then place the next pivot table.

If this sounds like it would work please let me know. I will then work on the actual steps.

Thanks,

Jason

 

[sdruley]

Jason,

My first thought was using a power-pivot but it appears that the scope of what you are doing probably fits into the scope of a normal pivot design. The secret behind successful pivot tables is the autonomous selection of column headings with the first row of data just underneath the headings. The pivot table is designed so you don't have to "loop through each location" or "create an array of different locations". It was named pivot because the columns of data can pivot around other columns and even rows of data to see interesting relationships. Once you understand the pivot you will realize that the approaches you mentioned in your post are not necessary. In conclusion instead of "working on the actual steps" you will be moving rows and columns around in the pivot to get the data you need.

PivotTable reports 101 - Excel - Office.com
offers an example that is relevant to what you are doing.

Steve