Archive for the ‘Excel Report Builder’ Category

Want to duplicate an GP Excel Report plus a few fields?

October 29, 2010 1 comment

Want to duplicate an GP Excel Report plus a few fields?  Or wondering what these “Data connections” are in SmartList Builder? Or puzzled on where the “Data connection” went in Builders for GP 2010?  Wonder no more…

The GP Excel Reports are based off a series of SQL views added to the GP database. The “Default” ones are all the data straight from the view, others are the view with some restrictions added. (Same as the SmartList favorites they are modeled after.)

The programmers for GP Builders added these views as “Data Connection” in the table section for the Builders.  So you can use this data in anything you build. 

They have their own security (similar to the SQL tables/views) under MS Dynamics GP > Tools > SmartList Builders > Security > Data Connection Security.  

In GP2010, the “Data Connection” option disappears in the table section in Builders unless there are Data Connections enabled in security. (Same with the SQL table option!)

Next time you want to quickly add the data from an Excel Report (which you know are a lot like the SmartLists)… Try the Data Connection.   It might work for you.

Happy Building,

PS – for those of you familiar with SQL – check out the design of these views (names are the same as the name for the Data connection) you will learn all sorts of interesting things about the GP data and tables.  (All these neat SQL Functions “dbo.DYN_FUNC_XX”  like  DYN_FUNC_Account_Category_Number)


Copy a basic SmartList from GP into SmartList Builder?

October 15, 2010 1 comment

I hate it when I am wrong!   I have been posting that there is no way to copy a basic SmartList (one that comes with GP without Builder) into Builder.  While that is true…

Someone at Microsoft has been recreating these SmartLists with ALL the tables in Builder.  (The templates have been around a while for download but earlier versions weren’t very good.)

SmartList Templates for Microsoft Dynamics GP 10.0
(These require a CustomerSource Logon to download.)

I haven’t looked at all of them, but most seem very good.**  Check out all of the tables used on the Receivable Transactions SmartList:

Remember that Advanced GoTo’s don’t import so you will need to add GoTo’s to:

  • Payables Transaction Inquiry
  • Purchase Order Inquiry
  • Receivable Transaction Inquiry
  • Sales Inquiry (Sales Order Processing)

Also remember that not all SmartList functionality is reproducible in Builder.  For example, on Sales Transactions the default GoTo opens Entry if the transaction is open and Inquiry if the transaction is in History.  This is typical for many transaction SmartLists.  We can’t reproduce this in Builder (yet?… hint, hint…).  We have to build two GoTo’s, one to the entry screen and one to the inquiry screen and pick which to make the default GoTo.

One more thing…  The SmartLists from GP change the field names for user defined fields, you will need to do this yourself in Builder.

Happy Building,

**Here is a list of issues I see (to be revised as more are identified):

  • CUST_ADD – Customer Addresses & VEND_ADD – Vendor Addresses:  link to Internet Information table, see my post on this
  • ITEM – Items: link to Multiple Manufacture Items Master – this table can contain more than one record per item, so could cause Items to appear multiple times, if the table is used this way

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

July 22, 2010 2 comments

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…