Topic: ‘Reporting’

Dynamics GP 2010 “DIY” Fixed Assets Excel Report…

Around my casa I like to think of myself as a regular “DIY” (Do It Yourself) type person.  A little duct tape, krazy glue and some power tools will get a girl a long way in fixing things.  Don’t laugh, Yes, I really do fix things with krazy glue.

So I thought how about let’s use some of that DIY engineering to get an excel report for Fixed Assets.  So I came up with a “DIY” solution.  Don’t worry this solution does not require any duct tape, krazy glue or power tools.  You’ll only need Dynamics GP Excel Report Builder and Microsoft Office Excel 2007 or 2010 to create a real time Fixed Assets report.  The content is a bit long for a blog post so you can down load the step by step instructions by clicking the following LINK TO EXCEL REPORT BUILDER – FIXED ASSETS.

In the end you will have a report that looks similar to the following…

And there it is….You go to print a report from Dynamics GP and you get an “Unable to Start Printing” Error  when using a PDF product.

You check the print drivers on the Server, User’s Machine and even the Print Server if you are using one.  They are all the same.  You find that you can print from inside the terminal server but not from the user’s session.

Before going into a lot of trouble shooting, reinstalling print drivers, checking security and rebuilding report dictionaries there is a simple trick that might work for you. 

Read the rest of this entry »

Apparently, every 4 years I get asked this FRx question, “How do you create vertical lines on an FRx report?”  So I am blogging a reminder to myself so that in 2014 I can Google search it and read my own notes.    Well, by then we’ll all be on Management Reporter…Right?  And after my colleagues read this, I better not receive any text message with the subject line “Rookie.”  Ah, the price to pay for working with highly seasoned extremely intelligent consultants.

So how do you create lines that divide columns in FRx? 

Step 1:  Open up the “Columns” format you want lines on.

Step 2:  Highlight the Column you want the line to come before then right click and insert a new column.

Step 3:  In the Type row select or type in “FILL”

Step 4:  Column Width  = 1

Step 5:  While in the FILL cell create or select a new Format with shading.  For each column you want the vertical line click on FILL cell and select the “Font Style”

Step 6:  Save the column format and generate the report.

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:

Discover how to build reports using Excel Reporting in Microsoft Dynamics® GP. In this webinar, learn how to build links, add restrictions and otherwise fine-tune your reports using this powerful yet underutilized tool.

Find out how to deploy the 200 Excel reports for Dynamics GP and how to create a custom Excel spreadsheet with direct data connections to Dynamics GP.

Title: Microsoft Dynamics® GP Excel Reporting

Speaker: Mark Polino, I.B.I.S. Senior Consultant and Microsoft MVP

Make the most of your reports with this SQL Reporting Services Advanced Report Design Webinar by I.B.I.S.

Walk through retrieving and displaying data using stored procedures, how to use a multi-value dropdown parameter box and transforming multi-value parameter input into a temporary table for Dynamics GP reporting via SSRS.

Title: Advanced SQL Queries for SSRS – Microsoft Dynamics® GP

Speaker: Gerhard Venter, I.B.I.S. Senior Consultant

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

What is BI? Which BI tools and products would benefit my business? What kind of investment in money and time will we have to make?

Discover how to unleash your true potential in this I.B.I.S. Microsoft Business Intelligence Overview Webinar. Walk through creating Executive Dashboards using the tools you already have, like Excel 2007 Analysis Cubes.

Title: Microsoft Business Intelligence Overview

Speaker: Dwight Specht, I.B.I.S. Chief Operating Officer

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