Build an AX 2012 SRS report based on cube dimensions and measures


They are lots of really good resources dedicated to building SRS reports for AX 2012 using data providers and queries for transaction reporting.   I searched and searched and was never able to find any resource that could demonstrate to me how to do the analytical reporting based on the OLAP database.

This is not for newbies even though what I’ll show you is easy, if you are new to Dynamics and reporting I would highly recommend the series of videos by Saveen Reddy – this is how I started 4 years ago.

The MDX language is an art all of its own and being able to write these queries was always a barrier to producing OLAP reports.  What I didn’t know is that you do not need to know anything about MDX!  But you do need to be able to work with the SQL SAS instance and Visual Studio.  I will demonstrate all the steps to build your first SRS report using an MDX query.


  • Fully processed and updated OLAP database, steps to do this are here.

So what report shall we build?  Something very simple – Customer invoice amount per year.  I’ll ignore the Company Id but you will need to parameterise it later, what we want is our first MDX report.

Open Visual Studio and create a project and report framework as you have done many times before.VSReportStart

Add a dataset and give it a good name.

Set the Data Source property to DynamicsAXOLAP.  Now we need an MDX query.  I will show you how to get this in Visual Studio but you can also do it on the OLAP database as well.

Open a new instance of Visual Studio and select File > Open > Analysis Services Database…

Open database

Browse to the SAS Instance and wait for it to open.  Our report will be on the Sales cube so lets open that cube.  On the browser tab drag the measure and dimension you want.  This is the result for me.

OLAP report

We will use the MDX statement created here and embed that into our SRS report.  Switch the designer to show the MDX code and copy it.MDX

On the dataset of the report paste the MDX

SRS MDXNow we can create the report using what ever style you like.  I will build a Bar Chart.  Change the Default Layout on the Dataset from Table to BarChart and drag it onto the Designs node to make your AutoReport.

Set the Layout Template to “RoleCenterReportLayout” and change the AutoReport name to BarChart.

Set the Style template to BarChartStyleTemplate and change the chart sizing to suit.

Change the display labels as the MDX default is not a good look and you should have something like this.


Once you are happy with the labels formatting titles etc deploy the report.  Create a menu item in the AOT and open it to run the report.


MDX report

Now you are ready for something a little more complicated!


Connect an existing Financial Dimension to a measure group

Working with the AX 2012 OLAP is a much easier task then previous releases, there are a lot of moving parts but it can be quite simple.  I will be demonstrating how to configure the cubes by adding an existing financial dimension to an existing measure group.  In the Sales Cube there is a new measure group for AX 2012 R3 – Customer Invoice Lines – this measure group has no connection to the Financial Dimensions.  So lets hook it up.

If you have trouble deploying and processing the standard cubes to get the dimensions in then follow my previous post on this topic.

First lets see what happens when we try to browse and this is typical when a dimension is not hooked up to the measure group.


If we import the OLAP database into Visual Studio you will see that this measure group has no financial dimension and any attempt to add it will fail.


All the work is done in an AX development environment and moved via the model store into Live as per any other change, once in Live you just need to deploy it.  Now find which table or view is creating this measure group, in my example it is the SalesCube and the CustInvoiceTransExpanded View.

Once found open the AOT Perspectives, I have picked the LineAmountMST to show you the reference to the measure I used previously, Customer invoice line amount – accounting currency, from the properties you can see this is a measure.


You will see from the fields selected that the DefaultDimension field is not included, without this field no Financial dimension analysis is available.  Go to the View and add the DefaultDimension field from the query, you do not need to modify the query as this field is already there.  Take the defaultDimension field from the transaction lines table and not the header table.  The customer invoices measure group already has the dimensions hooked up, we are looking at the invoice lines for our analysis.

Restore the SalesCube perspective and the DefaultDimension appears in the field list.  Now open the Analysis services wizard and select the update option, just click next all the way through, pause at the Confirm changes option so you can see the magic.

Confirm changes

Process the OLAP in Visual Studio or SQL and then browse the Sales cube to see the result!

Corrected report