There are the easy exports where they just publish the report in text mode and then open the same in excel and I’ve seen customers that love formatting data in excel, spending countless hours formatting and at the end they have to do it again because data was refreshed.
There are others that are more complicated but yield better results, like Excel Reports, SSRS or plain T-SQL extracts and just very few who take advantage of the Export Solutions but that will be a subject of a future post so stay tuned.
Smartlist is a very useful tool within GP but some customers find it confusing because of the filters and how data is stored in GP.
One of the questions that made me write this post for you is, How can I sort data in Smartlist using more than one field, so I don’t have to do this in Excel?
The solution is simple but believe it or not, I learned that not many people know about this, I queried my own colleagues and I was shocked.
Here is my revelation for you, hope I can save you some time …
Use the Order By feature in Smartlist. Let’s go thru a sample:
Step 1.- Open Smartlist
Step 2.- Open Financial > Account Transactions
Step 3.- Double click on the * named smartlist
Step 4.- In the Search Account Transactions window click on the Order By button
Step 5.- Add the sort rules/levels from the list on the left, by using the Insert button
in my sample I am sorting by Account, then by the amounts, but I want all debits from highest amount to the lowest, then I want all Credit postings same rule and lastly I want to sort by the Series.
And here is the result, so no more single column sorting!!!
Step 6.- Click OK twice and see your results
Here is another sample where I am sorting by Journal entry then Highest Debit Amount to lowest and lowest Credit Amount.
Until my next post!!!
Francisco G. Hillyer
Hello all, I would like to provide you with some information on what to look when Dynamics GP seems to be working slow. As you may already know Dynamics GP is a process driven application and you may experience slow performance when specific processes are performed in GP please take a look:
· While posting might be due to the PJOURNAL table as you know checks post too or remittance is being printed separately
· Client workstations should have a default printer setup and online, remove invalid printers
· While opening windows, the autocomplete feature may cause performance issues and if not used can be turned off
· While login into Dynamics GP or utilizing 3rd party dictionaries as well if Menu Master table (SY07110) became too large.
· The location of the modified dictionaries other than local workstation
· Certain Smartlist reminders might interfere with login into Dynamics GP
· You may have shortcuts to network locations that are no longer mapped or available
· Printing to file directly into the client/remote computer instead of the hosted server user folders
· OLE Notes path in Dex.ini
· SQL AutoClose and AutoShrink options not set to false
· Virus scanner setup not excluding the following extensions (CNK, DIC, CHM, SET, INI, DAT, IDX, VBA, LOG, LDF, MDF)
· The Dynamics GP homepage smartlist favorites
· The Dynamics GP homepage outlook integration
· Enabling tracing options in DEX.ini
· Bad user defined triggers in SQL
· Bad configuration of SQL server memory allocation
· SQL server or Dynamics GP server available disk space
· SQL server log file is full and is not set to Autogrow
· TNT*.* files, your %TEMP% folder has not been cleaned
· Non-compliant SQL server/GP Server/Client hardware
· Different DB owner than DYNSA
· Little or no SQL server maintenance (Table Fragmentation)
· You might be missing table indexes or statistics
· When exporting a budget, thru the budget wizard it seems locked (if you are using the excel wizard to export, make sure the “save as” dialog is not on the background, Alt-Tab to it as it must have been opened and its behind your main GP windows.
· Too much history (You can archive historical years, specially if you have large tables like Item Master, Customers, Vendors) Believe me I ran reconcile one time and it took 6.5 days on a company with more than half a million SKUs and 5 years of sales.
I have witnessed how few administrators that in order to preserve enough disk space they have a tendency of running SHRINK on the SQL server, this obviously will fragment tables affecting performance. I have a post that covers that here.
If you want us to take a look at your environment don’t forget to contact us, and as always when troubleshooting record answers for the following questions:
1.- Can you replicate the issue? write down steps that let you reproduce the issue.
2.- If its related to posting, please note the module( s ), how many transactions are in the batch, how long does the process last? how long did the process last before?
3.- On a Server/Client install, can you replicate on the server?
4.- Can you reproduce on all or other clients?
5.- Are there any 3rd party products running on the same SQL/GP server or together with Dynamics GP?
6.- Are there any customizations in GP?
Until my next post and let us know if we can help!!
Francisco G. Hillyer
Hello all, here is something I was asked recently:
“I have processed my 2012 payrolls for the year, my IT is always on the latest and greatest regarding service packs, hotfixes, updates etc, and they installed the 2013 payroll tax tables update, and I have not executed the Payroll Year End, can I close it anyway?”
Short answer: Yes you can close it, but it will be incorrect.
Please if this happens to you, make sure to restore the Dynamics DB prior to the install of the payroll tax table update because I am sure you always do a backup of your databases prior to any updates installed right?
Leslie Vail has a post from 2011 where she explains how FICA/S wages and withholding is calculated Leslie’s Post
NOTE FOR LESLIE (Step 5b says “he year-end” instead of “the year-end”)
But I wanted to provide a graphical representation of it
Until my next post…
Francisco G. Hillyer
So just like every year we go thru similar steps, I am providing some help along the way, many of the processes described here belong to a published KB from Microsoft as well links to posts from other resources but I wanted you to have them all in one place.
Please note that some of the links require you to have access to Customersource/Partnersource.
Difference between Year End Update and Payroll Tax Table Updates
The Year End Update download contains software changes to allow you to comply with 2012 filing requirements as well as the most recent fixes for your Dynamics application.
The 2013 Payroll Tax Table Updates contains updated rates and tax changes as well it might contain FICA/Medicare changes to be applied when processing 2013 payrolls.
Here is the link for the 2012 US Payroll Year End Update: Click Here
Here is the link for the US Payroll Tax Updates: Click Here
It is important to mention that you can install the Year end update prior to your last payroll for 2012, however do not install the Tax table updates before your last 2012 payroll as you might end up with a wrong calculation.
Always backup your data, your .DIC files as well.
Setting up / Adjusting Fiscal Periods in GL
Microsoft provides a KB article number 871679 KB871679
Closing Dynamics GP GL
STEP 1: CLOSE ALL OTHER MODULES
Complete the posting/closing procedures for the modules in the suggested order prior to closing GL, please note that if you are not using a particular module, just skip it:
Follow the instructions provided in each KB
Inventory: KB 872713
Receivables Management: KB 857444
Payables Management: KB 875169
Fixed Asset Management: KB 865653
For Microsoft Dynamics GP 10.0 Service Pack 2 and greater, functionality was added to consolidate balances for dimensions in Analytical Accounting. Please review KB 960356 to make sure you have properly marked the dimensions that you want to be consolidated during the year-end process. Please note that there is no separate year-end process that needs to be run in the Analytical Accounting module. When the year-end close process is run for General Ledger, it will automatically consolidate the balances and move the transactions in Analytical Accounting for dimensions that were properly marked.
PAYROLL year end procedures are independent of the procedures in other modules and are usually performed at the end of the calendar year. Please see this KB 850663
STEP 2: POST FINAL ADJUSTING ENTRIES
Adjusting entries are considered most of the time as entries that allow you to correct errors that were made when transactions were recorded as well they might be utilized to assign revenues or expenses to period or periods in which revenues were earned or expenses incurred.
If you need to in setup or adjust your fiscal periods in GP, please review the section “Setting up / Adjusting Fiscal Periods in GL” at the beginning of this article.
STEP 3: VERIFY ACCOUNTS POSTING TYPE
The posting type helps Dynamics GP determine whether an account will be closed to the retained earnings account or if the account balance will be brought forward to the next fiscal year.
Follow these steps to print an account list:
- On the Reports menu, point to Financial, and then click Account.
- In the Reports list, select All Accounts, and then click New.
- In the Option box, type all accounts.
- Click to select the Inactive Accounts check box. (if you want to delete inactive accounts)
- Click Destination to specify a report destination, and then click OK.
- Click Print.
STEP 4: CLOSE FISCAL PERIODS FOR 2012 (OPTIONAL)
Use the fiscal periods setup window to close all fiscal periods open for the 2012 year. This will prevent transactions from posting to the wrong period or the wrong year.
NOTE: If you still use Microsoft FRx, keep one period in the most recent historical year open to prevent the error: “FRX Print Engine Failed to Load the Company Calendar” see this KB 874932
STEP 5: PERFORM MAINTENANCE ON FINANCIAL SERIES (OPTIONAL)
Run the check links procedure on the financial series group of modules.
Make sure that you always have a backup and that you can confirm you can restore from it.
STEP 6: VERIFY SETTINGS IN GL SETUP WINDOW
If you are like me and always want to keep historical records, you must enable the checkbox next to Accounts and Transactions in the Maintain History area of the General Ledger Setup Window.
NOTE: If for some reason you have the checkbox enabled for “Close to Divisional Segments” and you are no longer closing to Divisional Segments or by mistake someone else enabled it and not using it pay attention:
I have discovered in the past a BUG in dynamics GP that is still there as I reported in this blog in August 14, 2012, if you would like to read more about this important step please click here: YEAR END CLOSE BUG 64711
STEP 7: MAKE ANOTHER BACKUP (OR BACKUP IF YOU HAVE NOT)
Make sure all users are out of the system
Remove stranded user sessions, you can follow this post Removing Stranded Sessions
Backup DYNAMICS database
Backup all company databases if you are unsure which databases to backup you can query the company master table (SY01500) and retrieve the names, the following script can help you determine that information.
select INTERID, CMPNYNAM from dynamics..sy01500
Make sure to backup as well the Dynamics GP code folder
STEP 8: PRINT A TRIAL BALANCE REPORT (OPTIONAL)
Use the Trial Balance report window to print a year end detailed trial balance report. Even when this step is optional it is highly recommended to be followed.
STEP 9: PRINT YEAR END FINANCIAL STATEMENTS (MR, FRx, Other)
Print any year end financial statements that are required. The most common are:
- Balance Sheet
- Profit and Loss Statement
- Statement of Cash Flows
- Statement of Retained Earnings
STEP 10: SETUP A NEW FISCAL YEAR
Before you can perform the year end closing routine, you must setup a new fiscal year, please follow the instructions on the section at the top of this article to Setup or Adjust Fiscal Periods.
STEP 11: CLOSE THE YEAR
- Click on the Microsoft Dynamics GP Menu > Tools > Routines > Financial > Year End Closing.
- If its not specified, you can key in the Retained Earnings Account.
- Optional: Specify the Starting Journal Entry
- Click on Close Year
STEP 12: BACKUP YOUR SYSTEM
Make another backup of the Dynamics DB and all the company Databases.
Thank you for reading I would like to mention that this post enhances Microsoft KB 888003
Until my next post
Francisco G. Hillyer
This time I would like to share with you something that I recently learned “the hard way” obviously on a support case.
First of all, I want to express the importance of validating your information and that the engineers/partners that are/were involved in your company setup of Dynamics GP were bound to the best practices established by Microsoft and supported by many of my colleagues.
In my case, a customer did a side by side upgrade of SQL, with this came the issue of not having the DYNSA login in the new SQL instance, we followed certain processes to make sure DYNSA was the owner of the Databases Dynamics GP is using.
But you may ask who or what is DYNSA? my friend Mariano Gomez has a post very complete about this subject and you can find it here: Mariano’s DYNSA Info since I am not reinventing the wheel take a look at Mariano’s blog its packed with information for all audiences (GP related !!).
So when I was working on this customer DYNSA setup, I suddenly remembered another case where I was having issues with a third party, I jumped into their environment (literally) and started investigating this DB configuration, to my surprise the owner of the databases was an AD account not DYNSA. I proceeded to replace the owner and then certain SQL reports started working and producing results. I am still intrigued on why, but I will do a full research on spare time.
So back on the game for this customer issue I was having while trying to archive data, just imagine a SOP30300 table with 11 million records and a huge base of customers.
The queries running were taking countless hours to execute not even mentioning the impact on the processor. memory and of course user experience.
I learned that the customer had a “Maintenance Plan” where they executed the Shrink process on SQL, as you may know I am a SQL enthusiast and I recalled an important blog post from another noted resource Mr. Pinal Dave aka “The SQL Authority” here is his post about why is BAD to shrink a DB Shrink is Bad for you… there is one section in the article that explains that Shrinking a DB to obtain disk space will actually fragment your tables, obviously to reduce fragmentation you rebuild indexes. So this maintenance plan was being executed to reduce disk space, then to improve performance, and the disk space was gone again. Wise words from a mentor that prefers to be in the shadows once said “with current prices on storage why waste time shrinking when you can focus on performance”.
I ended up tweaking some SQL scripts to automate a SQL job on finding fragmented tables in the DB and executing that as part of DB maintenance, as I said “go buy another disk drive and add it to your server, move the logs to this new disk and keep data apart from the logs and you will be better than now”.
I hope my experience helps you for a better community.
Until my next post
Francisco G. Hillyer
I know people that use Smartlist extensively, they really use it but the pain they have is sorting, I have witnessed many exports to Excel and even some people were some kind of creative that they made their own “Export Solution” to do what? you may ask, and yes you have the answer, to SORT by other columns.
So when I show them this trick they just think that I’ve been hanging around with MGomez, FHamelly, DPitcher, the famous SChapman, MPolino and many more just I wont finish the list.
Here it is… how about if I tell you I can show you how to sort Smartlist by using multiple fields, different sorts and even with fields not being displayed on the smartlist itself?
Lets open Smartlist….
Double click on one of your smartlist objects, I am choosing the “All” in Account Transactions.
Once the Search dialog appears click the button on the bottom called “Order By”
Then the Select Order By window appears, note on my example that I sorted my list by Account, then by Series (Descending), by Transaction Date and by the User Who Posted.
The User Who Posted is a field that is not being displayed in the screen, even in this example the user who posted is not doing us any good report wise as we cannot see the value, it’s a clear example that you can use a non displayed field to sort.
Once you have chosen the available columns and moved them to the Order by section you click OK to go back to smartlist search dialog, then OK again to have the search executed.
As you can see the result is ordered by the Account number, then by the series int value, then by date.
Don’t forget that once you define your sort, save your smartlist definition so you don’t have to re-work.
I hope I can save you sometime with Excel macros and ease your job directly from Dynamics GP Smartlist.
Until my next post!!
Francisco G. Hillyer
Good day to all, here is another post that comes from working with budgets and excel spreadsheets.
Recently I had a case where the customer was trying to import budgets into Dynamics GP. All spreadsheets seemed normal, they uploaded correctly and we were under the impression that everything was ok for all the years we imported.
But to our surprise the customer called in and said “It appears that the sign is reversed, we verified and we entered the budgets with the wrong sign instead of (52,250.00) should be 52,250.00 can you change it? And by the way we deleted the budgets can you import again?.
Ok so my coworker started adding columns to the left and multiplying the numbers by –1 to reverse the sign, I saw that this process was a little painful and slow considering there were many spreadsheets to work on.
I take an empty cell, and type –1, then I select that cell and copy the value to clipboard or I press Ctrl-C
After I have the value copied I select the range of values to change. Notice that the copied cell is still marked.
Then I right click on the selection and choose Paste Special
The paste special window has a section called operations, this section is able to Add, Subtract, Multiply and Divide, so I select Multiply
And after selecting the operation I click ok and my values are updated with the reversed sign.
Just imagine the potential and time saving opportunity here, if you want to subtract a fixed value to all columns or add a fixed value, this makes it relatively easy and fast.
Another example will be when someone asks you to take the numbers from last year and add 25% its so simple to just add a 1.25 to a cell, copy the value, select your range and select multiply. Try it with variations and you will experience this hidden gem from Excel.
I would like to point out that its not just for budgets but for many tasks in real life. Why I added to a Dynamics GP blog? because it originated from working with the budgets.
Have a nice day and until my next post.
Francisco G. Hillyer
As you may know my posts originate from being in the front lines working support and aiding fellow coworkers in need, my friend Chastidy presented me an issue with Integration Manager, the customer is trying to record data into the Internet Information window specifically to the “Additional Information” field. When they verified the field properties it says it holds up to 32,000 characters, if they copy and paste the contents being imported it saves just fine, but Integration Manager is truncating to 255 chars, but the field option is set to cancel the document, not to truncate at max.
First thing on my mind was “Challenge Accepted”, the solution its simple, I went to the data source properties and verified the columns available, I noticed that the field data type chosen was “String”, if I recall correctly from my IM self training class Strings are limited to 255 characters, and if you would like to import more than 255 characters the other data type that will allow this into a field like this one is “Long VarChar”, once I changed the data type in the source properties voila!!! information uploaded correctly.
Then the next thing on my mind was “Oh Yeah!!” and pictured me with dark sunglasses.
This small change has a huge advantage and saves a lot of headaches.
I hope this works for you and keep the comments flowing.
Until the next post!!
Francisco G. Hillyer
I had the opportunity to work on a case where the Year End Close report was being printed with the Debits not matching the Credits. No its not the JE created after a GL close, but the report that lists all P&L accounts and the retained earnings account.
Customer showed me how “before the upgrade” all YEC reports matched and “after the upgrade” they were not in balance.
I inherited this case from my friend Doug Pitcher and my friend Mike Feori.
The oddness of the case made it challenging to resolve, however I try as much as possible to adhere to the “Microsoft Dynamics 10 Rules” rule number 6 says: “Search for a workaround when final solution can’t be provided or is delayed”.
Here is the information I used to recreate the scenario and its improved with comments from Microsoft CTS.
The issue has been written up as Bug # 64711 “Divisional Account Segment information not updated in GL40000 table if you disable in the General Ledger Setup window”.
Here are the steps that can be taken to recreate the problem:
1. Go to the General Ledger Setup window (Financial Area Page under Setup).
2. Select the check box for ‘Close to Divisional Account Segments’ and select segment 1.
3. Verify that the DIVACTSG field has a value of 1 in the GL40000 table.
4. Go back to the General Ledger Setup window and uncheck the box for ‘Close to Divisional Account Segments’.
The DIVACTSG field would be set to a 0 in the GL40000 table.
The DIVACTSG field will still contain the segment selected when you originally did the setup.
Run an update statement to set the DIVACTSG field to 0.
Basically, if you select the check box to close to Divisional Account Segments in the General Ledger Setup window and you specify a segment the DIVACTSG field in the GL40000 table gets updated.
The issue is that when you unselect the option in the General Ledger Setup window, the DIVACTSG field does not get updated with a 0. It holds the value that was initially selected, the window greys out but the value is still a 1.
This is a problem when generating the Year End Closing report for companies that are not using Multicurrency. There are two different reports generated for the year end close-one if you are registered and using Multicurrency and the other is when you are not registered or using Multicurrency. The problem is that during the year end close process, the code breaks the profit and loss accounts into multiple retained earnings lines. Only the first RE line is written to the temp table because the rest are rejected due to key errors. The key on the table is composed of the ACTINDX, Ledger_ID and CURNCYID fields. The report pulls from this temp table and so it is only getting the first line that is inserted into the table. The tables are correct, but the report is wrong.
Remember I was able to reproduce all the steps many times while testing and trying to identify the issue because I had a pre close backup and a test environment to perform the close. Lucky me to always backup otherwise I would have been doomed by the following phrase “I have done millions of GL close procedures I don’t know what to do now….”
Have a great day!!!
Even customers are truly surprised when I show them how Integration Manager can pull information from one company or even better from another SQL server into Dynamics GP as a source.
Yes I know you are surprised too that I am telling you that your source can be a SQL Query Script…….
We have many ways of sorting data in integration manager, we even have filters and sort fields on the source definition, but nothing beats the customer frustration when they have a sorted file and Integration Manager scrambles the file, then open up the distributions for example on a JE import and see that the order is not even close to what the source file have.
One thing important is that there is a setting in the IntegrationManager.ini file. This setting is called UseOptimizedFilter normally I see this option with the value True.
When this option is enabled, Integration Manager uses a series of ninja techniques to optimize the data upload but that has an impact on the sort order.
If you want to have the distributions ordered or your transactions in the order you have them in your source file, close your Integration Manager, go to this file located on the Integration Manager code folder and set to false this setting.
Try your integration once again and voila!!!! Its just like magic, everything is the way you had it on your source file.
I am hoping to save techs from headaches here, and if this tip helps you, let us know, we want to know if our work can actually influence a better Dynamics community.