Topic: ‘Reporting’

With the release of Microsoft Dynamics GP 2010 and Microsoft Office Excel 2010, see any naming correlation? You will have more options, more views and even more reporting power, right at your fingertips.  

Without diving into the “What’s New” with Microsoft Dynamics GP 2010 or Microsoft Office Excel 2010 at present I would like to take this opportunity to talk about Microsoft Excel Pivot Tables in the 2007 version.  Pivot Tables are an important piece to the end result and you will need to know a little more about them in order to get the best usage from your data extrapolated from Microsoft Dynamics GP,  Analysis Services or Smartlist. 

The learning approach here is going to be from an Excel perspective.   We are going to cover the basics of Microsoft Excel 2007 Pivot Tables.  We will learn some important terms, definitions and included as a link is the full article with practice example that you can use to create your own Pivot Table in Excel 2007.

Once you see how Basic Excel Pivot Tables work you will see the huge benefits you can gain from incorporating products like Analysis Cubes and future Excel versions.

To download the full article with step by step instructions to create a pivot table using Excel 2007 click the link http://www.box.net/shared/xts1xymlqk

To access the Excel spreadsheet used in the practice click the link http://www.box.net/shared/q9hchzc5br

One of the default cubes in the Analysis Cubes for Excel product for Dynamics GP is the Receivables cube.  It can be used to generate refreshable aging reports via an Excel Pivot Table.

To do this, users should add the Company ID attribute to the Report Filter area to filter the results for a single company.  Select the Aging Periods attribute for the Column Labels and the Customers attribute as the Row Label.  After that, all that’s left is to add the Aging Amount measure as the Values section in the PivotTable.

If the aging routine hasn’t been run recently for your company database, however, an area of confusion may arise as users try to tie the cube aging report to the one found in GP.  This has to do with how the aging routine and the cube data load work together to populate the cubes in the data warehouse.

When the aging routine is run in GP (Tools >> Routines >> Sales >> Aging), it updates several fields in the RM20101 table.  If documents in this table have aged to the point that they should be bucketed in a different aging period, this routine ensures that the aging period index for that document is updated appropriately in the underlying GP table.   Coincidentally, it is from this very same table from which the Receivables cube draws its data.  Not surprisingly, the data load for the Receivables cube will only be correct as of the settings identified during the last time the aging routine has been run. 

Therefore, in order to ensure that amounts from the various Receivables documents are bucketed in the correct periods, you may want to consider automating the Receivables aging routine to run just prior to the data load for the cubes.  Additionally, you’ll want to make sure that both procedures occur on the same day to ensure that documents are bucketed correctly.  Doing so will ensure that the data in the cube will tie out to any Aged Trial Balance report printed from GP during the course of the business day immediately following the data load.

Although several methods exist for automating the aging process, including requiring your users to run the process manually at COB.  One of the best descriptions of how to accomplish this process I have seen resides over on Michael Johnson’s blog.  Be sure to check the following link for a detailed description for automating your aging process: http://mbsguru.blogspot.com/2009/04/automatic-aging-for-gp.html

Have you ever had to deal with recalcitrant text boxes in the Visual Studio SSRS development environment? You’ve sized them to grid, you’ve aligned them to grid, you’ve manually entered height and width dimensions, but nothing works: when you click the Preview tab, one text box extends just a couple of pixels past the other. You check the font size of the text in the box: no problem there, it fits in tidily.

The issue may be caused by internal padding in the text box. Right click a text box and select Properties. The following dialog box will pop up:

Now click the Format tab:

Check the amount of internal padding (“Amount of space to leave on each side of report item.”)  The Top and Bottom padding, combined with the size of the text, might be just enough to push your text box out of kilter. Reduce the number of pixels around the text appropriately and your text box will be able to behave exactly the way you want it to:

Note that it is also possible to adjust multiple text boxes by displaying the standard Visual Studio properties box using the F4 key,  and then changing the Padding property’s values:

However, there are some conditions that need to be met before the Padding values can be changed for more than one text box at a time using the standard VS properties box—in certain situations VS will not allow you to change the Padding for more than one text box at a time.

Gerhard Venter
Senior Consultant
I.B.I.S., Inc.

In this ever changing, fast paced, economic climate the companies that will succeed make the decisions that position themselves for the best return on investment…before anyone else.  “How are they doing this?”  By taking consensus of budget and forecast data and executing quickly.  Using application based budgeting and forecasting tools in addition to their reporting and accounting systems.  Providing decision makers with the most accurate data possible, furthermore, enabling them to make the decisions that drive their company. Microsoft Forecaster 7.0 is just that.  Either when used with Dynamics GP and Microsoft FRx Reporting or as a stand-alone application it gives companies that leading edge.
The next question, “What about excel for budgeting and forecasting?”  Excel is great and yes it works for budgeting and forecasting.  However, managing multiple spreadsheets with links, formulas and/or large amounts of data can be hard to read, audit and is subject to higher error risk.  Therefore, included below are some of the advantages of using Microsoft Forecaster 7.0 and how it can benefit your company.
The Microsoft Forecaster 7.0 Advantage
1.  Audit Functionality

•  Tracks changes made to data when the Audit Trail History logging function is enabled.    
•  Reports are available to view or print changes made to raw data.

2. Security

•  A hierarchy of Groups is setup defining privileges or functions users can access.
•  Another, optional, component allows menu assignment to users providing additional security. 

3. Workflow

•  Customizable workflow is an optional notification process providing communication to users at all levels of the budget in progress.  Using a defined routing hierarchy users submit, review and approve budgets by status method.  
•  A workflow status can be added or customized. It can be defined to e-mail the status notification and/or lock a budget in a certain status from additional changes.  In addition to, it can be setup for users to enter a reason codes for changes, approval, withdrawal and rejection of budget data.

4. Integration Capabilities

•  FRx DirectLink provides integration between Microsoft FRx and Microsoft Forecaster.  Allowing quick access to real-time actual and budget data.  
•  ExpressLink provides integration from Microsoft FRx.  Historical financial balances or actual data can be loaded into Microsoft Forecaster using a wizard saving time and reducing data keying errors.

5. Import and Copy/Paste functionality

•  Allows for importing into Microsoft Forecaster 7.0 from excel spreadsheets saved as text files.
   If you have data in spreadsheets you can also copy then paste into Microsoft Forecaster 7.0 
•  Copy plan wizard allows budgets to be copied and saved as new budgets. Read the rest of this entry »

We have had a lot of feedback from customers on the Excel reports available for Dynamics GP 10.0. The most common question is how much do the reports cost and what does it cost to implement them? The good news here is that if you are licensed and currently running GP 10.0, you already own them and it is a simple installation. I thought I would provide a step by step of installing the reports, so here you go. Read the rest of this entry »

While setting up Analysis Cubes on one of our new internal environments, one of our consultants came across an error we had never seen before.

This particular install was being completed for a 64 bit machine  containing Windows Server 2008 and SQL Server 2008, so the consultant had to use a 32 bit install workstation.  When entering the server name for the data warehouse and tabbing off of the field, he received the following “SQL 2008 Server connection failed. Please enter a valid SQL 2008 Server name” error:

First, we confirmed that SQL Server 2008 was, in fact, installed on the target machine and that the correct Analysis Cube install package was being used (note: Service Pack 3 must be used to install Analysis Cubes with SQL Server 2008). 

In the end, we discovered that SQL Server 2005 Tools were still installed on the 32 bit install workstation from a previous install of SQL Server 2005.  Once we installed SQL Server 2008 on the same machine, it created a conflict for the install process.  This appeared to be the hang-up, as once we tried installing from another 32 bit machine on which SQL Server 2005 had never been installed, the installation worked properly.

I’ve been wanting to spend more time with Microsoft Office Sharepoint Server 2007.  I’ve found that to be difficult because virtual pc images don’t provide the spontaneity to try a quick feature (plus they can be slow) and MOSS 2007’s hefty requirements make it difficult to install on just any old machine.  I really wanted MOSS on Vista to let quickly try a few things like Excel Services. Well, if you are considering MOSS 2007 and just want to try it out there is a better option.

Bamboo Nation provides a free piece of software that allow MOSS 2007 or Windows Sharepoint Services to run on a Vista Machine. (Not XP so here is another good reason to upgrade.)  MOSS 2007 is available as a trial too so you can play for 3 months for free. Bamboo Nation’s install runs easily and all you have to deal with is the MOSS install.

Everything I have tried so far runs fine with one caveat. Business Portal for GP will not install because the installer detects that Windows server is not running. I haven’t found a way around that so you can’t use this technique to try out BP. Also, don’t even think about running this in a production environment. I know, if you can figure out how to install and run MOSS, you can figure out not to run it on Vista in a production environment but I had to say it.

Our packaged service offering for Analysis Cubes continues to be well-received by those who choose to take advantage of the dynamic reporting capabilities offered by the product.  One of the surprising consequences of installing Analysis Cubes for many of our clients has been to provide them multiple opportunities to validate data that currently exists in their accounting system.  The ease with which Analysis Cubes provides drill through capability from summary-level to detail-level enables Controllers and other employees to target and identify areas in which data might have been miskeyed or never entered at all.  By identifying these gaps and correcting them in GP, we are finding that users are gaining value from Analysis Cubes far beyond its immediate objective to provide dynamic reporting.  In assisting our customers with this data validation process, we have uncovered a few issues with Analysis Cubes that we thought we’d share:

Outstanding Payables Amount is incorrect for Payables Transactions with multiple Purchase Lines:
One of the biggest issues we have identified occurs in the event that a Payables transaction has more than one Purchase distribution.  If a transaction does have multiple Purchase distribution lines, the full Outstanding Amount for a Payables document is displayed in the Cube once for each separate line.  If you have two such lines, the Outstanding Amount displayed in the Cube is double.  To see for yourself, create a transaction in GP containing two Purchase distributions and the corresponding Payables distribution.  Do not enter a corresponding payment.

Payables Transaction Entry Window

Payables Transaction Entry Distribution

Since no payment has been made on this transaction, one expects the Outstanding Amount for the Vendor on this date (assuming there are no other transactions that fit this criteria) will be the full amount of the document.

When you report on the Outstanding Amount for the Vendor in an Excel spreadsheet using Analysis Cubes, the Outstanding Amount will equal twice the expected amount.  If you check the drill through (double-click the value cell) for this Outstanding Amount, you’ll see one line for each purchase distribution with the full remaining Outstanding Amount assigned to each line.

Payables Cube Report

Payables Cube Drillthrough

We have notified Microsoft of this issue and have learned that they are currently seeking a workaround for this issue.  Even if no workaround is found, we do believe this is an issue that will be corrected in the next version of Analysis Cubes with GP 11.0.

Current Inventory Value Measure Ignores Negative Qty on Hand:
One of our clients pointed out a potential issue with Analysis Cubes unable to record an current inventory value for an item with a negative quantity on hand.  To prove her point, she generated a stock status report by site in GP and replicated the same in Analysis Cubes by using ‘Items by Location’ and the ‘Qty On Hand – Inventory on Hand’ and ‘Current Inventory Value – Inventory on Hand’ measures.  In every instance where a Qty on Hand was positive, the Inventory Value on the stock status report matched that in GP.  In every instance where the Qty on Hand showed a negative stock, however, the stock status report showed the correct negative inventory value whereas the cube showed a zero.  Because of this, the total inventory value of the stock status report from GP did not match that shown from the cube in Excel.

Negative Inventory Stock Status

Negative Inventory Cube Report

We’ve also identified this issue with Microsoft and are working with them for a resolution to this issue.  We’ll be sure to use this blog to provide an update!

The last two issues have been encountered in both GP 9.0 and GP 10.0.

Resolution to an Earlier Issue:

We are happy to report a resolution to an issue with package and encryption levels we encountered with GP 9.0.  This issue was reported on this blog in the September 29 posting.  In that post, we reported that we were running into issues with Analysis Cubes requiring the install user to remain logged into the SQL Server console in order for a scheduled Analysis Cube job to run to completion.  This created a number of issues, not the least of which involved dedicating a SQL Server license for that user to remain logged in at all times so the job could run as scheduled.  Our ability to correct this issue was primarily limited since the SSIS packages are password protected.  After multiple back and forth discussions with Microsoft, they have finally determined a workaround that we have used successfully.  We’ve been told a KB article detailing this resolution will be posted shortly, and we’ll be sure to backtrack to this blog entry and post a link to that KB when it does become available on PartnerSource. 

Update: As promised, Microsoft has released a KnowledgeBase article describing this issue and its resolution.  You can find it on KnowledgeBase as article #967078.

Our thanks go out to those at Microsoft Support who have helped us resolve this issue!

Analysis Cube Compatibility with SQL Server 2008:

Finally, we’ve seen a few posts in the Dynamics GP forums questioning Analysis Cube compatibility with SQL Server 2008.  We queried Microsoft on this and learned that there is no compatibility between GP 9.0 Analysis Cubes and SQL Server 2008.  Compatibility between GP 10.0 Analysis Cubes and SQL Server 2008 is possible, but it requires use of Analysis Cubes Service Pack 3 (SP3) which can be found on PartnerSource.

As always, we’re interested in hearing about your experiences with Analysis Cubes and/or your comments on this post!

On Friday, January 23, Microsoft announced significant changes to its business intelligence strategy.  You can find the detail behind this at http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx as well as a number of great blog entries.

To help our customers and partners, I want to summarize those changes then talk about how they will impact you.

Here are the announced changes:

1. Performance Point Monitoring and Analytics will be downgraded into MOSS Enterprise CALS:  This is the part of PPS that handled all dashboarding and KPI’s.  It is the very visual, very sexy part of the product that really helped it sell and also the part that worked the best.  If you own MOSS Enterprise, you now get M&A for free.
2. Performance Point SP3 Release in July/Planning Discontinued:  Planning is the portion of PPS that is the hardest to implement and was intended to replace Forecaster and Enterprise Reporting.  MS will release a performance update service pack (SP3) and then will stop development of new features.  As of July, when SP3 releases, PPS will cease to exist as a stand alone product.
3. Management Reporter To Be Managed by Dynamics:  Management reporter, the FRx replacement reporting tool in PPS, will be given to the Dynamics team to own and enhance.
4. Enterprise Reporting is Back on Support: All previous announcement of Enterprise Reporting’s support discontinuation (planned for Jan 2010) are revoked and its back on the price list.
5. Proclarity Desktop:  Gone.  Not much more comment other than that.

Why did they decide to do this?  Answering solely for myself:

1. The planning module was definitely a v1 release and required significant technical and programming expertise to implement.  However, the Dynamics channel already had good forecasting tools (Forecaster, ER) and wasn’t willing/able to adopt PPS quickly solely for that.  The channel that was adopting it (infrastructure and MOSS partners) weren’t very adept at financial planning and forecasting, so an automatic channel disconnect was setting in.
2. PPS was expensive.  In this environment, it wasn’t going to get good play.  In addition, customers with Enterprise Agreements were feeling "nickled and dimed" over the additional cost in the EA for PPS.
3. MS truly believes in "Democratizing BI", especially in the role Excel and SQL play in this space.  By pushing core BI functionality back into MOSS, Excel and SQL, they allow the widest possible audience to buy and use these features.
4. Lastly, I truly believe someone finally said "Hey, don’t you think the Dynamics team should be working on the finance stuff?".

So, what does this mean to the MS customer?

1. If you own PPS Planning, stick with it, but start looking for a transition in 2012 or thereabouts.
2. If you own PPS  and only use it for M&A, renegotiate your EA when it comes up to lower the cost of it by the PPS piece that got added.  Other than that, you are in the catbird seat.  Good job!
3. If you were looking at PPS for planning, kill the search and talk to your reseller about alternatives.
4. If you were looking at PPS for M&A, continue.  Having it in MOSS Enterprise is great news!
5. If you were looking at Forecaster, FRx, Enterprise Reporting, stop being fearful of the decision.  The products are great and will be supported into the foreseeable future.

As to Management Reporter, we don’t know what’s going to happen to that.  It was billed as the next-gen FRx, but its feature set is, IMHO, pretty crappy still.  I think it will be enhanced significantly by the Dynamics team and become a truly awesome product, but don’t look for short term results.

MS promises it will deliver more roadmap information at 2009 Convergence in New Orleans.  Will have our usual large team there, so we’ll update you after the conference.

We found this nice Sharepoint Server 2007 products comparison download and thought that we would pass along as more and more folks are asking about sharepoint as a part of their Dynamics GP infrastructure.