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

Advertisements
This entry was posted in SSAS & BI. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s