Dynamics 365 Business Central – Reporting with Dimensions
Using global and shortcut dimensions in Dynamics 365 Business Central provides much flexibility for reporting options. One limitation, which isn’t much of a limitation, is that you can only filter on up to four dimensions.
However, building the report to use up to four dimensions takes a few steps in order to properly report on dimensions (other than Global 1 and Global 2). By default, you can create an account schedule using the Global 1 and the Global 2 dimensions but not on shortcut dimensions. Here are the steps to report on shortcut dimensions:
- Create an Analysis by Dimension View
- Update the Analysis by Dimension View
- Assign the Analysis View to a Column Layout on an Account Schedule
- Personalize and Design the Column Layout Window to Add Filtering Options
- Print the Report
Sounds simple enough! And it is, but there are a few things to keep in mind. Plus, if you are new to Business Central, you may not have setup an Analysis by Dimension View. So, let’s get started.
Create an Analysis by Dimension View
It is important to reiterate, that without the Analysis by Dimension View, you can only filter your account schedules by your Global Dimension 1 and Global Dimension 2. You must set this up if you want to filter, or create column filters, on any shortcut dimension.
First, Navigate to Setup & Extensions >> Manual Setup >> Analysis by Dimension. Click New and enter an ID and name for your view. You can also select to limit the data in the Analysis View to a specified account range, such as only your profit and loss accounts. Once you populate the General fast tab information, select the dimensions you wish to report on; again, you can select up to four dimensions. When you are deciding which dimensions to use, keep in mind that these are the only dimensions that can be used for their assigned column layout. In other words, if you select only shortcut dimensions, then you can no longer use your global dimensions for the report assigned to this view. Not a bad thing, just want you to keep that in mind!
Update the Analysis by Dimension Card
Although this step is uncomplicated, it is one that can be easily forgotten! Before you generate a report using an Analysis View, make sure to update the view or your data will not accurately be reflected.
There are two ways to do this. The first option is to navigate to the Analysis View (Setup & Extensions >> Manual Setup >> Analysis by Dimension). Then, select the View you want to update and select Process >> Update. After you run this, the Last Date Updated field and Last Entry No. field in the card are updated. The second option is to search for Update Analysis Views. You can then select the View name and click process. Personally, I like the first option, so I can confirm the entry number field was updated on the view. It is kind of like double checking my work – or maybe I’m obsessive but double checking sounds better! Onto the next step…
Assign the Analysis View to a Column Layout
Again, this is not challenging, but getting it setup correctly is key in order to create a filter using your shortcut dimensions. Navigate to Financial >> Account Schedules >> Process >> Edit Column Layout Setup (or you can search for Column Layout). From here, click on the icon to select a column layout title and click Edit List. Then enter or select the Analysis View name next to the column layout you want to use. In my example, I assigned SHORTCUT Analysis View to the REGION Column. Then click OK.
That’s it for this step! I know, crazy easy, right?
Personalize and Design the Column Layout
Now the fun begins (insert meme here of a crazy scientist rubbing his/her hands together)! Let’s design the column to use one or even two of the dimensions from the Analysis View. If you only have two dimensions, you may not need to personalize the page. However, if you have 3 or 4 as in my example, you will most likely need to personalize the page.
Click on your gear or settings icon and select personalize. Make sure you have selected a line in your column and click More >> +Field. On the right, you will see a list of additional fields that can be added to this window. Locate the dimension(s) that you want to filter on, and drag and drop the field onto the page. Once you are done, click Done.
Now that you added your Analysis View dimensions, you can add filters as needed for your report. In my example, I created a column for each of my Region dimension values. To create the filter, simple select the dimension value to filter upon. Now you are ready to print your report, so close the Column Layout page to go back to the Account Schedules.
Print the report
Let’s see what this looks like. From the Accounts Schedule page, select Report >> Print. Confirm the column name is the one you just modified. Update your date filters, and if applicable, update your dimension filters. If your Analysis View does not have global dimensions, you will notice you do not have the option to filter by the global dimensions. Once your filters are set, click Send To. From here, you have the option to send the report to Excel, Word, or PDF; in this example we used PDF.
Remember, in our previous step we created column filters for the Region dimension. As you can see in the report example generated, the balances for the dimension values are populated in their respective columns.
Congratulations! You have reports based on your shortcut dimensions. The good thing is, once your view and account schedules are created, you do not need to repeat all those steps. You do, however, need to run the process to update the Analysis View prior to generating this report. Again, this process makes sure that you have the most up to date balances on your view, and report.
We understand that your business is unique and won’t fit a pre-determined mold. So we can review and simplify the tasks to avoid the headaches that can happen. Simply reach out to us at 833-827-4276 or email us at email@example.com.