Using Management Reporter with Dynamics AX? Or maybe thinking about it?
This is a quick post on a few things you might want to know.
Much of this information depends on what version of AX you’re running. That’s always my first question when talking to a Management Reporter prospect who’s running AX.
If you’re running AX 2009, Management Reporter (today it’s version 2) links directly to the database and pulls live numbers. It slows way down when you add reporting trees, though. One thing you can try is using dimension sets…this seems to help speed things up.
AX 2012. Sigh. What can I say? There are issues with report processing time for the current version of MR on AX 2012. The solution appears to be in the next version of MR, Management Reporter 2012.
Management Reporter 2012 is almost here. The architecture is different (it’s an ‘almost-live’ datamart) and there are huge performance improvements. Here’s a blog post from the CPM team with more information on the MR 2012 datamart.
BTW, the MR 2012 datamart will be available for AX 2012 and NAV 2009 R2.
Looks like existing AX 2012 clients will be able to choose between the live connection and the datamart. Which to choose?
Well there’s no substitute for your due diligence, but here’re a few fun facts. The AX 2012 GL has changed substantially from AX 2009. It doesn’t track summary GL balances. It no longer has a single transaction table. Instead it has 5 separate tables and they all have to be joined in order to get a number to add to the beginning balance in order to get a balance in an account. This is what MR is doing behind the scenes, and it’s why it’s so slow on AX 2012.
The datamart changes things significantly since MR will pull already calculated numbers from the datamart (which, in case you were wondering, is simply an SQL database).
AX and Attributes
Enough architecture! There’s one more thing I want to mention about the MR user interface for AX. This applies to both AX 2009 and 2012.
The MR user interface is pretty much the same across all product lines—GP, AX, SL, NAV. I say ‘pretty much’ because the Attributes it pulls will vary. (For instance, the GP Attributes will pull vendor id and vendor name.) This is a list of Attributes that AX can pull into an ATTR column:
These ATTR columns are used when drilling down to transaction detail. So your Detail Level in the Report Definition has to be set to some form of Transaction. Careful—this also slows down processing time.
Attributes can also be found in the Row Modifier column (notice these attributes are the same as above) in the row definition, so that you can use these on a single row instead of an entire column:
BTW, these 2 screenshots are from my latest training manual Creating Reports in Management Reporter II—Step by Step Instructions on a Dozen More Reports. There’s also more information at this post on FRx transaction detail drilldown (even though it’s FRx…just replace the T codes with ATTR and one of the options).
That’s it for now—here’s to faster and better AX reporting! Cheers…Jan
Hi Jan,
Great information, thanks. I have a question if you have a minute. I am having an issue with MR & AX 2009. If I create the closing transactions, my ytd is showing a zero balance for my YTD column for my balance sheet. I know Microsoft’s advice is not close the year and create opening and closing balances. I need to close the year on AX because of the standard AX reports. How do you recommend I handle this issue?
I have tons of AX clients on Management Reporter; I have seen one instance in which a periodic beginning balance for period 1 didn’t tie to the ytd beginning balance. They reran AX closing and unchecked the box to create closing entries. So you might experiment.
XAD in the column restrictions cell of the column def will exclude the closing entries. Good luck…Jan
Ever run into an issue where a specific JE posts to Ax2012 but does NOT get updated to the datamart? I’m looking at the FACT table and for one period, May 2012 I dont see the JE’s which appear in AX…If I did a 2nd entry for May (which I then reverse) it does get posted to the DM.Fact…Any thoughts?
Steve
Yes I’ve seen data in AX not make it to the datamart, although I haven’t looked at the tables and don’t know when, why, where, or how. Microsoft provided scripts to correct so it is fixed, but I think about it every single day. If you find out more please let us all know! Jan
FYI everyone, here’s what Microsoft suggests: try the latest Management Reporter 2012 code. It’s Rollup 2 posted on or around July 31, 2012. Jan
All, I have also experienced missing transactions in Management Reporter even after Rollup 2 was installed. I discovered that restarting SQL Server can cause this condition. After I stopped & restarted both Management Reporter services, the data mart was back in sync.
Thank you Jim!
I am having an issue with Management Reporter 2012 not picking up various journal entries. ie if I know a line item on the income statement to be one amount (by way of the AX Trial Balance), MR will show a lower amount. When I drill into AX from MR I can see the posted AX transaction that MR is missing.
Hi Judy…you might review the above 2 or 3 comments and give that a shot. As I mentioned in an email to you from the airport last night, you may need to open a ticket with MS. If you get it figured out, please post. Cheers…Jan
RU3 was released yesterday — might want to give that a try…We’ve been told that it’s going to fix some of the data mart issues regarding missing data…
Hi Jan,
I tried the latest Management Reporter 2012 code – Rollup 2. I also restarted SQL server and Management Reporter services. I am now able to view all transactions from Management Reporter. Thank you.
Thank you Steve and Judy!
Thanks for this post. The article and comments below were helpful. I am curious about one thing you mention in the article: “For instance, the GP Attributes will pull vendor id and vendor name.”
Does this mean I will not be able to put the vendor name on my MR reports in AX2012?
Hi John…yes you’re correct. MR will only hit GL table(s), and somehow the GP programmers added this information to their GL, but AX 2012 is all normalized so its not there. One of these days we’ll be able to pull something other than GL, but for now, no go for AX. Cheers…Jan
Thank you Jan…the clarification is appreciated!
Hi Jan,
Just wondering, as the GL data gets built into the data mart and eventually at some point the database will become very large. How does data get purged from the data mart? Are there automated routines that gets created to manage this? Thanks!
Hi Jeff…well I always thank my lucky stars that I don’t do installations, so I don’t know the answer to your question. I have a guess, though: guessing the database can be extremely large since it’s sql based, and I’m also guessing there are scripts that will allow a rebuild from scratch in which you can specify the number of years or periods that you want included. But if you want to be certain, you should ask someone else :)
Have an issue at client site, using MR 2012 with AX 2009. Running MR reports with ‘Detail Level’ = ‘Account’, in Row Definition, row is defined by the Financial Dims in this case BU and CC, not the ‘Account’, MR produces a ‘Financial’ level report and user must ‘drill-down’ to see the ‘Account’ level report. How can I produce just the ‘Account’ level report?
Hi Greg…I haven’t tested just the Account level, but if it’s anything like FRx (and it IS), then you’ll always have to drill down to get there. So the solution is to make the row detailed enough that it gives you the look you want at the Financial level. Cheers—Jan
At Convergence in New Orleans last week, I talked with tech support about these datamart problems. AX 2012 with MR 2012 Rollup 4 is supposed to be SOLID. Got feedback?
I Jan. I see your post above about acct categories in ax. I can actually see acct categories being available in both row modifier,just like from your screenshot above and also available in the link to financial column from the row def.. What is the recommended way of placing acct categs? Via row modier
Hi Jeff…the attributes used in the row modifier will filter that one row for the attribute value you choose, meaning that you’d fill out the ‘From’ and ‘To’ parts of that window. Most of the time you’ll see the attribute in the column. That will give you a column that lists every value. It’s not a filter like the row modifier is. HTH! Jan
Hi Jan, thank you. But what i mean is that Main account categories in AX appear to be available and can be selected in the Attribute in the Row modifier and also in the Link to financial dimension. so ideally if in the link to financial dimension, it looks like u can combine acct categ and dimension. I want to use account categories from AX, where do u recommend that this be set/used? in Attribute in row modifier or link to financial dimension column from Row definition? thanks in advance! cheers
Aha, with you now. To pull data from the gl you use link to finl dimensions. The row modifier doesn’t pull data, it only filters it and is blank on most reports. Jan
Hi
I have a query. We defined rows by using ranges for main accounts. When we create a new account in AX, it does not get reflected in MR automatically. Is there some way to do that?
Hi Akanksha…sounds like if your range in the row includes the new account, it ought to pick up. First thing that comes to mind is maybe it’s the datamart that’s in use and it hasn’t refreshed yet. It’s described as an ‘almost live’ datamart, so if it doesn’t appear very quickly, I’d discuss with Microsoft to see what’s up and how to avoid in the future. Please feel free to comment again here if you find a solution. Jan
Hi Jan…thanks for your response. I wanted to know is it a feature of MR to add a new account row automatically in the row definition of a report, if the range is defined for it. Suppose a defined a range for a row definition from “edit” button and later on I add an account to that range. Is it supposed to create a new row for that account automatically in that row definition?
Thanks again
If that feature is available, I’ll check for datamart issues then.
MR doesn’t automatically add any rows to any row definition. But if your row definition defines a range, such as 45000 to 45888, and then you add account 45100 in AX, then no edit is needed because it’s inside the range defined. But if instead you have a separate row for each account between 45000 and 45888, then you’d definitely need to add 45100. Hope that helps.
Thanks Jan. I’ll check on this.
Hi Jan
I have been trying to use the range of accounts. But the problem is at account level when i generate a report, it gives me the name of all the dimension values in the Account name. What can I do to get just the Account number in my generated report?
Hi jan,
I wanted training materials on Management Reporter AX 2012. I would like to purchase.
Regards
Harsha
Hi Akanksha…I know what you mean! What you’re asking for is Subtotals. Meaning you want to subtotal by just the main account number, and have just its description show up. Unfortunately that’s not possible at this time. But you can ‘vote’ on the Connect site and ask Microsoft to add this feature. There’s more information in my post Beer and View Subtotals. Every vote helps keep this missing feature in their line of sight! Cheers…Jan
PS. I also have a post on how to create a pivot table from an MR report as an interim measure.
Hi Harsha…sounds great; I’ve written two self-study training manuals on Management Reporter 2012. There’s more information here, as well as links to the shopping cart:
Creating Reports in Management Reporter I—How to Create 4 Foundation Reports $180
Creating Reports in Management Reporter II—Step by Step Instructions on a Dozen More Reports $185
Just one thing to clarify: the Management Reporter user interface is the same whether the ERP is AX or GP or SL or NAV. My manuals use the GP sample data. Sometimes I do use AX screenshots, though. Like Attributes for instance. They’re different in AX than they are in GP. So I’ve added screenshots and comments for AX attributes.
Thanks for the comment! Jan
Just released is CU6 for MR 2012. New vendor, customer and audit attributes have been added for AX. What great news!
Hi Jan,
Do you happen to know if Management Reporter can pull in a custom field from Dynamics AX? Maybe using Attributes? or MR just pulls from AX standard financial field in the tables?
We are looking at adding a custom field in budget register and need to have it appear in MR? Appreciate if you have any idea? Thanks in advance. Cheers
Jeff
The latest release, called CU6 (not RU6), adds some new vendor and customer and audit attributes. But a custom field in budget register—guessing that’s highly unlikely. Couldn’t hurt to test though.
Thanks Jan
We have just installed MS Dynamics AX with MR 2012.
We have 12 different companies and 2 different acounting calendars.
3 of the companies all book codes in the FD column types are available in MR.(All on the same accounting calendar)
9 of the companies NONE of the budget book codes in the FD column types are available in MR.(All on the other accounting calendar).
Any thoughts?
Hi John…if you look at the middle-bottom-ish of the MR window, you’ll see the company code for the company you’re logged into. Close all the building blocks and log into one of the companies that has the different calendar. I bet that will do it. BTW it’d be great to use the same book codes in all companies so you don’t have to fool with maintaining different column definitions for the two different calendars. Good luck! Jan
Thank you, one last question. I can now see the oldest budget book code (2011) but not 2012 & 2013.
That sounds as if the 2012 and 2013 transactions aren’t in the datamart yet. IT should know how to fix. Hope that’s it.
Hi Jan,
I am using MR with AX2012.
When I try to open a row, column, or reporting tree definition I get the message ‘The operation could not be completed due to a problem in the data provider framework’. I have done research on line which seems to suggest it is an currency set up issue but my AX setups are in place. Any ideas?
Thank you in advance. Michelle
I mentioned the other night in an email to Michelle that this could be lots of things, and that the fastest way to resolve would be to open an incident with microsoft tech support. Cheers! Jan
Hi Jan,
we are running AX2012 R1 against MR2012. during the day we are having very different response times. In the morning we can have a report done in 5 min later on running the same report can take 1-2 hours. Have you any experience related to performance issues? Cheers
Soren
Hi Soren…this makes me wonder what else is happening on the server when it goes 1-2 hours. Also wonder if MR is on a server by itself. I don’t do any installation, so I think your best bet may be to talk directly to microsoft tech support. Good luck! Jan