Archive for April, 2010

Exposing SQL Reporting Services Reports in Great Plains

Images located in a gallery at the end of the post.

Recent versions of Microsoft Dynamics GP ship with several SQL Server Reporting Services reports.  If using SQL Server 2005/2008 and GP 10 (or GP 2010), these reports can be deployed and used.

Not too many clients know about or use these reports, probably because they were originally all on disk 2 of the 2 disk GP 10 install, or as an additional product on some of the newer installs. 

The installation of the canned reports is two step, and is not covered in this article to any depth. With that said, the process is fairly basic and involves installing a deployment wizard, and then running the wizard to install reports.

For a list of reports shipped, visit this previous blog entry:

Once deployed to the SSRS Report Manager Site, the reports all live within a pre-defined file (tree) structure. The Great Plains reports deploy to the root site folder by creating company subfolders (Image 1), and then several series subfolders (Image 2), and finally the reports (Image 3). 

Microsoft introduced reports lists in GP 10, which provides a common access point for all Great Plains reports.  If we look at a reports list, we will find that it contains a compatible organization, but by row-column instead of tree (Image 4).  Each report in the list view has the properties Report Name, Series, and Company (Called Category).  Consequently, there is a structural relationship between the GP Reports List functionality and the GP SSRS Reports.

To expose the canned SSRS reports within Great Plains, Microsoft has leveraged (or created) this relationship and added a configuration window to the Tools->Setup->System menu named “Report Tools Setup” (Image 5).  This window contains three tabs, and one of the tabs is called “SQL Reporting Services”.  There are only two configuration points on the tab: Report Manager and Report Server URLs.  A network/system administrator would know these values, but just in case you are curious, they should be some thing like:

Report Server:


Report Manager:

Enter the two configurable values for your environment, and click ‘Ok’.  That is enough to expose the canned reports in the “Custom Reports List” (ignoring security for the time being).  You know it is an SSRS report because the window footer will state the product as “SQL Reporting” (Image 6).  You could potentially take this report deployment one step further and save the reports to the My Reports List, and then they will be available on the Great Plains dashboard.

Double clicking on a report link in the Great Plains reports list will direct the user to that report on the report manager. The report will automatically run if it has default parameters.

Microsoft has added logic to tighten the relationship between the custom SSRS report list and user session. When a user accesses the custom reports list, the application filters the SSRS reports by current Great Plains session interid (i.e.-database name, or in the case of Fabrikam: TWO); all series reports in the correspondingly named folder will be listed (GP searches all series subfolders). Consequently, you will only see those reports that belong to the current company you are logged into, which is right in-line with Great Plains company access security.

Any client would immediately look at the canned SSRS reports and say, “Can you modify this to meet our requirements?”. The answer is “Yes”. Using standard SQL Reporting Services functionality, you can download the “out-of-the-box” SSRS report, add it to a Business Intelligence Studio project, edit the report, and then upload back to the site. This process does come with some report definition configuration, so it is not recommended for the novice.

This begs an additional question, can we expose completely custom SSRS reports to Great Plains?  Or, in other words, can we expose reports developed by in house developers, consultants, and anyone not named Microsoft? The answer is “Yes”.  All you have to do is deploy your custom report to one of the series subfolders created by the SSRS deployment wizard ( located on the Report Manager web site). 

For example, I created a custom vendor list for the TWO company.  I deployed it to my SSRS site at the location HOME->TWO->Purchasing->Vendor List.  Sure enough, it shows up in custom reports list with a name of “Vendor List”, series of “Purchasing”, and a Category of “TWO” (Image 7 and Image 8).  No additional configuration is needed beyond just deploying the report to the correct folder (assuming you have established the correct SSRS data source for TWO company). Even better, it is only visible when logged into the TWO company.

“It is a far, far better thing that I do, than I have ever done; it is a far, far better rest that I go to, than I have ever known.” – A Tale of Two Cities by Charles Dickens

Project Accounting Overview Videos

I just recorded some overview videos for Dynamics GP Project Accounting to provide an introduction to some of the scope and functionality of the system.  I covered these topics:

Project Accounting – T&M;  -  This video shows how to use Project Accounting in Dynamics GP to set up a simple T&M; project, enter timesheet information, and create invoices using a batch process.

Project Accounting – Fixed Fee  -  This video shows how to use Project Accounting in Dynamics GP to set up a simple Fixed Fee project, enter timesheet information, recognize revenue, and create invoices using a batch process.

Project Timesheet Entry in Business Portal  -  This video shows how to easily enter project timesheet information into Business Portal, use an approval process, and post directly to the Project Accounting module in Dynamics GP.

These are quick little demos that cover a lot of material.

Creating SmartLists using SmartList Builder and SQL Views

There are a number of GP bloggers that often include SQL Query Language as part of their posts.  This is a rich source of specific problem solvers.

SmartList Builder allows you to easily take advantage of these resources to provide data inquiry and reports.

These are the steps to use once you’ve found a SQL Query that would be useful:

  • Create the new SQL View using the SQL Query
  • Grant access to the new view
  • Create the SmartList using the SQL View

I created this video to show you a simple example of how to do it.

Victoria Yudin is one GP Blogger that regularly includes SQL Queries in her posts.  Check it out.

This is the specific post I referenced in the video.

Microsoft Dynamics GP upgrade thoughts

PictureI hear more and more buzz being thrown around about Dynamics GP  2010 all the time. I myself have installed, tested, created youtube video’s, participated in webinars, etc. so I guess I’ve just added to the general hysteria.

I was just trying to think back over the past several releases of Great Plains and remember if there was similar hype. I remember going to convergence during one of the major release announcements (could have been 7 or 7.5) and hearing MBS announce a great new version that is now available. Excited and surprised a bit, having to spend the whole convergence trying to find out about all the cool new features etc. I’m sure there will be those type of breakout sessions this time around in Atlanta but if you keep up with any of the Dynamics blogs, communities, and resources there is far less need to wonder what is being release.

I’m sure some of this is because of the following:

  • Downloading a GB or so of data is way more feasible now than in 2005
  • MBS used to send out CD’s when the software was released. That was often the first time partners and customers actually got to play with the new version
  • Bloggers, MVP’s, MBS employee’s etc. are able to disseminate great information to the Dynamics community where in the past the monthly email newsletter from your partner was about all the resources available
  • Youtube demo’s galore on Dynamics GP
  • Webinars from MBS and your local partners regarding what’s coming out new
  • Beta testers used to be the risky early adopters. Now they seem a dime a dozen. ha.

So does all of the above make upgrading less of an unknown? Sure.

Does that mean you should run out and upgrade as soon as the RTM release is finalized? NO WAY.

I recommend waiting at least one full service pack before even considering upgrading. I’m sure there are lots of differing opinions on this subject but to me it’s just not worth the pain and agony that occur with the first version no service pack release of any software.

Should 8.0 and before clients upgrade to 10.0 at this point?

So if you are on an older version of GP (8.0 and before) should you be waiting until Aug/Sept timeframe for the first SP of Dynamics 2010 to be released? (that’s 5 or 6 more months) I guess it depends if you are OK with the risk of running without MBS support for that long. (Obviously if you are still on 8.0 or before this isn’t that big of an issue for you.)

My general thoughts are at this point, wait for the first SP of Dynamics 2010. You can skip some further pain that’s caused by unexpected bugs in waiting for the first SP and you can still have a good 4 or 5 years of use from the system. This is assuming you will use 2010 until very last legs of the product mid way through 2015. But you really should be upgrading at some point as I suggest in my To upgrade or not to upgrade post.

Technorati Tags:

Integration Manager and ODBC

Integration Manager (IM) is a common choice for integrating data into Microsoft Dynamics GP. IM has several hooks into the Great Plains modules include the Financial, Payables, Receivables, Sales Order Processing, Payoll, Inventory, Project Accounting (via eConnect), Fixed Assets (via eConnect), and can even be used to update exchange rates. The tool allows users to define named integrations, source locations, source relationships, and target field mappings.

Integration Manager’s general functionality is to take mapped data and validate it using Great Plains business logic. If the validation succeeds, IM creates the Great Plains records and uses system automation logic to perform subsequent calculations (like the sales tax engine or auto creation of general ledger distributions).

Integration Manager expects that the user be involved, and actually requires the push of a “Run” button to initiate the integration. There are no scheduling or service components to Integration Manager. IM is a great tool for small business integration, and is perfect when user involvement and manual data manipulation is acceptable.

Since the “IM Process” is manually driven, how can we make it easier for the user? Well, I believe one place to look is in the management of source locations. Most IM integrations are set up using the standard delimited text source (CSV, Pipe Delimited, etc..). Setting up integrations like this can be inconvenient for multi-record transactions, like Sales Order Processing (SOP Header, SOP Line). Each record type would have to be defined as a separate file in the integration, and saved as text delimited. This process can become quite combersome, especially when dealing with Excel to text conversion rules (try saving an Excel field as CSV with leading zeros in the field data…). Add on top of that fact that these integrations are regularly re-occurring, and this process can quickly become stale.

To get around some of these problems, we can use ODBC. IM provides two types of ODBC connection; I prefer to use the Advanced OBDC version. Let’s walk trough an excercise.

  1. Create an Excel file with two tabs – one for SOP Header and one for SOP Line information. Make sure the lines can at least be related to the header via document number. Save your Excel file to a network location. Name the tabs “Header” and “Line”.
  2. Go to Control Panel->Administrative Tools->Data Sources (ODBC). Create an Excel ODBC data source (System DSN) that connects to the file location – name it “IM Test”. You will need to click “Select Workbook” and then browse to the network location and point the Data Source to the Excel File saved in step 1.

  3. Start integration manager. Create a new integration called “Single File Test”.
  4. Add a source to the integration. When prompted for the Source Type, select “Advanced ODBC”. Click on the “Define New Advanced ODBC” node. This will open the source query dialog.

  5. Name your source query “SOPHDR”. In the data source drop down, select “IM Test”. This is the ODBC connection we set up in step 2. Paste the following statement into the SQL Statement box: SELECT * FROM [Header$]
  6. Click “Apply”. Check out the columns tab – notice that IM was able to read the columns from the Header worksheet.
  7. Repeats steps 4-6 for the Line tab in the Excel worksheet. Note that the SQL Statement will change to: SELECT * FROM [Line$]
  8. Now, configure your integration as you normally would by adding an SOP destination, query relationships between SOPHDR and SOPLINE, and destination mapping.
  9. Run the integration – we now are able to run an IM integration with multiple sources, all from the same Excel file.

Why is this useful? Well, a good Excel person could create a template or macro to manipulate the original source data into the correct integration format. They could save this template/macro somewhere to be re-used. Each time the integration needs to be run, the Excel template is opened, the source data is feed to it (copy and paste anyone?), and the manipulation is done via template/macro. We have not quite hit the value point: now just save the loaded template file to the ODBC network location…run the integration – all done! There is no more saving of multiple files to multiple locations with specific names. We have configured all that in our template (including tab names). Just one file, copied to one location.

I should not stop there; however, the text delimited IM approach we are used to using is actually just circumventing the Windows operating system ODBC setup. IM is actually using ODBC in all configurations. You could mimic the text delimited setup by using the operating system Data Sources/ODBC connection and IM Advanced ODBC setup.

We can make one more conclusion from this reasoning – you can use ANY ODBC connection for IM, including direct connections to SQL Server, Oracle, and Microsoft Access! Here is a sample of some of the ODBC connections provided by windows:

“All the world’s a stage, and all the men and women merely players: They have their exits and their entrances” – As You Like It by William Shakespeare

Subscription Options:
Subscribe via RSS
Articles Categories