Error Converting Request to Purchase Order–Business Portal 5.1

 

I have installed Business Portal 5.1 for one of my customers and been through the below error in converting Purchase Request into Purchase Order

The specified Protocol is invalid

Doing researches about this subject returned that the web.config of the business portal might be missing from this path “C:\Program Files\Microsoft Dynamics\Business Portal” and it was!

To resolve this I have connected to another client whose running the business portal with no issues and copied the web.config! It worked perfectly, below is the web.config content:

<?xml version="1.0" encoding="utf-8" ?>
<
configuration>

<
system.web>

<
xhtmlConformance mode="Legacy" />

</
system.web>


<
appSettings>

<
add key="Protocol" value="1"/>
<
add key="TaxEngineServiceAssembly0" value="Microsoft.Business.Taxes.Services"/>
<
add key="TaxEngineServiceClass0" value="Microsoft.Business.Taxes.TaxEngine"/>

<
add key="TaxEngineDataServiceAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEngineDataServiceClass0" value="Microsoft.Dynamics.Common.TaxEngineData"/>

<
add key="TaxEnginePreCalculateDocumentEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreCalculateDocumentEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreCalculateDocumentEventMethod0" value="DocumentPre"/>

<
add key="TaxEnginePreGetTaxGroupIDsEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreGetTaxGroupIDsEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreGetTaxGroupIDsEventMethod0" value="PreGetTaxGroupIDs"/>

<
add key="TaxEnginePreCalculateCodeEventAssembly0" value="Microsoft.Dynamics"/>
<
add key="TaxEnginePreCalculateCodeEventClass0" value="Microsoft.Dynamics.Common.TaxEngineISV"/>
<
add key="TaxEnginePreCalculateCodeEventMethod0" value="PreCalculateCode"/>

</
appSettings>

</
configuration>

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Purchase Order Commitments View

I been working on a report where the customer requested to view the payment voucher with its corresponding commitments information and had a need to have Committed Amount, Actual Amount and Budget Amount, view below details all the needed information about this subject:

SELECT     ACTINDX, BUDGETAMT,  
ISNULL((SELECT SUM(DEBITAMT - CRDTAMNT) AS Actual FROM dbo.GL20000
WHERE (OPENYEAR = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS ACTUAL,

ISNULL((SELECT SUM(DEBITAMT - CRDTAMNT) AS Actual FROM dbo.GL10001
WHERE (YEAR1 = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS UNPOSTED,

ISNULL((SELECT SUM(Committed_Amount) FROM dbo.CPO10110 WHERE
(YEAR(REQDATE)= MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS Committed_Amount

FROM
(SELECT YEAR1, SUM(BUDGETAMT) AS BUDGETAMT, ACTINDX FROM dbo.GL00201 AS MASTER
WHERE
(BUDGETID = (SELECT TOP (1) BUDGETID FROM dbo.CPO40002
WHERE (YEAR1 = YEAR(GETDATE())))) GROUP BY ACTINDX, YEAR1) AS MAIN


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Why do I see empty charts in Fabrikam?

 

I have installed Dynamics GP 2010 R2 and really enjoyed walking through dashboards and KPIs added to the application, but most of the KPI’s does not show the actual company data, instead it is showing zeros as below:

image

This is actually not related to an installation issue, it is due to the fact that the report is using the current date while Fabrikam uses 2017 as the default year, to view the report with data, just click on the “View” icon and select the report:

image

Change the date:

image

And enjoy!

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP 2010 R2 Business Intelligence Installation Error maxRequestLength

 

During Dynamics GP 2010 R2, I have selected to deploy Dynamics GP Business Intelligence Reports over SQL Server Reporting Services, but before starting the installation, the system generated an error that maximum number of retries has been exceeded and was requested to set a maxRequestLength flag in the web.config.

Web.config could be located under the installation path of SQL Server Reporting Services, in my case it was under the following path:

C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services\ReportServer

in the web.config, search for <httpRuntime executionTimeout=”9000″ /> and include the variable there to make it looks like the below:

<httpRuntime executionTimeout=”9000″ maxRequestLength=”20690″/>

Somehow, 20690 in specific is required, it will not accept number higher!

Update: Below is the exact error message:

The deployment has exceeded the maximum request length allowed by the target server. Set maxRequestLength = “20690” in the web.config file and try deploying again.

Enjoy!

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP Reporting Series: Customer Statement

It seems I totally forgot to publish this article and thought that I finalized the series!

Customer statements are normally dependant on the each client process, end user will normally request the customer statement to be in the way that fulfills their needs, some users will require the statement to integrate the payment document management module and some requires the aging.

However, normally the customer statement option in the utilities covers almost all needed statements, below statement is a standard statement could generate the customer statement in “Payment – Invoice” columns which make easier to the accountant the understanding of customer transactions.

SQL Command:

SELECT      CUSTNMBR,    
DOCNUMBR AS DOCNUM,     DOCDATE,    
CASE RMDTYPAL    
WHEN 1 THEN ‘SLS’       WHEN 2 THEN ‘SCP’     
WHEN 3 THEN ‘DR’       WHEN 4 THEN ‘FIN’     
WHEN 5 THEN ‘SVC’       WHEN 6 THEN ‘WRN’     
WHEN 7 THEN ‘CR’       WHEN 8 THEN ‘RTN’     
WHEN 9 THEN ‘PMT’      END AS CODE,    
ISNULL(CASE RMDTYPAL    
WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT    
WHEN 4 THEN ORTRXAMT WHEN 5 THEN ORTRXAMT    
WHEN 6 THEN ORTRXAMT ELSE 0    
END,0) AS INVOICE,    

ISNULL(CASE RMDTYPAL    
WHEN 7 THEN -(CURTRXAM)     WHEN 8 THEN -(CURTRXAM)    
WHEN 9 THEN -(CURTRXAM)     ELSE 0    
END,0) AS PAYMENT,    DOCNUMBR AS APPLIEDTO    
FROM RM20101
WHERE  (
(RMDTYPAL = 7 AND CURTRXAM 0)
OR (RMDTYPAL = 8 AND CURTRXAM 0) OR (RMDTYPAL = 9 AND CURTRXAM 0)
OR RMDTYPAL = 1     OR RMDTYPAL = 2 OR RMDTYPAL = 3
OR RMDTYPAL = 4 OR RMDTYPAL = 5 OR RMDTYPAL = 6) 
AND VOIDSTTS 1     
UNION     
SELECT      CUSTNMBR,    
APFRDCNM AS DOCNUM,     DATE1 AS DOCDATE ,    
CASE APFRDCTY
WHEN 7 THEN ‘CR’ WHEN 8 THEN ‘RTN’       
WHEN 9 THEN ‘PMT’ END AS CODE,         
0 AS INVOICE,    
ISNULL(CASE APFRDCTY     WHEN 7 THEN APPTOAMT    
WHEN 8 THEN APPTOAMT     WHEN 9 THEN APPTOAMT    
ELSE 0     END,0) AS PAYMENT     ,    
APTODCNM AS APPLIEDTO    
FROM RM20201
WHERE POSTED 0     
ORDER BY DOCDATE    

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the below:

Crystal Report Design:

 image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: PM Manual Payment

Finally we’ll need to print the vendor payment, we’ll use the Manual Payment form to enter the transaction and print out the payment using the report, below is the needed command:

SQL Command:

SELECT    
dbo.PM10400.PMNTNMBR, dbo.PM10400.DOCNUMBR,
dbo.PM10400.DOCDATE, dbo.PM10400.VENDORID,
dbo.PM00200.VENDNAME, dbo.PM10400.PYENTTYP,
dbo.PM10400.CARDNAME, dbo.PM10400.CHEKBKID,
dbo.PM10100.CRDTAMNT, dbo.PM10100.DEBITAMT,
dbo.PM10100.DSTINDX, dbo.PM10100.ORCRDAMT,
dbo.PM10100.ORDBTAMT, dbo.PM10400.DOCAMNT,
dbo.CM00100.DSCRIPTN, dbo.PM10400.CURNCYID,
dbo.PM10100.DISTTYPE, dbo.GL00100.ACTDESCR,
dbo.GL00105.ACTNUMST, dbo.PM00200.VNDCHKNM,
dbo.PM10400.TRXDSCRN
FROM         dbo.PM10400
INNER JOIN dbo.PM10100 ON dbo.PM10400.VCHRNMBR = dbo.PM10100.VCHRNMBR
INNER JOIN dbo.PM00200 ON dbo.PM10400.VENDORID = dbo.PM00200.VENDORID
INNER JOIN dbo.GL00100
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
ON dbo.PM10100.DSTINDX = dbo.GL00100.ACTINDX
LEFT OUTER JOIN dbo.CM00100 ON dbo.PM10400.CHEKBKID = dbo.CM00100.CHEKBKID
WHERE     (dbo.PM10400.CNTRLTYP = 1) and  (dbo.PM10100.CNTRLTYP = 1)

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the below:

Crystal Report Design:

 image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: PM Transaction Entry

When we need to perform any adjustment transactions on our vendors, we’ll need to print a document to be attached to the original document sent by the vendor, below is the needed command:

SQL Command:

SELECT    
dbo.PM10000.DOCNUMBR, dbo.PM10000.DOCTYPE,
dbo.PM10000.DOCAMNT, dbo.PM10000.DOCDATE,
dbo.PM10000.PYMTRMID, dbo.PM10000.SHIPMTHD,
dbo.PM00300.ADDRESS1, dbo.PM00300.ADDRESS2,
dbo.PM00300.ADDRESS3, dbo.PM00300.CITY,
dbo.PM10000.PORDNMBR, dbo.PM10000.VCHRNMBR,
dbo.PM10000.TRXDSCRN, dbo.PM10000.PRCHAMNT,
dbo.PM10000.TRDISAMT, dbo.PM10000.TAXAMNT,
dbo.PM10000.FRTAMNT, dbo.PM10000.MSCCHAMT,
dbo.PM10000.CASHAMNT, dbo.PM10000.CHEKAMNT,
dbo.PM10000.CRCRDAMT, dbo.PM00200.VENDNAME,
dbo.PM10000.DISTKNAM, dbo.PM10000.CURTRXAM,
dbo.PM10000.CURNCYID, dbo.PM10000.VCHNUMWK,
dbo.PM10000.VENDORID, dbo.MC020103.ORCTRXAM,
dbo.MC020103.ORFRTAMT, dbo.MC020103.ORTAXAMT,
dbo.MC020103.ORCASAMT, dbo.MC020103.ORCHKAMT,
dbo.MC020103.ORCCDAMT, dbo.MC020103.ORDISTKN,
dbo.MC020103.ORWROFAM, dbo.MC020103.OMISCAMT,
dbo.MC020103.OPURAMT, dbo.MC020103.ORTDISAM
FROM         dbo.PM00200
INNER JOIN dbo.PM10000 ON dbo.PM00200.VENDORID = dbo.PM10000.VENDORID
LEFT OUTER JOIN dbo.MC020103 ON dbo.PM10000.DOCTYPE = dbo.MC020103.DOCTYPE
AND dbo.PM10000.VCHNUMWK = dbo.MC020103.VCHRNMBR
LEFT OUTER JOIN dbo.PM00300 ON dbo.PM10000.VENDORID = dbo.PM00300.VENDORID
AND dbo.PM10000.VADDCDPR = dbo.PM00300.ADRSCODE
WHERE dbo.PM10000.VCHNUMWK = {?VCHNUMWK}

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the below:

Crystal Report Design:

 image

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,

Mohammad R. Daoud – CTO
MVP, MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com