Compelling Analytics: SQL Server Reporting Services in Dynamics AX 2009

转载：http://blogs.msdn.com/saveenr/archive/2008/09/06/compelling-analytics-sql-server-reporting-services-in-dynamics-ax-2009.aspx

Compelling Analytics: SQL Server Reporting Services in Dynamics AX 2009

In my role as Lead Program Manger for Business Intelligence in the Microsoft Dynamics AX team, one of my architectural goals is to help AX developers exploit the power of Microsoft's BI stack.

With regard to BI stacks they have a reporting component and Dynamics AX has its own reporting engine (MorphX) and in Dynamics AX 2009 we've added support and are integrating with SQL Server Reporting Services (SSRS).  From a strategic point of view this is important: Over time we want to not only work with it, but also increasingly use it in Dynamics. There's an huge team of talented people working on SSRS and it represents Microsoft's reporting story. By increasingly adopting this stack we leverage their expertise and efforts and simultaneously get more time back to focus on adding features to Dynamics AX.

So, from the perspective of code and managing resources (people and time and money) this makes sense. What I'll address in this post is to help make absolutely clear why this is a REALLY GOOD THING FOR USERS.

MORPHX reporting - the native reporting engine in AX

First, let's take a look at a report built with MorphX in Dynamics AX 2009.

NOTE: These screenshots use SQL 2005. Dynamics AX 2009 supports both SQL 2005 and the recently-released SQL 2008 but my demo machine only has 2005 installed on it.

We start with the report called "Divided Trial Balance" in the "General Ledger".

Clicking on "Divided Trial Balance" will launch this dialog to set parameters for the report

Select the From and To dates ...

Click OK. The MorphX report is shown

NOTE: I realize this is a bit hard read because the report is sized to fit the screen. Time for that 30" monitor.

AND NOW THE SSRS EQUIVALENT

If you paid close attention to the list of reports in General Ledger you may have noticed there were two reports called "Divided Trial Balance". One of them had an asterisk by the name.

In an out-of-the-box install of Dynamics AX 2009, reports built with SSRS are marked with the asterisk so that they are easy to find.

Let's click on it. This window launches.

It looks a bit different from the MorphX version but there are some common elements.

Set the From and To dates.

And then click View Report. And you'll see this:

So these reports look roughly equivalent (which was deliberate).

And you can see some features of SSRS in the export section of the header.

AX ROLE CENTERS AND SSRS

AX 2009 Introduced Role Centers (a.k.a. Dashboards) for about different 30 user personas. Role Center's heavily use SSRS.

Let us look at a Role Center (by navigating to the Home tab).

My user account is set to be the CEO of the company in the AX database, so I see the "CEO Role Center" which I click Home.

NOTE: I don't have all the correct data in my AX demo database, so some of the numbers don't make sense in these screenshots (especially for the KPIs and Indicators)

Everything bounded by the red box below is implemented as a SharePoint web page.

And everything you see highlighted by the red boxes below are actually SSRS reports (also web pages) embedded in the Role Center web page.

Role Centers are a key feature of Dynamics AX 2009 and SSRS is critical for them to work. If you don't use SSRS then you won't achieve the value we put into the product.

To make this more obvious - here's what happens if you don't have SSRS running.

Again: you need to be using SSRS with Dynamics AX 2009.

Now, let's take a look at some of the other role centers. It should be obvious where SSRS is being used.

Bookkeeper role center

The Operation Manager role center

The Production Manager role center

Shipping and Receiving role center

At this point what you've seen is that SSRS gives us some charting abilities which were not possible with MorphX.

THE PAYOFF - THE FUTURE WITH SSRS LOOKS GREAT

But, where can you take SSRS technology? How much better can these reports be because of SSRS? Because we didn't spend a much time making the reports "slick" in AX2009 it's sometimes hard to see the value. Fortunately, I know exactly how to show you were we want to go with SSRS and what is possible TODAY.

Here are real-world examples from my previous team, Microsoft Forefront Client Security. This product was released in 2007 and uses plain SSRS 2005 with no Dundas Charts or special activex controls. Everything you see is possible with "what's in the box".

And let's take a close up view on the Security Summary report.

Now, wouldn't you like that in some of your dashboards?

Or if you need more of the data look at the security state assessment summary report

I hope this post has made you more familiar with Dynamics AX and some of the new features in Dynamics AX 2009 and most importantly I hope you can see that we aren't interested in SSRS just because that's what are paying some "strategic tax" but rather there's some really great value in SSRS for AX customers.

Over time, we will increasingly adopt and integrate SSRS into AX. And in our next release after AX 209 I am planning on giving you some compelling analytics experiences both in terms of the insight they offer for BI as well as in terms of visual style and quality by fully exploiting the power of SSRS.