Topic: ‘Reporting’

We have SOOOOO many clients that get this error.  As best I can tell, it happens under two circumstances.

The first is when an un-posted transaction exists in the system on one night, then gets posted the next night and shows in both tables when you try to load the warehouse – that’s a theory, mind you, that I have yet to fully test.  However, in most circumstances, you can fix the problem with the below script:

Use DynamicsGPWarehouse

go

update
    LastUpdated
set
    [DateUpdated]=’01/01/1900′,LastRow=0,TempLastRow=0
where
    TableName = ‘TotalRevenue’
go

truncate table TotalRevenue

go

If you want to limit this to only a single company in the data warehouse, use the following:

Use DynamicsGPWarehouse

go

update
    LastUpdated
set
    [DateUpdated]=’01/01/1900′,LastRow=0,TempLastRow=0
where
    TableName = ‘TotalRevenue’ and CompanyID = ‘COMP’ –Replace COMP with your company ID
go

delete TotalRevenue where CompanyID = ‘COMP’ –Replace COMP with your company ID

go

However, there are some occasions when the above doesn’t work. That happens when you have a duplicate record in RM10101.  This table is the Work and Open Distribution table for the RM records and, if something goes wrong in posting, you may end up with a two sets of records for the same document – one posted and one unposted.  To diagnose, run this:

select RMDTYPAL, DOCNUMBR, SEQNUMBR from RM10101
group by RMDTYPAL, DOCNUMBR, SEQNUMBR
having count(*) > 1

The above will show you all documents where you have more than one distribution of the same type or status.  Take a look at the PSTGSTUS field – this is posting status.  Most likely, you’ll see identical records, one with a PSTGSTUS value of 1 (posted) and one with a value of 3 (unposted).  If so, you must solve the issue that caused this problem before reloading the warehouse.  Once you have solved it, use the first set of scripts to clean up TotalRevenue and then rerun the Agent job to load the warehouse.

Dwight

While snowed in at home today, I took a call from a client who was running into this little nugget of weirdness.

While updating the warehouse, the job failed.  Running the SSIS master package showed the error in detail: Arithmetic overflow converting IDENTITY to Type Int.  This occurred during the ItemTransactions component of the Item Daily Quantities update. 

My first reaction was to clear the ItemDailyQuantity table, reset LastUpdate and re-run the package.  So I did, but I cleared the table with a DELETE statement rather than a TRUNCATE statement (very important).  I got the same error when I re-ran the package.  After some digging on the web, I figured out the problem:

The ROWID of the ItemDailyQuantity table is an INT.  The client had a sufficiently large volume of transactions that the Identity value for that column exceeded the storage capacity of an INT (2,147,483,647).  If I had truncated the table, it would have reset the identity column value of the table back to 1, but, since I deleted it, it didn’t.  Truncating the table and rerunning the package made everything work okay.

If you want to avoid this issue, you could set the column ROWID to be a BIGINT or you can run the script located in this blog post (http://vyaskn.tripod.com/sql_server_check_identity_columns.htm) to figure out which tables are likely to cause the problem.

Good on ya,

Dwight

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