Codename GP "12" Preliminary Features Series – 1 of 4

November 17, 2011

Codename GP “12” Preliminary Features – Part 1 

This is article is part 1 of 4 from the series Codename GP “12” Preliminary Features. Some images and content reproduced with express permission from Microsoft Business Solutions, a division of Microsoft Corporation.

DISCLAIMER: These features are subject to change.


I am only repeating what I have heard so please don’t shoot the messenger. With GP “12” to be released “sometimes next year” – and yes, December 31, 2012 is still within that time frame – there may still be features on the following list that may not make the cut. However, it was fairly clear that the boys and girls on the Microsoft Dynamics GP development team in Fargo are working their rears off to get the feature list to a state where they feel pretty comfortable, meeting the demands on the shopping list.

Straight out of GPUG Summit’s closing session, comes some of the top features being worked on, based on the traditional 4 pillar goals:

4 Core Design Pillars

Each pillar allows the engineering team to showcase a list of features that will support the objectives behind pillar.

Simplicity

The PM Reprint Check Remittance, for instance, will allow users to re-print the check remittance without having to generate the check.

Improvements have also been considered for the FA Calendar Setup feature. In this case, the Fixed Assets calendar does not have to match the fiscal calendar. You can now have multiple calendars, for example, having an asset depreciate on a fiscal year for tax purposes, if tax year and fiscal year are different, and depreciate calendar year for financial reporting purposes.
  
The Journal Entry History Inquiry will see enhancements too. In today’s world, the existing window only looks at the open tables. The plan for GP “12” is to have it look at both open and/or history.

In the reporting area, you will be delighted to know that you will now be able to choose a printer at print time. This feature was only 20 years in the making, but it’s finally here! Hey, I remember like if it was yesterday, when Windows True Type fonts became a standard part of Report Writer reports. Before that, we only had 4 fonts to play with. Challenge: name the 4 fonts available prior to the introduction of True Type fonts.

SSRS Simplicity

In addition to the printer at print time, one of the most awaited features is the ability to print SSRS reports from right within Microsoft Dynamics GP, this is, you will no longer need to wait for the Internet Explorer browser to load Report Server to display the report and will rather see the report from within GP as if you were looking at a Report Writer report in the report layout window… ah, and before I forget, Report Writer will eventually be phased out as the predominant options to render reports.

I don’t know how this plays with all that Word Template functionality released fairly recently, but I am sure a lot of you will jump on one feet in happiness knowing that you no longer will need to suffer through the tortuous process of customizing a report. My instinct tells me, that Report Writer will mainly subsists as a data delivery mechanism for XML files needed for your beloved Word Templates. If history and memory serve me well, Microsoft rarely gets rid of a working function within its products, except it becomes as annoying as the Office Paperclip Assistant. Now that I come to think, Report Writer is got to be up there for a lot of you.

I will continue tomorrow with the Productivity pillar.
Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Advertisements

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

August 8, 2011

As I begin to wrap up a Microsoft Dynamics GP 2010 R2 production upgrade from Microsoft Dynamics GP 9.0, I ran into a small issue at my client. After deploying the new SSRS reports, and as users were getting ready to try them out, we realized that some 15 logins needed to be assigned to a number of the 24 default database security roles created for the SSRS reports.

User Mappings (some information blurred to protect the client’s identity)
This would be a bit cumbersome giving the share number of clicks required to accomplish this feat. In addition, we had just setup Microsoft Dynamics GP security, and given that the SSRS database roles were similar to those in GP, something needed to be done to automate the assignment of these roles based on Microsoft Dynamics GP security roles.
As a result, I created the following script:
-- Created by Mariano Gomez, MVP

-- This code is licensed under the Creative Commons
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
use DYNAMICS;
go

DECLARE @userid varchar(50), @companyid varchar(5), @securityroleid varchar(200), @ssrsRole varchar(200);
DECLARE @sqlStmt varchar(255);

DECLARE c_reportsecurity CURSOR FOR
SELECT a.USERID, b.INTERID, a.SECURITYROLEID FROM SY10500 a
LEFT OUTER JOIN SY01500 b ON (A.CMPANYID = b.CMPANYID)
WHERE a.USERID not in ('sa', 'DYNSA', 'LESSONUSER1', 'LESSONUSER2') and a.SECURITYROLEID NOT LIKE ('MBS%')
ORDER BY a.USERID;

OPEN c_reportsecurity;
FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ssrsrole =
CASE
WHEN @securityroleid = 'ACCOUNTING MANAGER* ' THEN 'rpt_accounting manager'
WHEN @securityroleid = 'AP CLERK* ' THEN 'rpt_accounts payable coordinator'
WHEN @securityroleid = 'AR CLERK* ' THEN 'rpt_accounts receivable coordinator'
WHEN @securityroleid = 'BOOKKEEPER* ' THEN 'rpt_bookkeeper'
WHEN @securityroleid = 'CA AGENT* ' THEN ''
WHEN @securityroleid = 'CA MANAGER* ' THEN ''
WHEN @securityroleid = 'CA STAKEHOLDER* ' THEN ''
WHEN @securityroleid = 'CERTIFIED ACCOUNTANT* ' THEN 'rpt_certified accountant'
WHEN @securityroleid = 'CL AGENT* ' THEN ''
WHEN @securityroleid = 'CL DISPATCHER* ' THEN 'rpt_dispatcher'
WHEN @securityroleid = 'CL MANAGER* ' THEN ''
WHEN @securityroleid = 'CL STAKEHOLDER* ' THEN ''
WHEN @securityroleid = 'CUSTOMER SERVICE REP* ' THEN 'rpt_customer service rep'
WHEN @securityroleid = 'DP MANAGER* ' THEN ''
WHEN @securityroleid = 'DP STAKEHOLDER* ' THEN ''
WHEN @securityroleid = 'DP TECHNICIAN* ' THEN ''
WHEN @securityroleid = 'FA MANAGER* ' THEN 'rpt_accounting manager'
WHEN @securityroleid = 'FA STAKEHOLDER* ' THEN 'rpt_certified accountant'
WHEN @securityroleid = 'IT OPERATIONS MANAGER* ' THEN ''
WHEN @securityroleid = 'MBS DEBUGGER ADMIN ' THEN ''
WHEN @securityroleid = 'MBS DEBUGGER USER ' THEN ''
WHEN @securityroleid = 'OPERATIONS MANAGER* ' THEN 'rpt_operations manager'
WHEN @securityroleid = 'ORDER PROCESSOR* ' THEN 'rpt_order processor'
WHEN @securityroleid = 'PAYROLL CLERK* ' THEN 'rpt_payroll'
WHEN @securityroleid = 'PM AGENT* ' THEN ''
WHEN @securityroleid = 'PM MANAGER* ' THEN ''
WHEN @securityroleid = 'PM STAKEHOLDER* ' THEN ''
WHEN @securityroleid = 'POWERUSER ' THEN 'rpt_power user'
WHEN @securityroleid = 'PURCHASING AGENT* ' THEN 'rpt_purchasing agent'
WHEN @securityroleid = 'PURCHASING MANAGER* ' THEN 'rpt_purchasing manager'
WHEN @securityroleid = 'RT AGENT* ' THEN ''
WHEN @securityroleid = 'RT MANAGER* ' THEN ''
WHEN @securityroleid = 'RT STAKEHOLDER* ' THEN ''
WHEN @securityroleid = 'SHIPPING AND RECEIVING* ' THEN 'rpt_shipping and receiving'
WHEN @securityroleid = 'WAREHOUSE MANAGER* ' THEN 'rpt_warehouse manager'
WHEN @securityroleid = 'WENNSOFT SMS CONTRACTS* ' THEN ''
WHEN @securityroleid = 'WENNSOFT SMS DISPATCHER* ' THEN ''
WHEN @securityroleid = 'WENNSOFT SMS POWER USER* ' THEN ''
WHEN @securityroleid = 'WENNSOFT SMS SETUP* ' THEN ''
WHEN @securityroleid = 'WSJC ACCOUNTANT* ' THEN ''
WHEN @securityroleid = 'WSJC ACCOUNTING MANAGER* ' THEN ''
WHEN @securityroleid = 'WSJC ADMIN* ' THEN ''
WHEN @securityroleid = 'WSJC BILLING CLERK* ' THEN ''
WHEN @securityroleid = 'WSJC POWERUSER* ' THEN ''
WHEN @securityroleid = 'WSJC PROJECT MANAGER* ' THEN ''
WHEN @securityroleid = 'WSTT PAYROLL CLERK* ' THEN ''
WHEN @securityroleid = 'WSTT POWERUSER* ' THEN ''
END

IF (@ssrsRole <> '')
BEGIN
SET @sqlStmt = 'USE ' + rtrim(@companyid) + '; EXEC sp_addrolemember ' + QUOTENAME(@ssrsRole, '''') + ',' + QUOTENAME(rtrim(@userid), '''');
EXEC(@sqlStmt);
END
FETCH NEXT FROM c_reportsecurity INTO @userid, @companyid, @securityroleid;
END

CLOSE c_reportsecurity;
DEALLOCATE c_reportsecurity;
The script looks at the Security Assignment User Role table (SY10500) and retrieves the physical company database from the Company Master table (SY01500), then assign an SSRS database security role to each of the Microsoft Dynamics GP default roles. If a role does not exist, you can choose to leave the assignment blank.

The script then proceeds to evaluate the database security role obtained, then creates a SQL string that can be executed. The SQL string uses the sp_addrolemember system stored procedure to add the corresponding SQL login to the role. A cursor is used to loop through each user, company, and security role combination to obtain and assign the proper SSRS database role.

You can choose to add custom security roles or roles for third party applications that deploy their own SSRS reports to the above script.

This definitely helped saving some time… phew!

Until next post!

MG.-

Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


SSRS: GL Trial Balance Summary report returns no data

July 11, 2011

This one comes courtesy of my friend Steve Sieber at McGladrey.

After installing Microsoft Dynamics GP 2010 R2 and deploying the SQL Server Reporting Services reports, you will encounter an issue when printing the GL Trial Balance Summary SRS report located under Financial.

1. Launch Report Manager and click on the company for which you would like to run the report (the issue can also be reproduced in the Fabrikam (TWO) company database). Click on Financial | Trial Balance Summary, enter all the parameters and options for the report the click on View Report, the following is returned:

GL Trial Balance Summary – SSRS

As you can see, even though the correct parameters are selected, the report returns no records.

2. If the GL Trial Balance Summary report is executed from GP with the same parameters, the report correctly delivers the expected records and result:

GL Trial Balance Summary – GP

From a technical perspective, the GL Trial Balance Summary SSRS report executes the dbo.seeGLPrintSRSTrialBalance stored procedure. The issue appears to be that the #GLTBDTemp temp table does not get populated with the records needed to render the report. You can test the stored procedure by executing the following statement from SQL Server Management Studio against any company database.

exec seeglPrintSRSTrialBalance 0,0,0,‘000-0000-00’,‘999-9999-99’,’01/01/2017′,’12/31/2017′,2017,0,1,1

This issue has been reproduced by Microsoft Support and they are currently researching the problem for a solution. 

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


New Article on MSDynamicsWorld: Michael Johnson on "Filtering Salesperson-specific data with SSRS"

February 10, 2010

“It didn’t take long to figure out that the problem wasn’t with Business Portal, but rather the way they were trying to use it and NOT using SQL Server Reporting Services (SSRS). Both tools have a place in their environment, but one is not a substitute for another”

My friend Michael Johnson, a.k.a. “The MBS Guru” has finally come out of hiding and has decided to publish a really clever solution for filtering salesperson-specific data in GP using SSRS over at MSDynamicsWorld. The good thing about Michael’s solution is, you can apply it to pretty much any report that requires such type of access and filtering capabilities. They don’t call this guy the MBS Guru for nothing!

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/