MSDynamicsWorld.com held a Google Hangout last month and I was lucky enough to be asked to be on the Panel. If you missed, here's your chance to see it again! (Yes, I copied this right from their channel!)
This panel discussion featured Mark Polino, author of the newly released book "Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013" along with Dynamics GP financial reporting experts Belinda Allen of Smith and Allen Consulting and Jared Hall, Program Manager at Microsoft. Learn more about Mark's new book and related topics around creating, managing, and distributing financial dashboards with Dynamics GP and Excel.
Some of the topics covered in our live conversation included:
* Dashboard Basics - who wants dashboards, what elements are a must-have, and what are the must-have skills of a dashboard developer?
* Excel 2013 dashboard fundamentals
* Running Excel Reports
* Managing dashboard requirements
* Drilling down: From Excel to GP
* Sharing and Collaboration Options
Jason Gumpart and MSDynamicsWorld.com is a great guy with great content. Subscribe to their YouTube channel and to their site:
MSDynamicsWorld on YouTube
www.MSDynamicsWorld.com
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
My Friend and fellow Microsoft Dynamics GP MVP, Mark Polino write an amazing book. I thought I would share my review with you. If you use Excel, you should consider adding this book to your bookshelf!
This is a great step by step guide for a tool that almost everyone owns and uses, “Excel.”
I’m excited to have this book in my library. Pivot Tables are such a powerful tool, and this book guides readers through steps that are both easy to understand and duplicate. With the focus being on Excel 2013, this is a great tool for any Excel user.
As I started writing this review, I made notes of my favorite topics and found too many to list here so I just grabbed my top 5:
- Security
- Manual vs. Automatic Refresh
- PowerPivot
- GP Drillbacks
- Conditional Formatting
This is a Great Excel Resource!
More InformationBelinda (the GP CSI) AllenBelinda@saci.com917-445-9070
What an exhausting day at Microsoft Dynamics Convergence for GP, SL, NAV, AX and CRM. Whew. I’ve attended quite a few sessions today and have filled my head with all kinds of ideas I can share you all in the coming weeks.
I started the day today having a very early breakfast with my fellow MVP’s attending Convergence this year. It was a great chance to meet and re-kindle relationships with other Dynamics people.
Then it was off to a deep dive in Project Accounting, then 50 tips for Dynamics GP, a session on the benefits of joining GPUG (GP user Group) and finally creating Dashboards with Excel.
Microsoft’s Jared Hall showed GREAT dashboard that is available for free on the Microsoft website.
http://blogs.msdn.com/b/gp/archive/2012/11/06/business-intelligence-investigation.aspx
Above is the link to the Microsoft blog where you can download this cool tool. One of the tabs provides you with instructions on how to set it up. Yes, it’s FREE!
I used this same dashboard (with ratios and CRM data removed) during my session yesterday “GP for Prospects.”
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
Below is an exert from a future "Tech Tips" that I wrote for the Microsoft Dynamics GP User Group (GPUG) to send to it's members. If you are not a member, you should review membership.
What are Excel Data Limitations?
Which version of Excel should you use?
You’ll want to be on Excel Version 2007 or 2010. Why? Column and row capacities in older versions cannot accommodate some of the files. Below is a table that shows size limitations of various versions of Excel. You’ll see that Excel 2007 and Excel 2010 have much larger row and column maximums than the older versions.
You should always work with supported versions of software; this includes both GP AND Excel.
|
Version
|
Max. Rows
|
Max. Columns
|
Max Columns by letter
|
|
Excel 2010
|
1,048,576
|
16,384
|
XFD
|
|
Excel 2007
|
1,048,576
|
16,384
|
XFD
|
|
Excel 2003
|
65,536
|
256
|
IV
|
|
Excel 2002 (XP)
|
65,536
|
256
|
IV
|
|
Excel 2000
|
65,536
|
256
|
IV
|
|
Excel 97
|
65,536
|
256
|
IV
|
|
Excel 95
|
16,384
|
256
|
IV
|
|
Excel 5
|
16,384
|
256
|
IV
|
Over a million records sounds like a lot, but it still might not be able to handle your needs, so you’ll need to address your Excel needs differently. Let’s review some options:
What is the difference between an Excel Table and an Excel Worksheet
Think of a table as being an “Excel Database.” In an Excel worksheet, you can put a single record on many lines if you like (like the following).
A table will be setup so each row is one complete record and each record is only on 1 row (like the following.)
Both of the above examples provide the same information, but by placing the data in a table rather than a worksheet:
- Excel will apply default formatting to any new data added.
- Excel will automatically apply the Filter setting. The headings for filters will remain when you scroll down.
- You can easily add totals to the bottom with the check box in the Table Tools Design tab.
- If you create a formula in a column, new data rows will have the formulas automatically copied to the new record. (This is a powerful feature if you use a calculated column in a Pivot Table.) This also means Pivot Tables will automatically capture the new data as well.
Understanding and using Tables is an important and necessary element to Excel Reporting.
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
Date: Thursday, January 03, 2013Time: 1pm – 2pm EST Register Now »
Join Me and NetCom Learning, for an interactive look at Microsoft Dynamics GP | Excel, as a GP Report Writer. Sign up for the complimentary learning session today!
In this session, we will cover:
- Build an Excel Based General Ledger Trial Balance using Excel Refreshable Reports
- Overview of Excel Report Builder
- Overview of building custom Excel Refreshable Reports manually
- Excel Pivot Tables - the "go to" for Business Intelligence
- Excel Pivot Tables for Ad Hoc Reporting
- Windows 8 OS Raffle!
- Q&A with Belinda
Register Now |
Featured Speaker
ME!
Belinda Allen
|
|
|
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
Come see me in person discuss SmarList Builder and Excel Builder at the (Microsoft Dynamics GP) GPUG regional meeting Tuesday, 11/27/12 at the Microsoft Office in Manhattan, New York.
Please join us for the next meeting of the GPUG NY/NJ Regional Chapter on
Tuesday, November 27, 2012 from 8:30am - 12:00pm EST. We're planning a great
meeting at the Microsoft office in Manhattan. Don't miss this exciting opportunity
for face-to-face networking with your peers and fellow GP users in your area.
|
|
| |
|
8:30 - 9:00am
|
Welcome & Introductions
|
|
9:00 - 10:00am
|
SmartList Builder
|
|
10:00 - 10:20am
|
Break & Networking
|
|
10:20 - 10:40am
|
SmartList Builder - Customer Examples
|
|
10:40 - 11:45am
|
Excel Builder & Navigation Builder
|
|
11:45 - 12:00pm
|
Planning of Next Meeting
|
Meeting Location:
Microsoft Office
1290 Avenue of the Americas - 6th Floor
Room: Belasco
New York, NY 10104
Registration Costs:
Basic Members: $20.00
Premium Members: FREE
|
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
Today’s posting is specific to Microsoft Office only, particularly Excel and Word.
When meeting with a customer yesterday, he mentioned having problems copying Excel spreadsheets into Word. When she chose to copy, the formatting was lost. She liked using the “Track Changes” feature for reviewing documents and information in Microsoft Word.
After taking this problem to my desk, I realized I had a similar issue: the paste option buttons did not appear on my screen.
To turn this feature on, click on File from your menu bar, then Options, then Advanced. Make sure the box for “Show Paste Options button when content is pasted” is marked. This feature allows you to retain formatting from the source file or the destination file, or to merge the formatting.
A couple of items to note:
- The buttons will vary depending on the program from which you are copying and pasting.
- Hover over each button to get a preview of how the paste will appear without committing to the paste itself.
- You may experience a problem and not see the additional buttons at all, seeing only the paste Text button. (See below for my fix.)
Pasting default options:
Keep Source Formatting (Default): This option retains formatting that was applied to the copied text. Any style definition that is associated with the copied text is copied to the destination document.
Merge Formatting: This option discards most formatting that was applied directly to the copied text, but it retains formatting that is considered emphasis (such as bold and italic) when it is applied to only a portion of the selection. The text takes on the style characteristics of the paragraph where it is pasted. The text also takes on any direct formatting or character style properties of text that immediately precedes the cursor when the text is pasted.
Keep Text Only: This option discards all formatting and non-text elements such as pictures or tables. The text takes on the style characteristics of the paragraph where it is pasted and takes on any direct formatting or character style properties of text that immediately precedes the cursor when the text is pasted. Graphical elements are discarded, and tables are converted to a series of paragraphs.
Seeing no buttons?
I had this problem with my Microsoft Word 2010 paste option. It is believed to be a conflict between some features of Skype and/or features of Chrome. I corrected the problem with the following steps:
- I added a custom group for pasting to my Home tool bar in Word.
- In this new custom group, I added all potential pasting options.
- Once I confirmed the Clipboard group now included all pasting options, I removed the new custom group I created.
For those of you using Office 2010:
There is a feature to “Paste Special” when inserting your Excel spreadsheet as an object using OLE (Object Linking and Embedding).
What does OLE mean really? In this example, it’s a way of linking your spreadsheet to your word file. You should be very careful with this option as there is a link between the two files. Later changes to the Excel file will alter the Word document as well.
In my customer’s case, the answer would be using a paste option, not the OLE option, so the revisions could be documented.
Because these programs create many questions, I plan to write more blog posts related to Word and Excel in the future, specifically connected to data in GP. Let me know if there is anything particular you wood like to see in regards to the integration of data between Microsoft Office and Microsoft Dynamics GP.
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
If you use Microsoft Dynamics GP and have ever wanted to easier way to monitor open purchase orders and line items that have been received and not invoiced, you’re in luck.
I will show you how to use Excel Refreshable Reports to monitor both Open and Historical Invoices. It’s easier than you might think!
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
I will be on the panel for this free Webcast answering your questions about reporting in Dynamics GP!
Join our panel of experts in financial reporting tools forMicrosoft Dynamics ERP to learn the latest thinking on the tools and techniques for building the right reports for the right audiences with the right data. Learn how top minds in the field are using the Microsoft technology stack to enforce compliance, improve secure access, and optimize data visibility and intelligence in different scenarios and in different types of businesses.Is your organization's financial reporting maximizing the tools at its disposal? Do you find yourself producing less than optimal layouts for your executive team based on the tools you have on hand?
Join us on Friday, April 20, 2012 at 11am EDT (GMT -4) to ask the experts your questions and learn about topics including:
- When to use out of the box reports vs report writers
- Potential regulatory hurdles for private and public companies that you should be planning for today.
- What to expect when moving from FRx to Management Reporter
- How to justify an investment in SQL Server Reporting Service report development
- When to embrace - and avoid - Excel reports
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070
As a user, my business partner was always asking "Where does our cash go?" With the use of smartlist, I was able to efficiently answer that question. To give him a year or month's worth of data was basically the same as saying "I don't know." It's too much information to process easily. So what did I do?
I created a smartlist in Microsoft Dynamics GP for the operating bank account, and as I reconcile to the bank (which I do almost daily) I send him a list of everything that was entered since I last reconciled. This is a MUCH smaller list of data and it's more easily digested and answers his questions just the same.
If you chose to do this, do not create your restriction based on transaction date, as today I may be entering something from yesterday or something for tomorrow. Instead use "POSTED DATE" which is the actual date the transaction was posted. This way you are giving a list of all transactions that was posted since the last bank reconciliation, regardless of the transaction date. Also, for consistency, use the previous day as the ending date because you may be posting something else today but yesterday is completed forever! This concept works even if you do not reconcile to the bank daily.
The next time someone says "where does our cash go?" Answer "Let's watch it together for a while and see!"
Belinda (the GP CSI) AllenBelinda@saci.com917-445-9070