emeadaxsupport: AX 2009: The “Goal” value in the Profitability Analysis report on RoleCenter page pages does not show the budgeted values

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...ed-values.aspx
==============

I came across this sometime back now. The “Goal” value in the Profitability Analysis report on RoleCenterpage pages does not show the budgeted values.

Going through the KPI underneath the Report I found:

-The general ledger KPIs in the analysis database does not take into account any budget defined in AX Client as the goal of the KPIs. Instead however they shows the goal as being a fixed percentage (99% for COGS) of the last period (or 0 when the last period is empty) (You can open the general Ledger cube in BIDS/VS and on the KPI tab verify the Goal expressions for each KPI defined in the General Ledger cube)

- Discussing these findings with my Financial Escalation Engineer we concluded the reason for using these fixed values is because Budgeting is not required for accounts and without, these KPIs would always show a 0 goal value.

Having a fixed value based on the previous period appears we could apply a simple workaround to this to show budgeted values:

Reverse engineering the Profitability report for the COGS KPI we submit the following MDX query (Demo data with Fiscal Year 2006-07-1 period 2006-08-01 (Currency USD))

SELECT {KPIValue("Cost of Goods Sold"),KPIGoal("Cost of Goods Sold"), KPIStatus("Cost of Goods Sold"), KPITrend("Cost of Goods Sold")}ON COLUMNS,
STRTOSET('[Ledger period time dimension].[Ledger Period Hierarchy].[Year].&[CEU]&[2006-07-01T00:00:00].&[CEU]&[2006-08-01T00:00:00]') ON ROWS
FROM [General Ledger Cube]
WHERE STRTOMEMBER("[Master Company Reporting Currency].[Currency].&[" + 'USD' +"]")
The Value expression of this COGS KPI is defined as below where it can be found we’re getting the [Master Amount - Ledger] measure for Ledger account category “35” (COGS)

([Measures].[Master Amount - Ledger],[Ledger account descriptions].[Ledger account categories].&[35])
With this expression it’s easy to formulate the budgeted COGS the value Expression for this will be

([Measures].[Master Budget Amount - Ledger],[Ledger account descriptions].[Ledger account categories].&[35])
Through replacing current COGS KPI Goal expression with budgeted COGS value (from above) and saving the modified KPI, the KPI (and profitability analysis report) shows you the Budgeted COGS as the Goal value.



Repeating the similar steps for each KPI defined in the general Ledger account groups where you’re budgeting makes your budgets show up a the goals value in the KPI / Profitability report.



Further Information:
For testing the expression with the Contoso demo data I used the Query below

SELECT {
([Measures].[Master Amount - Ledger],[Ledger account descriptions].[Ledger account categories].&[35]) //Value
,
([Measures].[Master Budget Amount - Ledger],[Ledger account descriptions].[Ledger account categories].&[35]) //New Goal
} ON COLUMNS,
STRTOSET('[Ledger period time dimension].[Ledger Period Hierarchy].[Year].&[CEU]&[2006-07-01T00:00:00].&[CEU]&[2006-08-01T00:00:00]') //First date is Year, second is period
ON ROWS
FROM [General Ledger Cube]
WHERE STRTOMEMBER("[Master Company Reporting Currency].[Currency].&[" + 'USD' +"]")


Disclaimer:
This programming example is for illustration purposes only. Microsoft disclaims all warranties and conditions with regard to use of the programming example for other purposes. Microsoft shall not, at any time, be liable for any special, direct, indirect or consequential damages, whether in an action of contract, negligence or other action arising out of or in connection with the use or performance of the programming example. Nothing herein should be construed as constituting any kind of warranty.

--author:Jago van der Most





Источник: http://blogs.msdn.com/b/axsupport/ar...ed-values.aspx