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.
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.
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’.
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’.
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.
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.
Press F5 to execute the query, put in company account value and click OK.
The same error as on the portal.
- 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’.
Click OK, then a wizard will guide you through to import the database.
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’.
Change to ‘KPI’ tab,and choose the ‘Actual vs Budget Revenue’
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.
OOPS!!!!!! Can’t find the ‘Budget Revenue’ measure!!!!!!!
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.
It is derived from another two Calculated Members – ‘Budget invoiced revenue’ and ‘Budget accrued revenue’.
Expression for ‘Budget invoiced revenue’:
Expression for ‘Budget accrued revenue’:
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.
Expand the ‘Budget updates’ in the Metadata tab.
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.
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.)
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.
Click ‘Yes’ button.
Click Run to process the ‘Budget updates’ dimension.
Reconnect again
Reload the role center…
Fred Shen's AX weblog 