Dynamics AX
  RSS Feed  LinkedIn  Twitter
Want to turn you're data into a true asset? Ready to break free from the report factory?
Ready to gain true insights that are action focused for truly data informed decisions?
Want to do all of this across mutliple companies, instances of Dynamics and your other investments?
Hillstar Business Intelligence is the answer then! (www.HillstarBI.com)

Hillstar Business Intelligence for Microsoft Dynamics AX and NAV on Mobile, Desktop, Tablet

Let us prove to you how we can take the complexity out of the schema and truly enable users to answer the needed questions to run your business! Visit Hillstar Business Solutions at: www.HillstarBI.com

Tuesday, June 23, 2009

Dynamics AX 2009 - Reporting Possibilites

So we have talked about BI in the past few months. Went over several Microsoft tehnologies that make up the Microsoft BI platform. This includes SQL Server, Reporting Services, Analysis Services, Integration Services.

This is all the BI platform from Microsoft. And we now have started to look at how this BI platform fits in with and can be used inside Dynamics AX 2009.

The first dive into this was a pratical use for creating custom SSRS reports for a Dynamics AX 2009 instance, and looking into OLAP use for Dynamics AX 2009. (Link: Dynamics AX 2009 BI: Practical use with SSRS and looking at OLAP)

So with that we saw with Dynamics AX 2009, there is a new Visual Studio 2008, Project Template for Dynamics AX 2009 SSRS custom reports.

With showing this, and coming back around to all that we have covered with this so far, I think we are at a point to where we should look at the higher level of reporting options inside Dynamics AX, and get some higher level pros and cons about them.

So with that said, lets look at the reporting options for a Dynamics AX 2009 instance.

The point of reporting is to gain access to data that lives inside a System and it's database. This is the point of reporting with Dynamics AX 2009. To give users access to the different data they may need to perform functions, processes and help make business decisions.

So the reporting options with Dynamics AX addresses a spectrum of reporting. You can look at this as having Procedural based reporting on one end of the spectrum and Analytical reproting at the other end.

On this spectrum, there is a few different ways that reports can be created that span the above mention spectrum. The following is a high level list of these reporting options.:

Standard MorphX reports
The standard MorphX reports are the base reports that come inside Dynamics AX. Most of these are very procedural based in their nature. They are meant to delivery things like invoice journal data, Sales Order Confirmation, Invoices, etc. They serve a purpose to help fulfill a business process.

These kind of reports have the most flexibility for working with business logic, and Dynamics AX objects. They are also the most difficult to develop and modify at times, and are the older way of reporting inside Dynamics AX. (They have been around since the early Axapta days.)

You use X++, and MorphX to create, modify and work with these reports. End users can make use of custom queries and filter possibilities that can stay with that user for as long as their usage data is kept.

So this offers the most flexibility of any of the reporting options for getting at Data inside Dynamics AX. These are very developer driven reports, and super users would not be creating these.

Though this is mostly procedural based reporting, some analyical reports exists in this form, though not many.

Custom SQL Server Reporting Services (SSRS) reports
This reporting option, though could be done to some degree in earlier versions, is really just now a true offering in Dynamics AX 2009.

This makes use of a easier, Visual Studio driven development model, that .Net developer would find familiar to work with. This process creates report definition files, that are used and connect through the .Net Business Conector to work with Dynamics AX data and business logic.

The business logic can be access through using the AxaptaWrapper .Net class, that comes from making use of the .Net Business Connector and the Dynamics AX .Net Reporting Framework.

This is not as complex to create, makes use of a report designer that is easy to use, drag and drop possibility, and real flexible in design and layout possibilities.

This also executes on a SSRS server, which can offload some of the report generation from the AOS itself.

You don't have as much flexibility with this option as a standard report, for example the custom Qeuries for filtering a report, that are user driven can not be used. The developer of these custom SSRS reports must give access to filtering through the report design.

This option offers the ability to create procedural based or analyical based reports in nature. OLAP cubes, KPI's, etc. can be access and added as sources of information for building reports here.

Report Builder / Ad-Hoc Report Creation
This option first came to exists in DAX 4.0, and has been improved in Dynamcis AX 2009.

This makes use of the perspectives node inside the AOT, to expose report models with to the Report Server. This option is targeted at super users who want to get at report data quickly, and in an ad-hoc nature.

This makes use of the SQL Server Reporting Services - Report Builder tool, and based on your instance and the deployed perspectives, will govern what report models your super users can access to look and report on data.

This is very much a drag and drop ability, and meant to be a design so the super users don't have to know the data model.

This is the most limited of the reporting option, and that understandable as this is Ad-hoc, and not meant to give all access to everything. The biggest limit is access to the X++ calculated fields inside Dynamics AX. Those fields that are not just a single table field, but calculated depending on a number of different variables.

The point of this though is to get super user the ability to gain access to around 80% of the reporting data needed. If more is needed, then they can get with thier IT staff to deliver a custom SSRS report for the full amount of data.

This reporting option, like the one before, can offer Procedural and Analytical in nature reports. Access to Cube and Procedural perspectives allow access to different data, and the different data and relations that might exists.

SQL Server BI Development Studio (BIDS)
This is the out of the box tool that most standard Microsoft BI projects use to create reports with. This is also used to create and manage OLAP cubes and OLAP database, and create and managed SSIS packages and logic.

For the report creation this is direct access to the SQL Server database, and now interaction with Dynamics AX itself. So that is the biggest Con with using this to create reports with. No access to those, sometimes much needed X++ calculated business logic fields.

If you choose this option, and need those fields, you would have to 're-create the wheel' and duplicate that logic through T-SQL or SQL Scalar Functions.

This can be very Procedural or Analytical, depending on what your doing with BIDS. Also, BIDS is used, even if not for report creation, to manage a Dynamics AX 2009 instance OLAP cubes. To look at them, modify them, trouble shoot them, etc.

Well that's all for now. I think this is a good start to the reporting options inside Dynamics AX 20009 instance. Moving forward we will dive deeper into looking at each of these, getting some screent shots and real world examples.

See you soon!

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , ,


Blogger Mandeep said...

Hi Brandon,
Thanks for the valuable imformation on your blog .

Please suggest me regarding the Journal Import process in AX2009 . Plan is to import CSV file( through Defination Groups) for the General Journal lines ( table : LedgerJournalTrans ) though a AX Batch process .

1. When I manually import any excel file then the Status of the Defination group changes to 'Imported in Total' .So every time , User has to import files he has to manually change the status to 'Import' which is not practical in real scenario where same file has to be imported 2-4 times in a day . So what is the best way to handle this.
2. When in the defination group the type is 'Excel' or 'Standard' , the option to define Batch defination comes but in case of 'Custom'( for csv file) , the Batch option does not come . How to set batch for the import of CSV files?
3.For csv file import , does the field set up(under defination group),sequence for active fields can be modified as per the import file format or the import file should have the sequence as per AX field set up ?
I believe that for auto creation of Journal header number has to be coded in the defination group so that every time the file is imported the Journal header is creeated with the Ax number sequence .

Please suggest .

Thanks and Regards ,

9:12 PM  
Anonymous Mahmoud Anass said...

Hi Brandom,
Inspite the huge capablities of SSRS but it lacks too much of integrations feature between it and AX, too many features included in Standard report (MorphX reports) I cannot implement in SSRS, I will list some that are in my memeory for right now, for example, how to pass args to SSRS, how to get use of or display methods in SSRS.
I don't know if Microsoft have future road map for more integrations between SSRS and AX, inspite all the Microsoft sites and experts are recommending using of SSRS instead of Standard reports. I hope Microsoft can clarify this point cleary!!!

All the best

Mahmoud Anass

2:40 AM  
Blogger brandon said...

Mahmoud Anass,

I plan on taking your request here, and running with this. To show how this can be used, get into passing parameters, and talk about the future with SSRS.


10:53 AM  
Anonymous axstart said...

As you know perspectives are not updated for ax2009 , especially with Report Builder 2.0 a clean customer understandable solution is not working any more. Report Builder 2.0 also works fine on sql 2005. The possibilities of Report Builder 2.0 are great and the need of visual studio is minimized.

Hopefully MS will solve this in AX 6

8:47 AM  
Blogger sudhir Reddy Jangam said...


I have AX 5.0 RTM and then installed SP1 also.
Few days back I have installed reporting tools and extensions and all reports are deployed under Report manager. (SSRS is already installed on my machine)
But I am not able to see Dynamic Report project template in visual studio 2008 and 2010 as well.
Please guide me how to resolve this issue.

7:59 AM  
Anonymous ankita said...

Please help me out in finding the solution to my problem. This is very
I am creating a AX 2009 SSRS report. In the report I have two
parameters, based on the first parameter which is a drop down , the
parameter needs to be filtered with specific values. For eg AccountNum
is the
first parameter which displays all the CustomerId. Based on the
AccountNum selected, the second parameter should display the
AppointmentNumber. Can you
let me know how to achieve this scenario. Is this scenario possible in
2009 SSRS?
Thanks & regards,

12:01 AM  

Post a Comment

<< Home

Copyright 2005-2011, J. Brandon George - All rights Reserved