Pages

Sunday, 21 August 2011

My Second Excel Based Customized Dash Board


Sunday, 14 August 2011

Making Reporting Easy With Excel

With advancement in Microsoft Office Tools, Dynamics ERPs always have new features to enjoy, In my previous posts i have discussed how Excel reports could help non technical users to extract easy information in form of Excel File from MS Dynamics GP.


Technical Users can just write SQL Queries and will be able to get results via Excel File, in this method files are automatically/manually refreshed.

To Enter SQL Query, Extract one excel file from MS DYNAMICS GP UNDER EXCEL REPORTS in order to get default connection settings. Once you get the data from default report make a backup of that report and use it every time for new reports.

To Enter SQL Query,

Within Excel  > Data Tab > Connections > [Select the Connection] Properties > Definition Tab > Enter Query in Command Text




Click ok, Click Refresh All.

Some Basic Queries you can work with,

  • SOP Transaction [ Both from Work and History Table ], this query helps you track where document exist and what's the status of the document.


SELECT 'Work' DataType,SOP10100.SOPNUMBE,
SOP10100.SOPTYPE,
SOP10100.DOCDATE,
SOP10100.CUSTNMBR,
SOP10100.CUSTNAME,
SOP10100.BACHNUMB,
SOP10100.CSTPONBR,
SOP10100.MSTRNUMB,
SOP10100.CNTCPRSN,
SOP10100.SLPRSNID,
SOP10200.ITEMNMBR,
SOP10200.ITEMDESC,
SOP10200.UNITPRCE,
SOP10200.XTNDPRCE,
SOP10200.QUANTITY,
SOP10200.LOCNCODE
FROM SOP10100
INNER JOIN SOP10200 ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
AND sop10100.soptype = sop10200.soptype
UNION ALL
SELECT 'History' DataType ,SOP30200.SOPNUMBE,
SOP30200.SOPTYPE,
SOP30200.DOCDATE,
SOP30200.CUSTNMBR,
SOP30200.CUSTNAME,
SOP30200.BACHNUMB,
SOP30200.CSTPONBR,
SOP30200.MSTRNUMB,
SOP30200.CNTCPRSN,
SOP30200.SLPRSNID,
SOP30300.ITEMNMBR,
SOP30300.ITEMDESC,
SOP30300.UNITPRCE,
SOP30300.XTNDPRCE,
SOP30300.QUANTITY,
SOP30300.LOCNCODE
FROM SOP30200
INNER JOIN SOP30300 ON SOP30200.SOPNUMBE = SOP30300.SOPNUMBE
AND SOP30200.soptype = SOP30300.soptype



  • Excel Based Dash Board
With some basic Excel Skills you can design you own customize dashboards, look at this example.

Drop you email to get this dashboard :)


Tuesday, 28 June 2011

Installing Fabrikam Reports in Management Reporter (MR)

Unlike FRx, Management Reporter does not provide default reports for Fabrikam, we need to upload the default reports in order to get the sample reports. Follow the procedure in order to get Sample Reports for Fabrikam.


In order to install MR follow my Management Reporter Installation Post June 2011.


Click here to download MR demo reports [Partner Source Access would be required] or alternatively Provide me you email address so I could email you the reports

  • In order to deploy the reports open MS Dynamics ERP MR Designer, Companies and Create a new Company as follows,






  • Click Finish and set the Company as default, if you unable to set the company as default troubleshoot the problem, likely MR installation is not properly done

  • In order to Import the Reports, navigate to Company > Building Block Groups within MR Designer, select the company and click import




Click Select All and Import



Note*



For MS Dynamics GP 2010 SP2 Fabrikam we would be required to rename the Segments name in order to deploy the reports,


Navigate to Administration > Setups > Company > Account Format > Change the Segments


Segment1
Segment2
Segment3


Make sure you perform above activity before deploying the reports.


Let me know if you are stuck somewhere.




Mustafa Chohan

Reduce work load while creating Items in Inventory Management

Traditionally it is always required in MS Dynamics GP to follow the steps to create items, that is create items in Inventory Cards, Assign Vendors , Assign Sites, Create Price List etc. Now with rich features of MS Dynamics GP 2010 user could easily copy all the attributes of one item to new item created.


To Copy Item, navigate to Item Maintenance Window Cards > Inventory > Item. Enter the Item number and click Copy. Item Copy Window will appear, select the Item Number to copy information from and select appropriate options and click Copy



More of GP 2010 rich features shall be discussed in future.


Saturday, 25 June 2011

Deploying Excel Reports MS Dynamics GP 2010

Excel Reports,

With the help of Excel Reports Non Technical Users can extract data form Microsoft Dynamics GP to Microsoft Excel. They can user their excel reporting skills to make their reports more productive and make them as per their needs.


Remember with these reports, you can create a Dashboard/Reporting Tools for executive who only view reports in GP, In single Excel File, with multiple sheets they have all decision tools without logging into GP. Excel Reports are automatically refreshed or you can provide functionality on sheet to be refreshed manually.

In order to deploy excel reports in Microsoft Dynamics GP 2010, you are required to create a folder where you wish the reports to be placed. Create a Folder in C:\ as ExcelReports.

In MS Dynamics GP 2010, navigate to Administration > Setup > System > Reporting Tools Setup > Excel Reports




Enter the path for System Level, and click Deploy Reports, print the status report.

To get the excel reports, navigate to any series and click Excel Reports 


To print the report, mark the report and click Excel Report Button on the TOP BAR.

In my future posts I shall tell you how Excel Reports can be used by non technical people by simply using Graphs and Charts in Excel.

Management Reporter Installation

Before you start with Management Reporter Installation make sure you have following pre-requisites installed on you computer,






  • It can only be installed on DOMAIN Connected Computer
  • Setup Must be run from Domain User
  • IIS 6 Metabase Compatibility Feature for IIS 7
  • ASP.Net
  • WCF HTTP Activation
  • SQL SERVER COMPACT 3.5 SERVICE PACK 2 
Start Management Reporter installation, I follow the following method of installation and also prefer it,

Install Management Reporter Database
Install Management Reporter Server
Install Management Reporter Client
Install Management Reporter Data Provider


You need to install all the above components to Design and View reports on Management Reporter.

It is always advised to create a new Domain User for Management Reporter, and supply that user whenever Windows User is required for setup. Use SQL Authentication whenever setup asks and gives option to select between SQL/Windows Authentiation. Add Windows login of MR User to SQL Server, and provide server roles as SysAdmin and Public.

Once you done with the installation you need to login windows with MR user and add other Windows user to use the application. Open Designer to add users.

Install Latest Feature Pack, update as the way you installed that is first update Server then Client and finally the data provider.

Some Errors,

ERROR 25000 -- > SQL SERVER COMPACT 3.5 SP2 is not installed or not properly installed, make sure you install both 32 and 64 bit version of the component on 64 Bit OS.

If MR Report Queue stucks, 
  • Restart Management Reporter Service > Start > Run > Services.msc > Restart Management Reporter Service
  • Restart IIS > Start > Run > CMD > (TYPE) IISRESET
  • Recycle Management Reporter Application pool in IIS
  • Restart Management Reporter Website in IIS
  • Restart Management Reporter Application Pool is IIS.

Scheduling Reports on FRx 6.7 through Email

With the help Microsoft FRx 6.7, you can schedule reports and send them via email to executives and managers. Managers may setup their own requirements and get reports on time rather then asking for reports to their sub ordinates. I assume that FRx 6.7 is already installed with latest service pack. Fabrikam reports will be used to schedule the reports. Microsoft Outlook 2010 need to be configured and setup as default Email Sender to send reports from FRx.

Follow these steps to get it done,

Install Report Server from FRx CD


Once it is installed, restart the computer.

Run the report server, to check it runs perfectly


Run FRx 6.7 Report Designer and click tabs > OUTPUT > EMAIL OPTIONs, add the required email, subject and Message options, SAVE THE REPORT.



To schedule the Report, Click Schedule Report button on Catalog.


Click ADD on Schedule page, to add reports click Catalog Selection. Set the interval and all other necessary information and click SAVE.



Report is perfectly scheduled.