All posts by Terry Choo

When not to use Management Reporter

Management Reporter is easy to use to create Management Reports and it does not require a developer to do it. But there are times when Management Reporter is not the right tool for the job.

Here are some reasons why you should not use Management Reporter:

1. The data you need is not in General Ledger or Budgets module in Dynamics AX

This is kind of the kill all of Management Reporter. If your report asks for things not in the General Ledger or Budgets, then no matter how hard you try, it cannot be done, even if the data you need is in the subledgers.

Common examples are if you need customer, supplier or project detail. These are stored in the Sales Ledger, Purchase Ledger and Projects module respectively and the data cannot be found in General Ledger. 

But hold on! I think one of the least known ability of Management Reporter is to be able to report on the attributes at transaction level. So you can report on attributes of the transaction like transaction date, voucher numbers, created by person etc at transaction level. This is quite powerful as it allows the report to provide detailed information at the drilldown level.

Also, there is a limited workaround in that I have seen Customer ID, Supplier ID and Project ID being set up as Financial Dimensions. Hence, with a little Excel magic, detail from these items can be added to the report.

2. You need graphs

Do you know you can create charts or graphs in Management Reporter? Yes, you can, trust me.

Have I seen a need to use it, not really. Due to the nature of reports being in a grid like format, the graphs are pretty inflexible and you cannot really do much with them. Throw in a bar or pie chart if you like, but compared with the likes of Power BI, it is like a University Graduate looking at work submitted by a pre-schooler. You are probably better off downloading the data into Excel and working on that.

3. Automatic publishing to MS Word, Excel or Adobe PDF

Cannot be done. If you are fortunate (I really mean old) enough to use FRx, you might have noticed that the ability to publish report directly to Excel, Word or PDF does not exist anymore due to what Microsoft terms “Better security”. So banish that idea. There are other tools in the market but they do not have the same flexibility as Management Reporter.

4. Real time

This is debatable. Normally, Dynamics AX data is integrated every 5 – 10 minutes, so the lag is not unreasonable and most people can live with that. However, in a very few rare customers, data integration takes hours so maybe that is an issue.

5. Integrating with data outside AX

If data is outside of Dynamics AX and what you need to integrate is really minimal, then there is the option of using Linking to External Workbook to integrate data from Excel spreadsheets. If it gets even a little complex, consider using something like Power BI or create a DataWarehouse instead. 

The Linking to External Workbook function is clunky to setup and maintain. The Excel files MUST be on the same server as MR and it is difficult to get it to work. So I would not try to go down this route. Of course there is always the manual process to download and integrate in Excel.

By the way, if you are using Dynamics 365 for Operations, then perish that thought. Linking to External Workbook does not exist.

Do use Management Reporter for the following:

1. Clear report based on Main Account codes and Financial Dimensions.

2. Near real time data is fine.

3. Easily created reports by the business and not IT.

4. Ability to look at data in summary level and then drilldown easily. Many other tools require a lot more effort to achieve this.

5. Data in the report is only from General Ledger or Budgets.

6. This is the only reporting tool in Dynamics AX I know that does not require you to learn about the database structure in Dynamics AX first – so they are generally quick wins.

In Summary

While Management Reporter is great at what it does – create Management Reports, there are some limitations and one should therefore look at other options to achieving your reporting goals.

Using Organization Hierarchies as Reporting Unit filters with Management Reporter

Well, hello. It has been more than three years since my last blog, since then I have been really busy at work and have a baby in the meantime.

That being said, I wonder how much joy you guys have with playing with MR.

I have been using Dynamics AX and MR for more than 3 years now and I am still able to learn new things. One of them is that MR is able to use the organizational hierarchies of Dynamics AX as the reporting tree.

For those who are clueless, in your organisation, you can define the organisational hierarchies of not just the standard organisation but for all your financial dimensions. The idea is that when you select an organisational hierarchy as the reporting tree, you do not need to create it from scratch. Furthermore, when the hierarchy changes in AX (I dropped the word “organizational” as it just makes typing too long), MR is able to reflect that based on the period selected for the report. I thought that was pretty cool.

What happens when you want to use the hierarchy as a filter in the column definition?

Terry: why in blazers would anyone want to do that?

Well, it so happened that I had a customer who needed to do just that.

Imagine the need to have different columns represent the amounts not at leaf level, but at a middle hierarchy level. Sure, you can filter on dimensions and manually create it, but there is no need to re-invent the wheel.

Do you know you can make a copy of the hierarchy at a point in time? Select a particular hierarchy, then save it. VOILA – it becomes a reporting tree.

Then open it and have a look – do you see the problem of using this tree?

That’s right, levels higher than the leaf level have dimensions assigned to them! This will not work for roll ups.

What you need to do is to remove the dimensions of the upper levels leaving the dimensions just for the leaf level. Now the tree will roll up correctly.

What happens when the hierarchy changes? Just save the latest hierarchy as a tree and delete the dimensions of the upper levels again. This should take you all of 5 – 10 minutes.

Have fun! :p

Using PIVOT function without a CTE

There are some cases when you need to use a PIVOT function in SQL but you cannot use a CTE. One such case is when you create a named query in a SSAS cube.

To do so, place the first part of the CTE query as a subquery of the Non-CTE query as shown here. Remember to add an alias to the subquery. The PIVOT portion of the code remains the same.

PIVOTwithoutCTE

The basic understanding of the PIVOT function can be found here.

Note: It may increase query performance when joining another table to the pivoted table to use join hints. e.g. LEFT OUTER HASH JOIN

Microsoft Dynamics AX 2012 – Multiple Rows In Demand Forecast Table [FORECASTSALES] for each item

Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012?

This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table.

Basically the overview line and each exploded line of the same item is stored in the same [FORECASTSALES] table. Therefore, if any measure in this table needs to be aggregated per item, filter out just the overview rows. You can do this by filtering the column [EXPANDID] = 0.

Continue reading

Management Reporter Security

Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here.

In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped by how assigning different roles to users in Management Reporter does not quite turn out as they had expected. Both times I felt I could only give a partial answer. Thus, I decided to do a little experiment to find out the truth once and for all… or until Microsoft comes up with the next Rollup I suppose!

In this topic, I will cover:

Management Reporter is able to integrate with different flavours of Microsoft Dynamics but in this topic I will cover integration with Microsoft Dynamics AX 2012 only, and only for up to RU5, simply because that is what is available to me for my experiment!
Continue reading

Conditional Coloured Bar Charts in Excel with Upper and Lower Targets

I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility.

It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which means I need to expand the depth of the topic.

Here is what I want to achieve:

How do I get the conditional colour formatting of the bars in the chart?
Continue reading

What is Microsoft Management Reporter for Dynamics AX?

It is a collaborative and interactive reporting solution from Microsoft for finance data.

View more here:

Management Reporter 2012 Overview

How is it different from standard SSRS?

It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to share via lync or sharepoint, and to insert comments at defined points of the report.

The other massively useful function is the ability to drillthrough all the way to the Dynamics AX entry.

The other functions I think which are useful are:

  • Multilanguage support
  • NO IT involvement required once setup
  • auto-incremental update of data (NO ETL required)
  • Multisource consolidation
  • Integration with Dynamics AX

The documentation for Management Reporter is available now on Technet.

http://technet.microsoft.com/en-us/library/dn435963.aspx

Note:

Microsoft SQL Server 2014 is not currently supported.

Minimum database requirements:

  • Microsoft SQL Server® 2005 Express Edition with Service Pack 4
  • Microsoft SQL Server 2008 or newer is required for the data mart database, system database, and the company  database for the ERP.

Introducing Microsoft Dynamics AX 2012 with SSRS Reports

Hi all,

I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business.

Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to support BI in an AX environment.

SSRS is now the primary reporting platform for Microsoft Dynamics AX since the 2012 incarnation.

Here’s the quote from Books On Line:

Microsoft SQL Server Reporting Services is now the primary reporting platform for Microsoft Dynamics AX. The default, preconfigured reports that are included with Microsoft Dynamics AX run on the Reporting Services platform.

Good news is that everything you know about SSRS can be applied into the world of Dynamics AX now. All the flexibility of being able to tweak every little detail is available to the report.

On the other hand, it would be useful for AX developers to know more about the SSRS development environment which includes:

  • SSRS services provides two URLs – one for the Report Manager, and one for web services
  • Administrator rights is required for deployment of reports
  • Report Security has to be defined in Report Manager. A new security role is available for this. It is called: DynamicsAXBrowser

For SSRS developers, it is useful to know the following:

  • The No. 1 rule is not to edit the report without saving the project back to the Application Object Tree (AOT). This is because if the AOT is rebuilt, all changes will be wiped out.
  • To create a new report, create a new query in the AX Development Workspace first before creating a new report.
  • Create a new report in Visual Studio 2010 and use the new Microsoft Dynamics AX Template called Report Model.
  • Creating a new report this way requires a whole different process than a normal SSRS report. (Which I will discuss in a separate post)
  • You can find out the SSRS url either from the AX Development Workspace (Tools -> BI Tools -> Report servers, or the usual way – via SSRS Configuration Manager.
  • Save the report project into AOT.
  • Then deploy the reports. There are two ways – via Visual Studio (which provides more output messages which is useful for troubleshooting, or via the AX Development Workspace)

For more information of SSRS in Microsoft Dynamics AX 2012, visit: http://technet.microsoft.com/en-us/library/dd309644.aspx

First take on PowerPivot

What is PowerPivot for Excel?

PowerPivot brings the power of Data Modelling into your PC.

Traditionally, the DataWarehouse is built on a database server (SQL) and a data model is built on top of it (SSAS). Then, a front end is used to present the data to users.

Now, data can come from different sources and pulled into the PowerPivot engine, relationships defined, calculations done and reports presented on Excel.


Continue reading

Understanding the PIVOT function

The PIVOT function is very useful but not easily understood at first. I thank Itzik Ben-Gan, Dejan Sarka and Ron Talmage and their study guide Exam 70-461: Querying Microsoft SQL Server 2012 for the brilliant explanation.

Here’s their example replicated here. Remember there are 3 columns used in PIVOT. The Grouping column is what you want each row to be aggregated to. The Spreading column becomes the columns after PIVOTing, and the Aggregate columns are the measures.

You need a pivot aggregate function (SUM in this case) and COUNT(*) cannot be used but COUNT(<column>) can.

The reason a common table expression is used to select only the necessary columns is because all other columns beside the Spreading and Aggregate columns will become the Grouping element which may not necessary be what you want.

Continue reading

Get list of dates using recursive Common Table Expression (CTE)

Many times we need a list of consecutive dates or time at runtime mainly for a recursive ETL process. This code provides a list almost instantaneously with only the start and end dates being the only input. The output is in Date format but is easily extended to a DateTime format.

For best results, you can use this in a CTE as part of the solution so that it does not take up space in the database.

The MaxRecursion option ensures that the recursion does not go on forever, however the Per Minute and Per Second versions will definitely overrun the MaxRecursion option.

Note: The code for each one are similar, the only difference is the first parameter of the DATEADD function which determines the recursive detail.

Daily
Monthly
Yearly
Hourly
Per Minute
Per Second
Continue reading