Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012?
This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table.
Basically the overview line and each exploded line of the same item is stored in the same [FORECASTSALES] table. Therefore, if any measure in this table needs to be aggregated per item, filter out just the overview rows. You can do this by filtering the column [EXPANDID] = 0.
Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here.
In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped by how assigning different roles to users in Management Reporter does not quite turn out as they had expected. Both times I felt I could only give a partial answer. Thus, I decided to do a little experiment to find out the truth once and for all… or until Microsoft comes up with the next Rollup I suppose!
In this topic, I will cover:
Management Reporter is able to integrate with different flavours of Microsoft Dynamics but in this topic I will cover integration with Microsoft Dynamics AX 2012 only, and only for up to RU5, simply because that is what is available to me for my experiment!
It is a collaborative and interactive reporting solution from Microsoft for finance data.
View more here:
Management Reporter 2012 Overview
How is it different from standard SSRS?
It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to share via lync or sharepoint, and to insert comments at defined points of the report.
The other massively useful function is the ability to drillthrough all the way to the Dynamics AX entry.
The other functions I think which are useful are:
- Multilanguage support
- NO IT involvement required once setup
- auto-incremental update of data (NO ETL required)
- Multisource consolidation
- Integration with Dynamics AX
The documentation for Management Reporter is available now on Technet.
Microsoft SQL Server 2014 is not currently supported.
Minimum database requirements:
- Microsoft SQL Server® 2005 Express Edition with Service Pack 4
- Microsoft SQL Server 2008 or newer is required for the data mart database, system database, and the company database for the ERP.
I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business.
Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to support BI in an AX environment.
SSRS is now the primary reporting platform for Microsoft Dynamics AX since the 2012 incarnation.
Here’s the quote from Books On Line:
Microsoft SQL Server Reporting Services is now the primary reporting platform for Microsoft Dynamics AX. The default, preconfigured reports that are included with Microsoft Dynamics AX run on the Reporting Services platform.
Good news is that everything you know about SSRS can be applied into the world of Dynamics AX now. All the flexibility of being able to tweak every little detail is available to the report.
On the other hand, it would be useful for AX developers to know more about the SSRS development environment which includes:
- SSRS services provides two URLs – one for the Report Manager, and one for web services
- Administrator rights is required for deployment of reports
- Report Security has to be defined in Report Manager. A new security role is available for this. It is called: DynamicsAXBrowser
For SSRS developers, it is useful to know the following:
- The No. 1 rule is not to edit the report without saving the project back to the Application Object Tree (AOT). This is because if the AOT is rebuilt, all changes will be wiped out.
- To create a new report, create a new query in the AX Development Workspace first before creating a new report.
- Create a new report in Visual Studio 2010 and use the new Microsoft Dynamics AX Template called Report Model.
- Creating a new report this way requires a whole different process than a normal SSRS report. (Which I will discuss in a separate post)
- You can find out the SSRS url either from the AX Development Workspace (Tools -> BI Tools -> Report servers, or the usual way – via SSRS Configuration Manager.
- Save the report project into AOT.
- Then deploy the reports. There are two ways – via Visual Studio (which provides more output messages which is useful for troubleshooting, or via the AX Development Workspace)
For more information of SSRS in Microsoft Dynamics AX 2012, visit: http://technet.microsoft.com/en-us/library/dd309644.aspx