Fixing Base Period On Management Reporter Sample Company Reports

I did a couple of posts a while ago on fixing the sample company reports (the first post fixed the Row and Column Definitions and the second the Reporting Tree Definitions), but there has been another issue with them that … Continue reading

Continue reading here:
Fixing Base Period On Management Reporter Sample Company Reports

November 18, 2013 · Ian Grieve · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 294

Weekly Topic: Word Document

Did you know that you can use Word to create professional looking documents you can send to your customers like invoices and statements?  I hope so because we added Word forms in GP2010.  In GP2013 we took it a step further based on your feedback.

Customers now have the ability to email their word documents with password protection.  This will ensure the recipient of the document will not be able to edit the document to make changes without entering a password.

The password can either be automatically created by the system or the end-user can assign their own password.  The customer can also choose not to assign password protection to their emailed word documents.   

Read More:
Weekly Topic: Word Document

November 15, 2013 · Pam Misialek · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 283

Recursive Querying in SQL Server

Viswanathan Neelakantan - Click for blog homepageIn this post we will discuss about the use of recursive querying in SQL Server.

Recently, I got a request from a Partner to develop a SSRS report for the Indented Bill of Materials in the Inventory module. I was going through the Dexterity Indented Bill of Materials report and found out that the report uses a temporary table. For filling the temporary table with the Inventory Bill of Materials hierarchy, Dynamics GP recursively loops around a logic written in Dexterity. We are stuck here, because we won’t be able to consume the logic that Dynamics GP uses to build the Inventory Bill of Materials hierarchy in SSRS. But, luckily we can create a recursive query in SQL Server to get the Inventory Bill of Materials hierarchy without spending much time.

Recursive Query to get the Inventory Bill of Materials hierarchy

DECLARE @billNumber CHAR (31)
DECLARE @BMReporting TABLE
(
ITEMNMBR CHAR (31),
CMPTITNM CHAR (31),
ITEMDESC CHAR (111)
)

SELECT @billNumber = ‘FAXX-FG3-0001′;

— By Recursive Query using CTE build the Indented BOM
WITH BMReporting (ITEMNMBR, CMPTITNM, ITEMDESC)
AS (
— Anchor member definition
SELECT CAST (NULL AS VARCHAR (64)) AS ITEMNMBR,
CAST (LTRIM (RTRIM (BMHDR.ITEMNMBR)) AS VARCHAR (64)) AS CMPTITNM,
LTRIM (RTRIM (ITMMSTR.ITEMDESC)) AS ITEMDESC
FROM dbo.BM00101 BMHDR
INNER JOIN IV00101 ITMMSTR ON BMHDR.ITEMNMBR = ITMMSTR.ITEMNMBR
WHERE BMHDR.ITEMNMBR = @billNumber
AND BMHDR.Bill_Status = 1
UNION ALL
— Recursive member definition
SELECT CAST (RTRIM (LTRIM (BMDTL.ITEMNMBR)) AS VARCHAR (64)),
CAST (RTRIM (LTRIM (BMDTL.CMPTITNM)) AS VARCHAR (64)),
LTRIM (RTRIM (ITMMSTR.ITEMDESC))
FROM dbo.BM00111 AS BMDTL
INNER JOIN dbo.IV00101 ITMMSTR ON ITMMSTR.ITEMNMBR = BMDTL.CMPTITNM
INNER JOIN BMReporting AS BMReporting ON BMDTL.ITEMNMBR = BMReporting.CMPTITNM
WHERE BMDTL.Bill_Status = 1
)
— Statement that executes the CTE
INSERT INTO @BMReporting
SELECT ITEMNMBR,
CMPTITNM,
ITEMDESC
FROM BMReporting
SELECT * FROM @BMReporting

–@billNumber :- The Bill Number for which need the hierarchy

Please refer the following link for more information on Recursive Querying in SQL Server.

http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Using the above recursive query as a base, built a Stored Procedure that accepts ‘Bill Number’ as an input parameter and returns the Inventory Bill of Materials hierarchy as a result set.

I have attached the SQL object and RDL file developed along with this post for your reference.

Hope this helps…

Until next post !!

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Original post:
Recursive Querying in SQL Server

November 15, 2013 · Viswanathan Neelakantan · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 680

Weekly Topic: SRS Report Access

Did you know that you can access SQL Reporting Service Reports directly from a GP form? 

This feature is cool because it allows customers to print SQL Server Reporting Services (SSRS) reports directly from a Microsoft Dynamics GP form making it easier to access information in a format that makes sense.

The user may select to print SSRS reports from various forms across General Ledger, Receivables Management, Sales Order Processing, Payables Management, Inventory Management, Human Resources, and Payroll.

Users can simply select to print a SSRS report assigned to the Microsoft Dynamics GP Form and the report will render information based on the record retrieved on the form.  Customers can print the reports we ship or they can create their own custom SSRS reports to print.

View article:
Weekly Topic: SRS Report Access

November 14, 2013 · Pam Misialek · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 391

Weekly Topic: Sorting Lookups

Did you know what we added several features that made it easier to use Microsoft Dynamics GP?  They reduce the number of clicks it takes to get your work done.

Customers have the ability to choose SmartList Favorites within lookups and select to save that lookup as the default.  Once this has been chosen as the default, the next time the user enters the lookup the list will populate based off the view they selected as the default. 

This gives the customer the flexibility to set the lookups based off how they need to see the data and can save them valuable time in the application.  The Set as Default option is available within the Customer and Prospects, Vendors, Items, Salespeople and Employees lookups. 

Read More:
Weekly Topic: Sorting Lookups

November 13, 2013 · Pam Misialek · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 300

Web Client Wednesday – Microsoft Dynamics GP Reset User Password not working

Continuing with the Web Client Wednesday series, today I wanted to highlight the fact that the web client is still a work in progress. While a lot of ground has been covered over the past 3 years, the product continues to evolve, and of course, in the meantime there will be little issues here and there that may affect the way you work.

Previous articles in the series:

Web Client Wednesday – Browser Support

Web Client Wednesday – User Interface Differences



Just recently I ran into a Microsoft Dynamics GP 2013 web client deployment case where the advanced SQL Server options are enabled to enforce password expiration. These properties are a part of the Enforce Password Policy options on the User Setup window.

User Setup

When a password expires and a user attempts to log into the desktop client, they are immediately prompted to change their password and the Change User Password dialog window displays to allow the user to enter the old password, and enter and confirm the new one.

Change User Password window

The behavior is different under the web client, however. Whenever a password expires, the Change User Password dialog displays as expected, but in this case, the Old Password field is disabled and the user would only be prompted to enter the new password and confirm.

Change User Password dialog on Web Client 

Microsoft Dynamics GP uses the old password to establish a connection to the database – at this point, the user has not validated their SQL credentials – in order to set the new password. If the old password is not supplied the connection cannot be established, which in turn fails the attempt to change the new password.

This issue has been identified as a bug by Microsoft Support in Fargo and is currently in the queue for resolution. If you are affected by this problem, you are encouraged to open a support case and add yourself to the list for a fix.



For more information on how the password system works in Microsoft Dynamics GP, please take a look at the following articles:

Developing for Microsoft Dynamics GP – Why does Microsoft Dynamics GP encrypt passwords?
Developing for Microsoft Dynamics GP – Microsoft Dynamics GP Application Password System
Microsoft Support – FAQ about the Advanced SQL Server options in the User Setup Window

Until next post!

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

More:
Web Client Wednesday – Microsoft Dynamics GP Reset User Password not working

November 13, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 498

The Dynamics GP Blogster: Web Client Wednesday – Microsoft …

Microsoft Support – FAQ about the Advanced SQL Server options in the User Setup Window Until next post! MG.- Mariano Gomez, MVP IntellPartners, LLC http://www.IntellPartners.com/. Posted by Mariano Gomez at 8:00 AM …. Exchange Rates Feed – Few days back I got mails from many customers reporting that the exchange rates feed stopped working due to an error with the host, the tool was linked t… 17 hours ago 5 days ago. Dynamics Confessions Blogspot.

Read the article:
The Dynamics GP Blogster: Web Client Wednesday – Microsoft …

November 13, 2013 · Mariano Gomez · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 269