How to reset Business Analyzer settings

Just recently, I ran into an interesting case where the consultant had installed and configured Business Analyzer for a user. However, during the configuration process, the consultant marked all available reports for all available companies, which put Business Analyzer into a tailspin as it attempted to load all this information. The consultant proceeded to shut down the application using the Task Manager program.

When he restarted Business Analyzer, the application went back into a tailspin, displaying only the loading screen with a "Connecting to Server" message for over an hour. Fearing this would be a permanent issue due to the number of reports and companies selected, the question was just obvious: how to reset Business Analyzer configuration settings.

The boring theory

If you have downloaded Business Analyzer from the Windows Store or are still running the Business Analyzer desktop client, chances are you had to go through a setup process. In Windows 8, this involves swiping the Edge UI to obtain the charms and clicking on Settings to obtain the Configuration pane.

Business Analyzer for Windows 8 configuration

Once in the Configuration pane you are asked to enter information about the Report Server from which reports will be loaded. Once the credentials have been validated, you will be directed to add reports accordingly.

Business Analyzer Reports Configuration

Selecting all reports from all companies under a specific instance (I'm running GP 2013) can have adverse performance effects if you are in an environment with multiple companies, as Business Analyzer attempts to obtain information for each report, for each company from Report Server. The important part though is, this information is stored locally, under the user profile folder, in the hidden AppData folder, where an XML configuration file is created (user.config). Since all reports were selected for all companies, building this file would normally take a considerable amount of time.

The Solution

To fix the problem all that's needed is to remove the Business Analyzer configuration folder, which can be found under:

"C:\Users\userid\AppData\Local\Microsoft\BusinessAnalyzer.exe_Url_..." 

After this, you can re-launch Business Analyzer and go back to the configuration options where you will be prompted to enter the server information and selected the reports to display once again. Easy enough!

Until next post!

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

Read More

Microsoft SQL Server security roles and Microsoft Dynamics GP SSRS reports

It's not uncommon to find system administrators and consultants alike struggling to deploy Microsoft Dynamics GP SRS reports in an environment, while trying to keep these reports secured. I find that the main issue revolves around understanding the different moving parts: domain accounts, GP user logins, the SQL Server database roles created for reporting, the reports web site security, the reports folder security and the list goes on.

So here is a list of resources that I believe will help with this:

The best place to start is the SQL Server Reporting Services Guide, which has all of the information for installing, deploying and setting up security for SRS reports to use with Dynamics GP. This guide is available for download from the Microsoft Download Center.

Microsoft Dynamics GP 2013 Guide: SQL Server Reporting Services

In order for users to access SRS reports (default) for Dynamics GP, they need to have the following permissions:

1. Access to the Reporting Services web site (native mode) or report library (SharePoint Integrated). I have found the following resources to be extremely useful when configuring Reporting Services security in native mode:

Granting Permissions on a Native Mode Report Server

If you are dealing with Reporting Services in SharePoint Integrated mode, the following is a good resource:

Security Overview for Reporting Services in SharePoint Integrated Mode


2. Permissions to the SRS report files themselves. The following TechNet video shows just that:


3. Database security roles (RPT_ ) under the DYNAMICS and/or company databases in order to pull data into the SRS reports. You will want to take a look at the following KB article 954242 - Frequently asked questions about the integration of SQL Server Reporting Services (SSRS) with Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010, which can be found at:

http://support.microsoft.com/kb/954242

I hope you find these resources useful.

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 10 Digest

It's been 10 weeks so far of great features in Microsoft Dynamics GP 2013. The list seems to be endless, but at the same time super informative. This shows the amount of work that went into making this one of the biggest releases ever.

Enjoy this weeks features:

Feature of the Day: Journal Entry History Inquiry

Journal Entry Inquiry

Original article: Feature of the Day: Journal Entry History Inquiry
Link to presentation: GP2013 FOD Journal Entry History Inquiry.ppsx
Learning script: N/A


Feature of the Day: Edit Remit To Address

Edit Remit to Address

Original article: Feature of the Day: Edit Remit To Address
Link to presentation: GP2013 FOD Edit Remit To Address.ppsx
Learning script: N/A


Feature of the Day: Update 1099 Information

Update 1099 Information

Edit 1099 Transaction Information

Original article: Feature of the Day: Update 1099 Information
Link to presentation: GP2013 FOD Update 1099 Information.ppsx
Learning script: N/A


Feature of the Day: Update Item Allocation Inquiry in Field Service

Service Call Entry - Parts showing allocated item

Can now inquiry Field Service allocations from Item Allocation Inquiry window

Original article: Feature of the Day: Update Item Allocation Inquiry in Field Service
Link to presentation: GP2013 FOD Update Item Allocation Inquiry from Field Service.ppsx
Learning script: N/A


Feature of the Day: Multitenant SQLReports

Can now select deployment folder for reports of a specific company

Original article: Feature of the Day: Multitenant SQLReports
Link to presentation: GP2013 FOD Multi-tenant SQL Reports Server.ppsx
Learning script: GP2013 Multitenant SQL Reports Server Learning Script

Until next post!

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

Read More

Microsoft Dynamics GP 2013 "Feature of the Day" - Week 4 Digest

Week 4th of the Feature of the Day series went by with some really cool additions. The central theme: lots of visual cues for improved transaction readability and more reporting capabilities from within Microsoft Dynamics GP - yes, I can smell it too... Report Writer days, as the main user experience, are nearing the end.
Feature of the Day: Email Indicators

Email Indicator

Original article: Feature of the Day: Email Indicators
Link to presentation: GP2013 FOD Email Indicators.ppsx
Learning script: N/A


Feature of the Day: Reconcile IV to GL

Reconcile IV to GL spreadsheet

Original article: Feature of the Day: Reconcile IV to GL
Link to presentation: GP2013 FOD Reconcile IV to GL.ppsx
Learning script: GP2013 Reconcile IV to GL Script.docx


Feature of the Day: SRS Reports on Forms

SSRS Reports run from the GP forms directly

Original article: Feature of the Day: SRS Reports on Forms
Link to presentation: GP2013 FOD SSRS Reports on Forms.ppsx
Learning script: GP2013 Printing SSRS Reports From Forms Learning Script.docx


Feature of the Day: Item Standard Cost Adjustment

Change Item Standard Cost

Original article: Feature of the Day: Item Standard Cost Adjustment
Link to presentation: GP2013 FOD Item Standard Cost Adjustment.ppsx
Learning script: N/A


Feature of the Day: Reconciled Indicator

Reconciled Indicator

Original article: Feature of the Day: Reconciled Indicator
Link to presentation: GP2013 FOD_Reconciled_Indicators.ppsx
Learning script: N/A


Until next post!

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

Read More

SSRS: GL Trial Balance Summary report returns no data - Follow Up

Back in July of last year, I wrote an article on a reported issue with the SSRS GL Trial Balance reports were not returning any data with Microsoft Dynamics GP 2010 and 2010 R2 (SP2) - see SSRS: GL Trial Balance Summary report returns no data.

Since then, this problem has been documented in KB article 2588519 - GL Trial Balance SRS Reports return no data using Microsoft Dynamics GP and a workaround provided in the same article to fix the ailing stored procedure, dbo.seeglPrintSRSTrialBalance, causing data not to be returned.

The good news is, this problem is scheduled to be fixed in Service Pack 3.

Until next post!

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

Read More

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

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/

Read More

SQL: Assigning Microsoft Dynamics GP Users to SSRS Database Roles

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/

Read More

SSRS: GL Trial Balance Summary report returns no data

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/

Read More

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

"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/

Read More