Category Archives: Business Intelligence

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.


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.


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:

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

Building a simple forecast solution on SQL Server

Pricing Forecast is a marketing function that requires a lot of data crunching.

Normally there are some factors that the Marketing team wants to adjust to find out just the optimum pricing for their products.

Accordingly, the forecast solution needs to be fairly flexible but still be powerful enough to make the necessary and sometimes even complex calculation. Otherwise, if the design is too rigid and requirements change slightly, then major changes in design need to be made, which in turn means whole rounds of testing.

Continue reading

The right terminology for the right audience

For those who has the privilege of being in the technology consulting industry for some time and started out in the technical side of things, remember those books or courses that teach about methodology or best practices?

For example, for project management, you need to specify a requirements gathering stage, done by the Business Analyst, the specifications stage, solutioning stage, project planning stage, development stage, unit testing stage, integration stage, iterate between development, unit testing and integration stage again and again, user acceptance testing stage, go live, application support, re-look at the lifecycle …. zzzzzzz…

Thing is most of the time I find that business users don’t really care for these terms. I recently simplified it (a lot!), and my boss still removed the words “user acceptance” in the testing stage. Eyes start to glaze over when explaining the various stages to business users thinking they would appreciate to know how structured I was. (Guess they weren’t that interested after all)

If you are a developer, and your audience are technical integrators or project managers, then by all means expand on these terms. Otherwise, users want to hear things using words from their world. (Things like YoY, MoM, Revenue, PPI, KPI for finance and operations, or production performance, keywords search results for others.)

Point is: Tailor your communication to your audience. (And this applies to developers too – especially developers who love to use your developer world terms. 🙂 )  Basically use what is useful and gets the point across clearly and concisely.

What is in your mind? Do you see my solution?

Hello, it’s been a while since I last blogged, so I’ll get right into it.

What’s in your client’s mind when you present a version of the solution? Do new things come up that was not mentioned before? Do you go “I told you so” in your mind?

Well, I recently had all three thoughts come to me. As simply technologists, which is what a BI developer is generally, there are several roads that lead to Rome, and you choose the most logical and straigtforward to begin with. In my case I knew that we needed to have two different entities for the project, a “client” and a “case”. Therefore, it makes sense to separate the two because of the 1:N relationship – a client can have more than one case attached to him.

However, I was told that the client and the case are one – because traditionally, there was only one ID assigned to represent both. Granted, I acknowledged that it should now be a 1:1 relationship between client and case and designed the database schema as such.

At the next meeting, I presented my schema and lo and behold, there could be more than one case attached to a client. …

I think often the client very much wants to keep what’s working, not to change anything too much, in this case the ID that they are working with. However, logically, it just didn’t make sense and as a BI developer, it is my duty to explain so. In the end we decided to adopt a combination of ClientID and CaseID.

The good thing is that nothing is built yet. I am a proponent of sorting these things out on paper rather than in code so alls well.

Next step is to include other users in the process so that I am not caught out with my design.