Wednesday, November 2, 2011

Argument passed to ‘KPIVALUE’ function must be a KPI name

During the past two weeks, I have been troubleshooting the Reporting Services Error on the AX2009 Enterprise Portal, especially for the ‘argument passed to the ‘KPIVALUE’ function must be a Key Performance Indicator (KPI) name’ issue.
image
In the following steps, I will show you how to troubleshoot the error with the not-functioning ‘KPI for Project Manager’ Report server report.
  • Find out the report library on the AOT.
On the SharePoint portal, click ‘Site Actions’ –> Edit pages, look for the report name.
image
Open an AX client, go to Tools –> Development tools –> Label –> Label Editor and enter ‘KPI for Project Manager’, which we will get the label ID ‘@SYS121988’.
image
In AX client, open an AOT tree, go to Menu items –> Output node, and right click and choose Find.
In the search dialog, choose All nodes and in ‘Containing text’, enter the label ‘SYS121988’.
image
Now we get the object which is ‘ProjKPI_ProjectManager’. In the AOT, go to Menu Items –> Output node and locate the ‘ProjKPI_ProjectManager’ and right click and choose properties.
image



On the properties, we get the object which is ‘ProjKPI.ProjKPI.KPI’.
  • Edit the report library in Visual Studio 2008
Go to AOT –> Report libraries, and locate ‘ProjKPI’ and right click and choose ‘Edit in Visual Studio’ which will open Visual Studio 2008.
Because in the error ‘Query execution failed for dataset ‘NetWIP’. We double click the ‘NewWIP’ dataset in Visual Studio 2008.
image
Press F5 to execute the query, put in company account value and click OK.
image
The same error as on the portal.
image
  • Debug in SQL Server Business Intelligence Development Studio
Start the SQL Server Business Intelligence Development Studio from Start –> Programs –> SQL Server 2005 –> SQL Server Business Intelligence Development Studio.
Click File –> New –> Project, and select project type ‘Import Analysis Service 9.0 Database’.
image
Click OK, then a wizard will guide you through to import the database.
image
image


image
Based on the query to build the ‘NEWWIP’ dataset:
“… 
SELECT {[MEASURES].[INDICATOR],KPIValue("Actual vs Budget Net WIP"), KPIGoal("Actual vs Budget Net WIP"),
KPIStatus("Actual vs Budget Net WIP"), KPITrend("Actual vs Budget Net WIP")} ON COLUMNS
FROM [Project Accounting Cube]
…”
The dataset is built upon [Project Accounting Cube].
Select ‘[Project Accounting Cube] in the solution explorer. and choose ‘View designer’.
image
Change to ‘KPI’ tab,and choose the ‘Actual vs Budget Revenue’
image
From the Value Expression, we can see the KPI is defined on the basis of measure ‘Budget Revenue’ and measure  ‘Actual Revenue’.
(([Measures].[Budget Revenue]-[Measures].[Actual Revenue])/[Measures].[Budget Revenue])*100
Expand the Measures in the ‘Metadata’ tab of the ‘Calculation Tools’ section.
image
OOPS!!!!!! Can’t find the ‘Budget Revenue’ measure!!!!!!!
image 
Okay, now we see, must be something wrong with the ‘Budget Revenue’ measure. Change to ‘Calculation’ tab in the same ‘Project Accounting Cube’.
Choose the ‘Budget Revenue’ Calculated Member.
image  


It is derived from another two Calculated Members – ‘Budget invoiced revenue’ and ‘Budget accrued revenue’.
Expression for ‘Budget invoiced revenue’:
image
Expression for ‘Budget accrued revenue’:
image
Both of them are based on the Dimension ‘Budget updates’, and they do not appear under the Measures as well, which means the data are not populated properly.
image
Expand the ‘Budget updates’ in the Metadata tab.
image


Here we go, we found the cause of the issue. No transactions in the Budget Update dimension.
Go to the Solution explorer, and edit the ‘Budget updates’ dimension.
image 
image
From the property, we can see the ENUM ProjTransType is from the following query:
SELECT A.ENUMITEMVALUE, A.ENUMITEMLABEL AS ENUMITEMNAME FROM [DBO].SRSANALYSISENUMS A WHERE A.ENUMID = 383 AND A.LANGUAGEID = ‘en-us’
Let us check this table from the SQL Server Management Studio. All good, the records are there. (We need to check the ProjPaymentStatus, LedgerPostingType as well.)
image
Then must be the issue that the dimension is not processed properly.
From the expression for ‘Budget invoiced revenue’ :
Sum
(
    (
        {[Budget updates].[Transaction type].&[1], [Budget updates].[Transaction type].&[2],
        [Budget updates].[Transaction type].&[3], [Budget updates].[Transaction type].&[4]},
        [Budget updates].[Posting type].&[126]
    ),
    [Measures].[Budget updates Amount] *-1
)
+
Sum
(
    (
        {[Budget updates].[Transaction type].&[5]},
        [Budget updates].[Posting type].&[127]
    ),
    [Measures].[Budget updates Amount] *-1
)
Make sure in the ProjTransBudget table (Dimension [Budget updates]) contain the records meeting the above criteria.
Process the ‘Budget updates’ dimension now.
image
image
Click ‘Yes’ button.
image


image
Click Run to process the ‘Budget updates’ dimension.
image
Reconnect again
image
Reload the role center…
image

No comments:

Post a Comment