(Update—there’s been a terminology change since I wrote this! I originally wrote about Dimension Sets, but in MR 2012, Dimension Sets are now called Dimension Value Sets. And just to make things tougher, Dimension Sets now refer to something completely different. Gotta love it. Anyway, I’ve edited this post to reflect this change.)
I was recently asked about my top tip for faster easier reporting in Management Reporter.
It didn’t take much thought—my answer was using Dimension Value Sets. They can streamline and simplify and ease maintenance.
Set up a Dimension Value Set once, use it across multiple building blocks, and maintain it in one place. What’s not to love?
Well I’ll tell you what’s not to love—there’s a bug that won’t let you subtract an account. To be precise, you can subtract it in the set, but MR won’t do what you say (I can’t help but be reminded of my husband). Let’s hope for a fix soon because these are a good investment of time. (Update—this has been fixed.)
OK one more thing—migrated FRx reports do not take advantage of this type of design. So it’s always good to review migrated FRx reports with an eye for this type of improvement.
So without further delay, here are a couple of examples of how you can use already-created dimension value sets. They’re not just for rows anymore!
BTW, the information below is excerpted from my second training manual Creating Reports in Management Reporter II—Step by Step Instructions on a Dozen More Reports.
Use Dimension Value Sets to create a summary reporting tree
This tree pulls all departments, but has only 2 detail reporting units. This is incredible if you don’t need each department detailed, and you still have lots of drilldown capability.
In column D, you have access to exactly the same Dimensions dropdown box as in the row format. This time, I’ll pick Segment 1, drop it down, and there are the dimension value sets for the Departments that I’ve created.
As a refresher, this is a list of all the departments:
000 Default
100 Administration
200 Accounting
300 Sales
400 Service/Installation
500 Consulting/Training
600 Purchasing/Receiving
999 Other
Use Dimension Value Sets in columns definitions too
I just created a dimension value set called P&L Accounts and I’m using the Dimension Filter cell to restrict each column to P&L accounts only. (The Account dimension is here because the row contains the departments.)
There are so many creative possibilities with dimensions and dimension value sets and the way they can be used across rows, columns and trees.
Cheers—Jan
hi Jan
Has the bug that you refer to been fixed yet?
I have 11 departments in segment 3 of my 4 segment chart of accounts. Segment 3 also does double duty in other parts of my chart of accounts to identify things like product group, when we get to the sales and cost of sales section of the income statement.
My entity is in segment 1.
For my office entities I want to make a tree that splits out each of the 11 departments, and would love to use dimension sets to identify the 11 departments.
thanks in advance!
Ian
Hi Ian…yes it was fixed in MR 2012. You’re welcome!
Hi Jan
thanks for the reply. I have put in a dimension value set and broke my income statement.
I am using it on a tree
I want to make a tree as follows:
entity = 120 group = sales
entity = 120 group = production
entity = 120 group = IT
… there are 12 different groups to specifically name
then I want to have
entity = 120 group = whoever was not named in the specific groups
so I have
entity = 120 less group = value dimension set where value dimension set = the named groups above.
When I run the tree with just entity = 120 I get all the gl accounts.
When I run the tree with all the different named groups including the value dimension set, all the accounts are not pulled. It is like the value dimension set subtracts all groups.
Do you know if there are any further bugs with using this feature? Any further fixes?
thanks in advance (again) – particularly in this case am i’m soliciting free consulting.
Ian
I was hoping you could help me out with Dimension Sets.
Here is the issue: my accounting folks created and use Account sets in FRx which are now Dimension sets in MR. It would seem to me that the application would
allow you to group Dimension Sets to create roll-ups however we are unable to figure this out. Is it possible?
We are running Management Reporter 2012 RU4 Hot Fix (ver 2.1.1033.30) with Dynamics SL 2011 SP1
Hi Kevin…I haven’t seen a way to group dimension value sets. But you can add multiple sets to one row. It would look something like this in the Link to Financial Dimension column: “+Segment1 = SET(Dept0,1,2) +Segment1 = SET(Dept 3,4,5,6,9)”. Can’t reuse across multiple building blocks though. An alternative would be to create new dimension value sets of all the values you want to group; could get messy but might be worth it. Suggest using a good naming convention if you go this route. Good luck…Jan
Hi Jan – maybe you have a better idea. i am using dimension value sets in my reports (MR2012 with AX2012). theres quite a lot of value sets i use. Now, there is a need to change the dimension name in AX. i.e. now when this was done in AX, the dimension value sets i had in MR started erroring. It appears i may have to redo all my dimension value sets and pickup the new dimension name from AX and reassign these back to my reports… have you seen this issue before and do you of any quick way to rectify this instead of manually redoing it? Appreciate any inputs you may have. thankyou!
Hi Edward…nope, thankfully I haven’t hit anything like that yet. But if you have zillions of reports, I’d be tempted to open a ticket with microsoft and let them recommend the best way. Thinking maybe they could do this behind the scenes with an SQL statement that would update everything at once and keep you from fooling around with this manually. Cheers…Jan
Thanks Jan. Appreciate it.
I am finding it difficult to easily manage my Dimension Sets. I find myself opening a Row Set and then drilling down to see the values included in my Dimension Set. Is there an easy way to get a snap shot of what values are included in a Dimension Set? Thanks
Hi Jason…yes, but first you have to make sure you have a row or a column or a tree open. Any of them will work. Then go to Edit > Manage Dimension Value Sets. That will give you the option to see them all listed, either grouped by dimension or not. Cheers.
Ian and others may be interested in the following Microsoft Connect suggestion:
https://connect.microsoft.com/dynamicssuggestions/feedback/details/799794/multi-dimension-support-for-dimension-value-sets
Jan,
Thank you for your response. Your suggestion is much better, but I still want a quick listing in a report/excel style. Perhaps someone can provide a SQL View or tell us the tables used to store the Dimension Sets. Thanks
Hi Jan,
Have you heard of (or figured out a way) to define DVS’s in MR based on multiple dimensions? For example, I have seen many FRx Account Sets where 1 set is made up of accounts defined by both a single segement for some and the Full account (sometimes 2 or more segments) for others. If this is the same issue that you and Kevin discussed above, my apologies. Thanks in advance. Mike
Hi Mike…you might take a look at the link above that Quixcle posted. I don’t know if it’s relevant or not because thanks to some Microsoft insanity, I’m not able to log into Connect at the moment. However, even if it’s on point and in the works, I’d be really careful with this because it’s hard enough to get a report accurate with ONE dimension. Then when you add a second dimension to the mix, much less the full account structure, it becomes much more complex. It’s done daily in the ivory tower, but in the real world where accuracy is important above all else…not so much. I recognize that sometimes you have to go this way, but when possible I prefer to use column D in the row to restrict to a reporting unit if a departmental restriction is needed. Off my soapbox now :) Jan
Thanks Jan. I appreciate the suggestions! Mike
Jan, is there an easy way in MR to use the existing information in the FD GL link dimensions block and create a dimension value set without re-typing all of the account information already contained within? FRx you could rename the GL link row format “cell” to a “Dimension Value Set” (I forgot the FRx term) is this an option in MR?
Hi Darryl…yeah those were account sets in FRx and I know exactly what you mean: you entered the accounts and had the option to Save As an account set. I haven’t seen any such thing in MR. I haven’t tested this but you might try copy/paste, but guessing that will be a no go. I can’t test on my version, but if memory serves, you can only use one dimension at a time in a dimension value set. If that’s problematic, you might consider using the account category dimension instead. Good luck…Jan
Hi, I’m stuck on this issue. My GL string is 4 segments, XX-XXX-XXXX-XXXX (Entity, Sub Entity, Revenue Center, and GL account). In MR, my row definition mostly uses segments 3 and 4, and the tree breaks everything out by segments 1 and 2. I’ve recently been tasked with trying to split one GL string into 2 rows (because I’d rather do that rather than just add new accounts) so the owner of the company can identify our use of Gasoline at one entity, separate from the others. I’ve tried numerous attempts, but they keep duplicating the info in the other entities. Is it flat out impossible to do on a row definition?
In a nutshell, the status quo shows a row for Gasoline, let’s say for $10,000, and the bulk is consumed by one entity, (let’s say $8,000). Currently, the consolidated report shows:
Gasoline $10,000
The owner would like it to read:
Gasoline – Entity A $8,000
Gasoline – Others $2,000
He understands that the individual entity report gives that information separately, but he mostly looks at the consolidated report. Any ideas?
Hi Andrew…yes, in the row definition, you can use column D Related Formulas/Rows/Units. Drop its box down, choose your tree, then restrict the first gasoline row to the unit in the tree that corresponds to entity A. Then you’ll have to do a TOT row for Gasoline Others which may then impact some of your formulas. And be sure you use some method to check accuracy. Cheers…Jan
Do you know of a way to print out a listing of what the dimension value set contains?
So sorry for the delay Peter! No, I don’t know a way, but I’d maybe experiment with this technique if you’re desperate. If you can’t select with mouse, try Ctrl-A (keyboard shortcut to Select All). Hope this will work. Cheers…Jan