Technical Tip: How to Add a KPI to an Existing Rolecenter Web Part for Microsoft Dynamics AX

Technical Tip: How to Add a KPI to an Existing Rolecenter Web Part for Microsoft Dynamics AX

​This article contains a series of technical steps to successfully add a key performance indicator (KPI) to an existing Rolecenter web part in Microsoft Dynamics AX ERP.

Fig.1-Technical-Tip-How-to-Add-aKPI

I will show you how to add a very simple KPI called “My Revenue.” It doesn’t really do anything except add the a goal of 105 and set the value at 100. Creating meaningful values might be a good exercise for another post, but here I am showing more how to define the KPI and how to get it to publish to your role center.

Step 1. Use Bids (Visual Studio Business Intelligence Development Studio) to create a new project to make adjustments to the SAS cube. Create a new Project using the Import Analysis Server template. Pick your sever and OLAP d/b. Bids will create a project with all the cubes.  Your project should look something like image below:

Fig.2-KPI-Project

Find the General Ledger cube and view in the designer. Under the KPI’s tab, add the new KPI from the toolbar. From here I was able to basically create a very simple kpi by creating 4 methods in the KPI, including goal, value, trend and status.

Name – My Revenue
Value Expression – 100
Goal Expression – 105
Status Expression: (Not all needed for my very simple on)
iif( KPIGOAL(“My Revenue”) >= 0,
(CASE
WHEN KPIVALUE(“My Revenue”) >= KPIGOAL(“My Revenue”) THEN 1
WHEN KPIVALUE(“My Revenue”) >= KPIGOAL(“My Revenue”) * 0.90  THEN 0
ELSE -1
END),
(CASE
WHEN KPIVALUE(“My Revenue”) >= KPIGOAL(“My Revenue”) THEN 1
WHEN KPIVALUE(“My Revenue”) >= KPIGOAL(“My Revenue”) * 1.10  THEN 0
ELSE -1
END))
Trend Expresion: 1

Set the status and trend icons as you want.

You can view the KPI in browser mode right from VS.Browser Icon from the toolbar.

Save your changes. Here you can see the KPI in the designer:

Fig.3-Save-Changes-KPI-Designer

The initial project is set to deploy to a d/b that you can use during development, which is the same name you gave your project. But for the next part to work, we have to deploy to the OLAP d/b expected from SSRS. So in the project properties, there is a deploy properties for Target – here set your server and database to what SSRS expects. Then back on the project, click deploy to update the new server/db.

Step 2. In the Microsoft Dynamics AX client – Under Report Libraries, select Ledger. Right Edit in Visual Studio. Just like editing  any of the SSRS reports in Dynamics AX. Once the project is available in VS. Then you find the correct report to modify in my case, CFOProfitablityAnalysis.

Fig.4-CFOProfitablityAnalysis

Edit in report modeler.

Find The dataset ProfitablityAnalysis – click on the properties. Add your KPI values – here I add “My Revenue”:
SELECT {
KPIValue(“Total Revenue”),KPIGoal(“Total Revenue”), KPIStatus(“Total Revenue”), KPITrend(“Total Revenue”),
KPIValue(“Total Expenses”),KPIGoal(“Total Expenses”), KPIStatus(“Total Expenses”), KPITrend(“Total Expenses”),
KPIValue(“Cost of Goods Sold”),KPIGoal(“Cost of Goods Sold”), KPIStatus(“Cost of Goods Sold”), KPITrend(“Cost of Goods Sold”),
KPIValue(“Gross Profit”),KPIGoal(“Gross Profit”), KPIStatus(“Gross Profit”), KPITrend(“Gross Profit”),
KPIValue(“Gross Profit Margin”),KPIGoal(“Gross Profit Margin”), KPIStatus(“Gross Profit Margin”), KPITrend(“Gross Profit Margin”),
KPIValue(“Net Income”), KPIGoal(“Net Income”), KPIStatus(“Net Income”), KPITrend(“Net Income”),
KPIValue(“My Revenue”),KPIGoal(“My Revenue”), KPIStatus(“My Revenue”), KPITrend(“My Revenue”)
}

ON COLUMNS,
STRTOSET(@Period) on rows
from
[General Ledger Cube]

where STRTOMEMBER(“[Master Company Reporting Currency].[Currency].&[" + @Currency +"]“)

Fig.5-Property-Designer

I did this with the property designer – but it wanted  parameters, otherwise it chokes on the parameters values and says there is an error in the mdx expression.  In the parameters, I put in Period.Period (another dataset field) and put USD in for currency. After that is accepts the properties okay.  On my first pass, somehow it had renamed period to period1 and was giving validation errors on the report design, if it does that, just click on the field in the dataset and rename back to period. And then it will clear out. Once you’ve done this okay, you should see your new KPI values (4) in the fields under the dataset.

Fig.6-Dataset

Next edit the Role center design(edit using designer). Basically there is a table with rows and columns for the KPIs. On the left column, find the row where you want to insert your KPI and right click – insert row below. Then copy and paste one of the other KPIs fields down for each column and then edit to your KPI properties – specifically the value property. Here are my values:

=Fields!Measures_My_Revenue_Goal.Value
=Fields!Measures_My_Revenue_Value.Value
=IIF(Fields!Measures_My_Revenue_Trend.Value = 1, “TrendUp”, IIF(Fields!Measures_My_Revenue_Trend.Value = 0, “TrendFlat”, “TrendDown”))
=IIF(Fields!Measures_My_Revenue_Status.Value = 1, “StatusGreen”, IIF(Fields!Measures_My_Revenue_Status.Value = 0, “StatusYellow”, “StatusRed”))

Fig.7-Review-Report

Once that is done you can preview the report and see if your kpi shows up correctly.

Once it works right in preview– On the project – Ledger-right click and build. Make sure it builds correctly(no errors). Then save to AOD. It will save that it saved to the AOD successfully down in the status bar.

Step 3. Deploy the updated report library.  In Dynamics AX, you will have to restart your client to see the AOD layer changes i.e., sys,syp,usr. In the AX client, right click on the ledger report library and click deploy, this will copy the report out the Report server you have defined. It takes a bit, get a cup of coffee.

After that refresh your EP page and bam, it’s there.

Simple … yes?


manufacturing, ERP, KPI, AX Technical Tip, Microsoft Dynamics AX ERP, Microsoft Dynamics ERP, Rolecenter, Web Part