Why care about User Classes?

With recent version of Dynamics GP, the user security model shifted away from user classes to security roles, tasks, and operations.  So, I am often asked why worry about user classes now? 

Microsoft Dynamics GP-Tools-Setup-System-User Classes

Users are assigned to a class in Setup-System-Users

Well, user classes still have a number of benefits in Dynamics GP.  I thought I would take you through some of them.

1. You can assign shortcuts to an entire user class.  Simply log in to Dynamics GP as 'sa', and you will see on your navigation pane a folder for each user class.  Add a shortcut in to one of those folders, and it will appear on the navigation pane for all users that belong to the class.
2. If you are using Account Level Security/Organizational Structures, you can assign an entire class to a branch of the organization structure, Cards-System-Organizational Tree.  This is especially useful when users are added to Dynamics GP, you can place them in to the correct class and they will automatically receive the associated account level security rather than having to assign their user individually to the organizational structure.
3. If you use project accounting, you can restrict access to certain processes and activities in project accounting by user class.  Microsoft Dynamics GP-Tools-Setup-Project-User Classes.  You can control things like being able to change project status, change the billing type of a line item, or add a budget line item "on the fly" from a transaction.

Please feel free to share your own continued uses for User Classes.  These functions may not be important on all implementations, but in some cases User Classes can still be very helpful!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Management Reporter- Using Dimension Sets for Report Design Efficiency

Every calendar year end, I feel like all I can come up with are payroll-related blog posts.  So, I am trying tonight to come up with something, anything other than payroll topics.  Thinking through the projects on my plate lately, Management Reporter is a common theme.   When working on large report projects in Management Reporter, I always try to find ways to make the report design (and therefore ongoing maintenance) more efficient.  One of the simplest way to do this is through the use of Dimension Sets.

Back in the FRx, these were called Account Sets and allowed you to group accounts for use on multiple reports.  And in Management Reporter, the functionality is similar.  You can create dimension sets to bundle groups of dimensions (any particular segment of your GL) in to named sets to be referenced in row, column, or tree definitions.

The benefits of using dimension sets include:

• Reference the same dimension set on multiple row, column, and/or tree definitions
• Have clients configure dimension sets in advance of report design, to simplify the report building process
• Update a dimension set, and all reports that reference the set are automatically updated
• Add, subtract, include ranges in a dimension et
• Simplify the "look" of a row definition, in particular, to make it easier to spot what is included in a row
• Centralize maintenance of key dimension sets referenced on several reports, like Net Sales or Administrative Expenses

Within a row, column, and/or tree definition you can reference:

• Multiple dimension sets (for example, multiple Account dimension sets, or an Account dimension set and a Department dimension set)
• Combinations of dimension sets and other dimensions (for example an Account dimension set plus a department segment)

Of course, dimension sets do require you to think through the design of a report before you begin actually creating it.  And it is even more beneficial if you analyze all reports to be created, to find the common groupings that can leverage dimension sets.

Once you have identified the dimension sets, it is very easy to create them in Management Reporter.  With any building block (row, column, or tree) open, simply go to Edit>>Manage Dimension Sets.

First, select the Dimension for the set using the drop-down list, you can select any segment of your chart of accounts (for example, main account or department or division).  Then you can either select an existing Dimension Set and click Modify or click New to begin a new one.  Note that you can also Delete and Save As for existing dimension sets.

After you click New, the New dimension set window appears and you can enter a Set Name and Set Description.  Then you can specify the segment values and ranges that should be included in the Dimension Set.  Use the + and - signs to add or subtract additional segment values and ranges within the set.  Click OK to save the dimension set.

To use a dimension set, you need to access the Dimensions window from either a tree, row, or column building block.  For example..

• In a row definition, double-click in the Link to Financial Dimensions field
• In a column definition, double-click in the Column Restriction field
• In a tree definition, double-click in the Dimensions field

Once you are in the dimension window, double-click in the segment/dimension field you wish to populate and the corresponding segment/dimension window will open (in this case, MainAccount).  Now, you can select the Dimension Set radio button and use the ellipsis (three dots) button to select from a list of available Dimension Sets for the selected segment/dimension.  You can also use the Manage Dimension Sets button as a shortcut to edit and create new Dimension Sets. 

Click OK to save the entries you have made, and then you can repeat the process for additional dimensions in the same window, or move on to another row, column, or tree branch on your report.  Now, if you need to add an segment value to Net Sales or to the Domestic Divisions rollup, you can simply edit the Dimension Set and all reports that reference the Dimension Set will be updated!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Local Taxes Set Up As Deductions?

It is not unusual, in my experience, for some local taxes to be set up as deductions.  This most commonly occurs with an earnings tax or "head" tax that is based on an income threshold.  For example, Denver has a "head" tax that only applies an individual who earns more than $500 in a month.

As you may already know, GP doesn't have the capability out of the box to calculate a threshold other than per pay period, year to date, or life to date.  So, if we paid an employee weekly, or semimonthly but wanted a threshold to be based on a month...we would be out of luck.

For this reason, you might choose to set up your local tax as a deduction.  This would allow you to include/exclude the deduction in the build, or enter transactions for the head tax.  Of course, there are other ways to approach it including manually entering a local tax code when needed on the payroll transactions or creating a customization.

But for those of you that do use a deduction to track local taxes, it can present an issue at year end.  If you map the deduction (using the W2 Box and Label fields from the Deduction Setup and Employee Deduction Maintenance windows) to the actual local tax field on a W2, the Microsoft Dynamics GP year end closing process for payroll will ignore the deduction.  I assume this is because it knows that the local taxes set up in GP as local taxes (Payroll Local Tax Setup) belong in that field.

If we caught this before year end, we could simply code the deduction to a box and label that is not currently being used and then modify the W2 form in Report Writer so that the field prints in the local tax box instead.  But what happens if we don't catch it in time?

Fortunately, if you can produce a spreadsheet of the employee ID with the tax amount and taxable wage, you can insert it in to the UPR10106 table, which is the Payroll Year End Local Table. Admittedly, I do this sort of thing a little oddly, but here it goes...

I add a column to my excel spreadsheet that says "INSERT INTO UPR10106 (RPTNGYR, EMPLOYID, SEQNUMBR, LOCLCODE, LOCLWGES, LCLINTAX, TAXTYPE) VALUES (".

Then I create a concatenated formula in Excel that appears like this:
=CONCATENATE(F2,"2011,'", A2,"',",1,",'DENVER',",D2,",",C2,",1",")")
To break these components down:
-F2 is the field with the static text mentioned above
-2011 is the hard coded reporting year for the W2
-A2 is the field with the Employee ID
-1 is the hard coded sequence number (I will discuss this more below)
-DENVER is the hard coded W2 Box Label/Local Code to appear on W2
-D2 is the field with the local taxable wages
-C2 is the field with the local tax
-1 is the hard coded tax type of City Income Tax (based on drop down in W2 Local Tax window)

Of course, you could choose to source the hard coded items from columns as well if you want.  The end result of this is a series of INSERT scripts that can be run against the company database to update the UPR10106 by inserting records.

INSERT INTO UPR10106 (RPTNGYR, EMPLOYID, SEQNUMBR, LOCLCODE, LOCLWGES, LCLINTAX, TAXTYPE) VALUES (2011,'ACKE001',1,'DENVER',10000,50.00,1)

One thing to watch out for, though, is the sequence number.  For each employee, for each year, the sequence number is an incremental number.  So, for example, if an employee had two local taxes withheld, the first one would have a sequence number of 1 and the second would have a sequence number of 2.  So if you have more than once local tax, you need to adjust the script accordingly.  And if a local tax is already included in the UPR10106 table for the year you are updating, you would also need to take this in to consideration or you will get a primary key error when attempting to insert the records with a duplicate employee ID, year, and sequence number.

Good luck with year end, and always always always make a backup before attempting these or any other scripts :)


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Recapture Provision? What Recapture Provision?

We have had a few clients ask about the recapture provision of the payroll tax cut extension.  Essentially, the recapture provision says that if an employee makes more than a set amount in the first two months of this year, the excess has to be taxed at 6.2%.  What?  How does that work?

Well, fortunately Terry Heley at Microsoft wrote a fabulous blog post explaining this provision and why GP doesn't do it...

http://blogs.msdn.com/b/dynamicsgp/archive/2012/01/05/payroll-tax-cut-temporarily-extended-into-2012-translated-so-a-user-can-understand-and-how-it-affects-microsoft-dynamics-gp.aspx

Check it out!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

1099 Reporting, Or Not?

Recent versions of Dynamics GP have included terrific enhancements to 1099 tracking.  The ability to track 1099 amounts per type and box at a transaction level allows users to track in greater detail without having to assume that all of a vendor's payments go to the same 1099 type and box number.

Along with these enhancements, though, there are not a lot of great reporting capabilities to review this information by type and box.  Of course, you can inquire on the information easily (Inquiry--Purchasing--Vendor 1099 Details, Cards--Purchasing--1099 Details).

Cards--Purchasing--1099 Details

1099 Details window

The issue with these windows is that you have to review the 1099 information vendor by vendor.  This is normally fine if you have a small amount of 1099 vendors.  But for those users who have 100, 200, or even 500 1099 vendors, a report or export is much easier to use.

So, we look to SmartList, where there are 1099 fields available...

Microsoft Dynamics GP--SmartList--Purchasing--Vendors

With 1099 fields added

However, if we look at the 1099 fields available directly in SmartList, we will see that they are the "Amounts Since Last Close" summary fields. Which means that they are not date sensitive (they would be based on the last time you closed the calendar year for Purchasing), and they also do not include amounts by 1099 type and 1099 box although you can display the vendor's default 1099 type.  So, this may work okay if a vendor uses only one type and one box, and you are confident that you closed the Purchasing module on time for the prior calendar year and did not post back to the prior year or forward to the new year.

So, what about reporting?  Yes, you can print a report from Cards--Purchasing--1099 Details, but is only available for one vendor at a time.  In the past, I would tell users to print the 1099 edit list from Routines--Purchasing--Print 1099.

Routines--Purchasing--Print 1099

1099 Edit List

Although this report is based on the actual transaction dates (therefore it is date sensitive), it does not break out the 1099 amounts by 1099 type and box.  So, although it may work better than the SmartList, it still would work best for those vendors that use the same 1099 type and box.

So what to do if you have a variety of vendors with a variety of 1099 types and boxes?  Aside from actually printing the 1099 themselves to review?  Well, if you have SmartList Builder, it is not all that hard to create a summary SmartList to display 1099 information.  There are two primary tables involved:



The Purchasing 1099 Period Detail table includes the key fields necessary for reporting the 1099 amounts by box and type:

It is important to note that the 1099 Box Number noted in this table is NOT the actual 1099 box number.  Rather it is an incremental number assigned to the boxes in order.  So, for example the second box on a 1099-DIV is actually labeled as box 1b on the 1099 form but it would be noted as 1099 Box Number 2 in this table.   I also added the PM Vendor Master file to the SmartList, in order to pull the vendor's Tax ID Number and address.

Now, at this point, you could use this SmartList as is but you would get multiple lines for each vendor since it would list out the amounts per box per period.  If you would prefer to condense the SmartList to one line per vendor, per type, and per box you can make it a summary SmartList.  To do this, you can click the Options button in the upper right corner of the SmartList Builder window.

SmartList Builder Options button

Options Window

Select table to display summary options

Mark the Summary SmartList checkbox in the Options window to convert the SmartList to a summarized a version.  Then for each field available in the SmartList, you must select the Summary Type.  To change the Summary Type, double-click on the field.  In the first screenshot above, we are selecting the options for the fields in the Purchasing 1099 Period Details SmartList.  Note that we select the option of Group By on those fields we want to summarize by... in this case, by 1099 Box Number, 1099 Type, Vendor ID, and Year.  The 1099 Amount field is then set to be a Sum. 

For the PM Vendor Master File, we have set many of the fields to Maximum.  Keep in mind that since we are grouping by Vendor ID, the fields should be the same for each vendor.  So when we say to pull the Maximum, we are simply saying to pull that common value for the vendor.  In this case, we also set to group by the Vendor Check Name.  This is redundant, though, since we have already grouped by vendor ID.

You may also want to add a restriction to display only those summaries with a 1099 amount greater than zero. 

SmartList Builder Restrictions button

Restrictions window

Click the Restrictions button at the top of the SmartList Builder window to add a restriction. 

The final SmartList will appear something like this:

Summary SmartList Grouped by 1099 Box Number, 1099 Type, Year, and Vendor ID

The final SmartList will display the summarized information, including the 1099 Amount by type and box number (keeping in mind that is the sequential box number).  For extra credit, you can even create a series of calculated fields to break the amount out in the correct box labels.  If you do this using a CASE statement to test for the sequential box number, a couple things to keep in mind...

1.  Because box labels change for 1099 type, I would suggest creating a separate SmartList for each 1099 type and adding a restriction to only display one 1099 type per SmartList.

2.  You will want to remove the group by on the 1099 Box Number and not display it in the SmartList.  As you will be summarizing only by the 1099 Type, Year, and Vendor ID.  The calculated fields will then display (with a separate column for each box) the amounts broken out by box.

Here is a sample of a CASE statement used to pull an amount for a specific box.  You will need a separate calculated field for each box on the 1099.

CASE WHEN {Purchasing 1099 Period Detail:1099 Box Number}= '1' THEN {Purchasing 1099 Period Detail:1099 Amount} ELSE 0.00 END

Good luck and happy new year!!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.