Tuesday, February 2, 2010

Using SQL Server Reporting Services(SSRS) with Microsoft Dynamics GP


In Dynamics GP we have a cool feature to have SQL server reporting services(SSRS) reports, Microsoft SQL Server Reporting Services (SSRS) offers a very powerful report writing capability for any SQL Server based system. Specifically for Microsoft Dynamics GP, Microsoft has created a library of over 200 pre-built reports to use.

In Order to access and leverage the SSRS Library that is provided by Microsoft and to upload it to your Dynamics GP system you need to perform some simple steps

Step 0 :        Prerequisites

-      SQL Server Reporting Services 2005 With SP2 Or Later

-      SQL Server Reporting Services 2008 With SP3 For SQL Server Reporting Services Wizard

-      IIS 6.0

-      Internet Explorer 7 or 6 with SP2

-      Dynamics GP 10.0 with Service Pack 1 or Later


Step 1 :        Install & Setup IIS

-      For Windows 2003

o    Click Start, point to Control Panel, and then click Add or Remove Programs, The Add or Remove Programs tool starts.
o    Click Add/Remove Windows Components, The Windows Components Wizard appears.
o    In the Windows Components list, click Application Server, but do not select the check box.
o    Click Details, and then click to select the Internet Information Services (IIS) check box.
o    For Further Details Please refer to windows manual.

-      For Windows 2008

o    Click Start -> All Programs -> Administrative Tools -> Server Manager.

o    In Server Manager, scroll down to Roles Summary, and then click on Add Roles. The Add Roles Wizard will start with a Before You Begin page. The wizard asks for verification of the following:
o    Select Web Server (IIS) on the Select Server Roles page. An introductory page will open with links for further information
o    Choose the IIS services to be installed on the Select Role Services page.
o    Click Next

-      For Windows XP

o    Click Start, point to Control Panel, and then click Add or Remove Programs, The Add or Remove Programs tool starts.
o    Click Add/Remove Windows Components, The Windows Components Wizard appears.
o    In the Windows Components list, Select the Internet Information Services (IIS) check box.

o    Click Next.

Step 2 :        Install & Setup SQL Server with Reporting Services

-      Install SQL Server 2005 / 2008 with SQL Reporting Services, refer to SQL server installation help for complete details.

-      After Installation Run SQL Server 2008 reporting service configuration manager. 

 

-      Select Server to connect.

-      Specify/setup/Apply proper Web services URL.

-      Specify/setup/Apply proper Report Manager URL.

Step 3 :        Install SQL Server Reporting Services For Dynamics GP

-      Run the Reporting Services setup wizard from GP CD2

-      Click Next On Welcome Screen.

-      Accept the license agreement.

-      Select Feature “U.S English Version”

-      Complete.

Step 4 :        Run Microsoft SQL Server Reporting Services Wizard

-      Click Start >> Programs >> Microsoft Dynamics >> Business Intelligence >> Microsoft SQL Server Reporting Services Wizard.

-      Click “Next” on welcome screen.

-      Specify “Dynamics GP Server Name” & “SQL User ID & Pass” on Connect to data source screen.

-      Select “Company” and specify Module against which you would like to deploy SRS standard reports on Select Database and module screen.

-      Specify “Target Server URL” , in define location & deploy report screen. This URL is report server URL. ( Refer Step 2 for URL ).


-      Click Finish.

Step 5 :        Setup SQL Reporting Service In Dynamics GP

-      Define the reports and web services path in “Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Reporting Tools Setup”:
-      Specify “Report Manager URL”. ( Refer step 2 for URL Details )

-      Specify “Report Server URL”. ( Refer step 2 for URL Details )

Step 6 :        Browse Standard SRS Report in Dynamics GP

-      Go to Administration tab and find “Custom Report List”:










Helping Links:

SSRS With Dynamics GP FAQ ( KB 954242 )

https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmyxupyzqpouxnxspsypwwwwonwumqpnovy&wa=wsignin1.0

Happy Reporting !

5 comments:

  1. Great Post...Lots of useful information found on your blog...Thanks again

    Software Development Company

    ReplyDelete
  2. Really a good information and very effective.

    Dynamics Hosting

    ReplyDelete
  3. Thanks For Your valuable posting, it was very informative. Am working in Erp Software Company In India

    ReplyDelete