Topic: ‘Reporting’

While installing Reporting Central’s product, The Closer, we ran into an interesting problem.

After a seemingly normal install, the product started up and prompted us for the login credentials to be used to access the SQL server.  This is completely normal.  We entered the credentials, tested them successfully and clicked Accept.  The system paused for 15 seconds then returned the below error:

Since we were using the sa login and were confident the server name was correct, we knew that the server side of this was fine.  After a couple of quick emails to Shane Hall at Reporting Central, we found the problem:

• In the program files directory, an XML config file is found.  The login information you enter is in this file.
• When I tried to edit the file, I got a file permission error when saving.  The file wasn’t read only – this was a windows security issue that was not letting me edit or write a file.
• The machine was Windows 7.  The user was a local Admin, but UAC was activated.  By default, this denied write and modify permissions to the Program Files directory in which the Closer was installed. 
• So, Closer would collect the data for the login, but be unable to write to the config file.  When we started Closer and entered the login info, it assumed it stored it and, when the app went to use it and it wasn’t actually there, it threw the above error.

We were able to easily solve the problem by right clicking the executable and choosing “Run as Administrator”.  Everything worked just fine at that point.

As an aside, I love this product.  If you don’t own it, buy it.  Especially if you use Purchase Orders and have to deal with reconciling Accrued Purchases.

Once again it is time for a quick “Snack” from Microsoft.  What’s New in Advanced Distribution

CLICK HERE to view webinar.  After viewing this webinar you should be able to:

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.