How to use AX Attributes in MR to get lots of drilldown information about a transaction
There are tons of new attributes available for AX in the latest release of Management Reporter 2012 CU6.
[aka version 2.1.6041.36, aka Cumulative Update 6]
BTW, all these concepts hold true for GP and SL too. You’ll just see a different list of attributes.
These attributes will give you lots of information at the transaction level when you drill down. Here’s how to set it up.
Use the ATTR column type
In the column definition, set Column Type to ATTR.
That allows you to drop down the box in Book Code / Attribute Category and take a look at the attributes available.
Here I’ve populated 6 different ATTR columns with different attributes, the last two being Vendor and Customer (two that will prove to be quite popular).
The attributes that are available are only the ones that are populated in the data mart. So you may not see all of these. But here is at least a partial list:
- Account Category
- Account Currency
- Account Type
- Acknowledgement Date
- Active From
- Active To
- Amount Type
- Budget Model
- Budget Plan Name
- Budget Plan Priority
- Budget Plan Scenario
- Budget Plan Workflow Status
- Budget Planning Stage
- Budget Type
- Budget Class
- Carry Forward
- Comment
- Consolidation Account
- Created By
- Currency Code
- Customer Name
- Document
- Document Date
- Document Number
- Document Status
- Entry Number
- Group Dimension
- Journal Category
- Journal Description
- Journal Entry Date
- Journal Name
- Journal Number
- Last Modified By
- Last Modified Date
- Offset Account
- Parent Budget Plan
- Payment Reference
- Posting Type
- PostingLayer
- Price
- Quantity
- Reason Code
- Reason Comment
- Return of Budget Funds
- Reversal
- Reversing Entry
- Source Document
- Status
- Suspended
- Totaling
- Transaction Date
- Transaction Description
By the way, AutoFit in the Column Width cell is a great idea so you don’t have to fool around and guess at widths.
GP users may be scratching their heads and wondering what’s up. You guys have had Vendor and Customer available in GP all along. But AX…not so much. I could explain, in a semi-convoluted way, why they haven’t been available in AX—it has to do with a normalized database—but I’d rather move on. Suffice to say that the data mart is what’s probably making this possible for AX users.
One more thing—you have to have a DESC and an FD column, or you won’t be able to save the column definition. I usually put the attributes to the right of the last FD column because I think it looks better that way.
Over in the report definition, be sure to set the Detail Level correctly
None of this will work if the Detail Level isn’t right. Setting the Detail Level to Financial, Account, & Transaction is what allows you to drill past the summary levels and into the transaction detail to get to the attributes once the report generates.
Oh yeah, one more thing—you might experiment with the Shrink to page width option under the Settings tab > Other button.
Enjoy the drilldown! Cheers—Jan
Hi Jan,
with AX 2012 using budget planning, I only see an option that MR can pull budget plan scenario on the book code from the column definition. Is there not a way to pull the budget plan instead of just the scenario?
Tnx.
Hi EJ…bottom line, I don’t know. I don’t have an internal AX installation that I can er, ‘experiment’ with. I asked a couple colleagues and they didn’t know either. Hope someone here might know! Jan
Hi Jan,
I have listed the attributes in my columns but they don’t show up when I generate my report. Anything I’m missing? The attributes I’m using is the User Defined fields attached to my accounts. I’m doing a simple Trial Balance report. Thanks.
Hi Hasan…they should show up when you drill down to the transaction level. They don’t show up before you get to that level. To be able to get to that level, you should make sure the report definition’s Detail Level is set to Financial, Account & Transaction. Good luck…Jan
Hi Jan,
I understand you need to drill down to view the attributes. I am wondering if there is another way to include an attribute such as consolidation account on the account level of a report without having to drill down?
Thanks,
Saul
Hi Saul…I’d experiment with this: you can use column I, Row Modifier, in the row definition. Row Modifiers will let you specify account modifiers, book code modifiers, and set filters on Attributes. You still have to set the Detail Level to Financial Account & Transaction. But you might have to drill down at least one level, although putting the attribute in the description column might get you what you need. FWIW, I have screen shots and explanations and an example report in chapter 18 (top 22 tips tricks & techniques) in my 2nd training manual, but the above should be plenty to get you past this. Good luck. Jan
Hi Jan,
Have you seen any issue before where the Vendor Name is not showing the correct vendor name at all when using this attribute with dynamics ax 2012? i.e. when you drill down to view the vendor name that is associated with the voucher transaction. It shows an incorrect vendor name that was used in the voucher. Any idea? tnx.
Hi Jeff…hmmmm, I haven’t seen that but that doesn’t mean it’s not problematic. Wonder if it’s a datamart issue. I’d be tempted to ping tech support. Cheers…Jan
Tnx Jan. I just thought maybe you have seen this issue before. I may just raise a support ticket for it. thanks again.
Hi, Jeff!
I can see in your list:
– Document
– Document Date
– Document Number
But my MR is only showing:
– Document
– Document Date
Do you know what might be missing? I’m actually using ‘document’ to show the vendor invoice number but it’s blank. It is working for customers, not for vendor. Do you know which Table.Field is it?
Thanks a mill!
Hi Juli…the only ones that populate are the ones with data posted. Maybe that’s it? Jan
Hi Jan,
transactions are posted and I cannot see the number yet.
You might experiment by creating as many different ATTR columns as you can in order to see what shows up and what doesn’t. If it still doesn’t look right, looks like a tech support incident with your partner or Microsoft is in store. Crossing fingers. Jan
I got an issue displaying attribute customer name. im using it with the row modifier in the row definition filtering by every customer I got in AX (hehe). But when reviewing amounts I got an issue with two customers. one transaction was displaying in Customer 1, but when I checked it it should display in the customer 2. the transaction with the issue was a general ledger with one voucher. the voucher has two lines, Customer 1 at the debit side and customer 2 at the credit side. Management reporter is assigning the two amounts for customer 2 (I think because it assumes there will be one customer per voucher). Has some else got this problems, or thing is there is a hotfix for this issue?
Thanks in advance
Francisco Montano
I’ve tried adding the attributes but it seems like I am not getting all of the ones you have listed. You mentioned that it need to be in the data mart. How do you add to the data mart?
Hi James…your posted data controls what attributes show up in this list. That’s so you aren’t faced with a list of attributes that aren’t applicable. So it won’t be at all unusual to not see all the ones that are listed here. Bottom line, there’s no special step re adding to data mart: you just post your data and the right attributes show up in the list. Hope this helps…Jan
Thanks, Jan So if the attribute I am looking for is something we don’t post it won’t show up? The attribute I am looking for is “Created By”. You would think that is something that is posted. No? Or is that a set up issue within AX? A button that needs to be clicked?
Hmmm I simply can’t fathom that you’d need to click a setup button to get that, but I don’t know AX. Maybe ask in the community.dynamics.com forum? Or maybe a user will answer here…
James, not sure what you mean by posted in terms of created by. Created by is an attribute that can be enabled on I believe any fields in AX but out of the box it is only on certain fields. This is the same for modified by. For example if you go to the vendors table and right click and personalize on any of the column headers you can then select add field and under the vendor group of fields you can select created by. Rather or not attributes such as created by, modified by, etc I believe are controlled in the AOT.
Thanks, Saul. I was just responding to what Jan had mentioned on the attribute fields that may or may not show up in the list in MR. She mentioned “posted data controls the attribute list”. I was trying to see if this was a field we had to actually enter – which I wouldn’t think so. Right now, I cannot see the “created by” attribute and I was wondering why. It is a field that is being capture right now within an AX report since I can filter by this field when I run a standard report out of AX. I cannot within MR. Any ideas?
James, I see what you mean. I am noticing the same thing. It seems like every transaction should have a created by attribute and thus the created by attribute should always show up in MR but I have the same issue as you, it does not show up. Perhaps this is a bug. Please let me know if you find a solution.
Thanks,
Saul
Hi,
I am trying to pull one particular journal type for a company in column. tried using attibute but not able to put filer for particular journal type. Have someone got such scenario anytime. Is it possible by Management reporter to pull filter data in column for one particular journal