Posts Tagged ‘Excel’

Sorting in Smartlist the Smart way

312339_117542088417759_1094173370_nLast year I worked closely with customers that performed data dumps into excel from Dynamics GP.

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

image

Step 4.- In the Search Account Transactions window click on the Order By button

image

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.

image

 

And here is the result, so no more single column sorting!!!

Step 6.- Click OK twice and see your results

image

 

Here is another sample where I am sorting by Journal entry then Highest Debit Amount to lowest and lowest Credit Amount.

image

 

 

Until my next post!!!

Francisco G. Hillyer

Why my GP is too slow?

Hello all, I would like to provide you with some information on what to look when 312339_117542088417759_1094173370_nDynamics 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

Excel–Changing Values and reversing sign

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 theMe 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.

So here is how I helped them out. The following is a made up sample of the budget sheetEX1

I take an empty cell, and type –1, then I select that cell and copy the value to clipboard or I press Ctrl-C

EX2

After I have the value copied I select the range of values to change. Notice that the copied cell is still marked.

EX3

Then I right click on the selection and choose Paste Special

EX4

The paste special window has a section called operations, this section is able to Add, Subtract, Multiply and Divide, so I select Multiply

EX5

And after selecting the operation I click ok and my values are updated with the reversed sign.

EX6

 

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

Dynamics GP Mashup – Easy to Make Dashboard

I was experimenting the other day to get Management Reporter data and other Dynamics GP data together in one easy to make dashboard. It turned out to be easier than I had thought it might be.

Early in the process I came across some antiquities from Jan Harrigan that pointed me in the right direction regarding Management Reporter. It was easy to adapt her ideas about FRx, to MR:

Executive Reporting: Using Excel Charts with FRx
Graphing with FRx

I easily modified one of my detailed MR reports to just produce the summary information I wanted for my dashboard. And then I print the report and export to Excel. Use the same file name every time. The result is the graph that shows in the upper left part of the dash board.

Next I just added the other graphs I wanted, using refreshable Excel reports from Dynamics GP. I’ve shown this a few times here:

Simple Dashboards Using GL Summaries and Excel Reports for Dynamics GP
Creating a Simple Dashboard Using Excel Reports

Here’s the result:

This is a video showing how to get the Management Reporter information and make it available for use in Excel: http://youtu.be/lBZzo2lnsD0

Export to excel find

Patrick over at Developing for Dynamics has a great solution for those of you using Dynamics GP 10 and beyond.

I tested this out on my stinky old PC. I downloaded 20,000 rows in about 50 seconds after making the dex.ini switch. Without the switch I quite after a 1 min and 15 seconds and had only downloaded 2000 rows. Extrapolated that would have taken 12.5 mins to do 20,000.

This has been a limiting aspect of smartlist in the past so was pretty syched to see this solution.

What a great find.

Budget Import & Export

Dynamics GP has a very easy to use budget import and export function that allows you to create budgets in Excel and update budgets in GP, based on amounts you have in Excel.

All this functionality is available from the Budget Selection screen.  Navigate to:  Cards  >>  Financial  >>  Budgets

BudgetSelection

I created this short video to show this functionality in action.

One very important caveat:  Make sure that you validate the information in GP after importing or updating budget information from Excel.  There is no error reporting on the import function if the import does not properly import the data.

I usually create a quick FRx report for this purpose.  But you could also simply export the information from GP, to a new spreadsheet (make sure not to overwrite your original spreadsheet).

Simple Formula to Extract Specific Data from a List in Excel

I recently discovered the “SUMIFS” formula in Excel that I think you’ll like because it makes it easy to select specific data from a data list in Excel.  Excel Reports for GP is a great way to extract data from Dynamics GP and report on it.  Because the resulting Excel spreadsheet includes a data connection to the GP database, you can format the spreadsheet and refresh the data as you wish.

Typically I have used the pivot chart, and graph functionality in Excel to create the reports I want.  But the “SUMIFS” formula allows me to have more control over the reported results and format.  This is how I generally use the formula:

Define range names for the data you want to report on.  In Excel: 

  • Select the column with the data you wish to name
  • Formulas  >>  Define Names

Excel1

Enter the formula to select the data you want:

  • Select the range that includes the data you want (e.g. Extended_Price)
  • Select the range that includes the values you will select
  • Select the specific value that corresponds to the data you want to display

Excel2

The result:

Excel3

See the formula in the formula bar on top.  In this case I used a relational reference to the criterion I used, B17.

Try it out.  It will give you a lot of control over the data you can select and how it is formatted.

SmartList Export Solutions in Dynamics GP

If you’re using SmartLists on a regular basis to report on information in Dynamics GP, you’ll want to learn how to use the Export Solutions functionality within SmartLists to add charts, pivot tables, calculated fields, and additional formatting to your data.

Export Solutions allows you to run an Excel spreadsheet macro on the data that SmartLists exports to Excel.

These are the basic steps to set up the macro:

  • Export a SmartList to Excel
  • Start and name a new macro in Excel
  • Perform the formatting you want in the Excel spreadsheet
  • Stop the macro
  • Delete any worksheets you may have added during formatting
  • Delete the data that was initially exported to Excel
  • Save the Excel spreadsheet as a template

These are the steps to attach the spreadsheet with macro to SmartLists:

  • Navigate from SmartList:  SmartList  >>  Export Solutions
  • Document: Select the Excel spreadsheet that contains the macro you wish to execute
  • Works for Favorites: Select the SmartList(s) you want the Export Solution to be available with.

SmartListExportSolution

Once the Export Solution has been attached to the SmartList(s), you can use it when you wish.  Just make sure to select it from the menu drop down, and not “Quick Export”:

DropDown

I’ve created a companion video to this on YouTube.

Publishing Refreshable Excel Reports with Dynamics GP

http://youtube.com/v/dgssNqKJ4w4

This video shows you how to generate refreshable Excel reports from Dynamics GP, and then publish them to SharePoint and make them accessible in Outlook. Refreshable Excel reports allows you to format an Excel spreadsheet as you wish, and then at anytime refresh the data in the spreadsheet from the GP database.

Creating a Simple Dashboard Using Excel Reports

Excel is increasingly being used as a reporting tool. I recently created a simple dashboard for a client using the refreshable Excel Reports that come with Dynamics GP version 10.0:

I started by using three of the Excel Reports to pull the data from Dynamics GP. I used the Customers Past Due, Customer Balance, and Vendor Balance reports to get the data. I have the results from all three reports on one spreadsheet. This allows me to refresh the data in one place.

I created a video to show you how I did it. Check it out.

Subscription Options:
Subscribe via RSS
Articles Categories