Home > Excel Report Builder, General - Builders, Sales > Excel Report Builder – GP2010 Pivot Tables – Sales By… anything!

Excel Report Builder – GP2010 Pivot Tables – Sales By… anything!

I do a lot of “Sales by” SmartLists for clients for sales analysis.  Sales by Customer; Sales by Salesperson by Month, etc.   You often end up with quite a large list of SmartLists for each different report that different parts of the organization wants.    That is why I am pretty excited about the new Pivot Table option for Excel Report Builder in GP2010.    (Now – don’t stop reading if you are on GP10, I have a trick for you so that you can use Pivot Tables too!… If you are on GP9, sorry…)  

Now – If you haven’t used Pivot Tables in Excel – boy have you been missing a great tool for analyzing data.  They make it so easy in Excel 2007.  If you are a newbie, play with that first to get an idea.  Take a SmartList and dump the data to Excel. Go to a new worksheet, press the Insert tab on the top and on the far left you should see PivotTable – click on that.  A screen will come up asking for your range of data to select – go select your exported data.  and Press OK (See Excel help for more details …)  You will now see this screen:

Now you want to drag your fields into each of the sections – like this:

And by magic – there is your summarized data.  
Want to add customer?  Just drag that field into the proper section  
Want a report for a certain date range?  Drag the Document Date field up to the Report filter and change the dropdown to only select certain dates.
Want each month in columns?  Add month to the column section… and so on.  So easy most people can get the hang of it.

(note – Double click on the field name in Values section to get the screen to select Sum rather than the Count that Excel will typically give you – also you can format your data here)

Now I am going to assume that you already have a Summary Sales by SmartList that you are using in SmartList Builder (See my post – Summary SmartLists in Builder (Sales by Item for a date range, etc)) so I won’t cover those same items here.   Remember items like restrictions, proper signs & quantities.     You can duplicate that SmartList into Excel Report Builder (See my post – Making a copy of a Builder SmartList) … or start a new one. 

The steps for exporting are exactly the same as a regular Excel Builder Report.  But when you launch the Excel you will not see a screen full of data, but instead you will see the first screen shot above.  In the field list you will see all of your fields selected on the Excel Builder for that report. You can build it for your users and save it – or leave it “blank” for users to build as they desire.  (Note – You can close the section on the right if you don’t want users to be tempted to change it – but you will also want to make sure they don’t have “write” privileges to the folder in Windows either!)

One of the feature of PivotTables you will not be able to use is some special date features in Excel ( the ability to select Years & Months for a date field) – but adding a couple calculated fields in Excel Report Builder will take care of this problem: 

  • Month – field type integer – MONTH ( {table.field name} )
  • Year –  field type integer – YEAR ( {table.field name} )
  • Quarter  –  field type integer –  DATEPART (q, {table.field name} )

Before you know it you will have something like this:

There are a lot of tricks to PivotTables in Excel – if you are not familar with them I would suggest looking on the web or Youtube for some training to best make use of your data.

Now – How about those of you on GP10?  

Make your Excel Report in Builder like normal.  (Or use one of the “stock” ones from GP)  Launch your Excel Report. Go to a new worksheet and press the Insert tab on the top and on the far left you should see PivotTable – click on that.  On the screen that comes up change the option to “use an external datasource” and press Choose Connection button

the next screen will show your data connection created by Excel Report Builder, select it and press the Open button on the bottom (not shown)

Press OK back on the Create Pivot Table screen…and you will find yourself looking at the Pivot table wizard with your GP data!  (You could delete the worksheet with the data (one made by Builder) and save this Excel as a different name.  Now it won’t be overwritten by Excel Report Builder.)

Try it – you will start to love PivotTables too…

Happy Building,

PS – If you are on Excel2003, and you have the download to open Excel2007 files – you can still use the PivotTable features – it’s just that the steps are completely different – if you web search you should find the how to this…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s