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.

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…

Dynamics AX 2009: Write to eventlog entry

Our client requested to keep track of all Dynamics AX system errors during the User Acceptance Testing. I use the application event logs to store the information.
The following code shows you how to write event log entry with X++:
Create a new class AX_EventLog with a static method WriteEventLog:
static void WriteEventLog(Exception _exception, str _event)
{
    str eventSource = "AX event";
    str logType = "Application";
    System.Diagnostics.EventLogEntryType eventLogEntryType;
    int eventCategory = 9999;
    ;
    switch(_exception)
    {
        case Exception::Info:
            eventLogEntryType = System.Diagnostics.EventLogEntryType::Information;
            break;
        case Exception::Warning:
            eventLogEntryType = System.Diagnostics.EventLogEntryType::Warning;
            break;
        default:
            eventLogEntryType = System.Diagnostics.EventLogEntryType::Error;
    }
    if (!System.Diagnostics.EventLog::Exists(eventSource))
    {
        System.Diagnostics.EventLog::CreateEventSource(eventSource, logType);
    }
    System.Diagnostics.EventLog::WriteEntry(eventSource, _event, eventLogEntryType, eventCategory);
}
In the Info class,

Exception add(
    Exception _exception,
    str _txt,
    str _helpUrl = ”,
    SysInfoAction _sysInfoAction = null,
    boolean buildprefix = true)
{
    …
    AX_EventLog::WriteEventLog(_exception, _txt);
    …
}
Here we go, event log entry…

Send workflow notification as email

In AX2009, we can email the workflow notifications. The following steps will show you how to set this up:
1. Create an email template for workflow.
Go to Basic –> Setup –> Email Templates. Create a new email template for workflow notification.

2. Set workflow to use the template that is created from step 1.
Go to Basic –> Setup –> Settings for workflow. Select the email template on the General tab.

3. Enable “Send notifications as e-mail message” for the user.
Tools –> Options.

How to show or hide query ranges on dialogs in Dynamics ax

Sometimes, we create dialogs which have a query associated with them. We use this query to filter records and show us some data. Often, associating such queries with dialogs adds ranges to the dialogs. But do we really need these ranges always? In this post, let us look at one of the reason why ranges may get added and how to hide them.

Let us create a simple Query as shown in the figure below. Nothing fancy about it. Just SalesTable and SalesLine joined.

Now we will create a dialog, set the dialog's query with the query we created above and run it.

When the dialog opens, this is how it looks.

Notice now that there are two SalesId ranges. These come from SalesTable and SalesLine respectively.

The logic to add the range here is that the first active index on the tables are displayed. Take a look at both SalesTable and SalesLine.

On SalesTable, the first index is SalesIdx and on SalesLine, the first index is SalesLineIdx.

Before we move further, some of you may question that SalesLineIdx has threee fields - SalesId, LineNum & RecId. So why is only SalesId shown as a range. Well, the answer is that for all child datasources, only the first range is shown.

Now back to the topic, I want to hide the SalesLine's SalesId range on the dialog. How can I do that?

The answer is to override the showIndexFields method on the dialog and return false. Let us try that.

view source

print?

1.public boolean showIndexFields(tableId id)

2.{

3.    boolean ret;

4.  

5.    ret = true;

6.  

7.    return ret;

8.}

Well, the fields have been hidden but the labels are still showing. This is a bug in the RunBaseDialogModify class which hasn't been fixed yet even in the latest roll up. If you are interested in fixing this, you will have to move the code on lines 26 & 27 in \Classes\RunBaseDialogModify\addQueryDatasource() inside the condition on line 75.

Coming back to our original requirement, I just want to hide the range for the SalesLine table. If you look closely at the showIndexFields() method's signature, it passes a tableId value. We can use this value to decide which tables to show the ranges for and which to disallow.

view source

print?

01.public boolean showIndexFields(tableId id)

02.{

03.    boolean ret = true;

04.      

05.    switch(id)

06.    {

07.        case tableNum(SalesLine):

08.            ret = false;

09.            break;

10.    }

11.  

12.    return ret;

13.}

As you can see from the below image now, only the range on SalesTable is visible now. The SalesLine range's label is still visible which is due to the bug in code I mentioned above.

In this post, we learnt how we can hide the ranges of certain tables by overriding the showIndexFields() methods.

You can download the project I have used in this post from here

Table override methods in AX

In this post, I will add the Override methods available to the table Airline.  Tables in AX have 29 methods that can be overridden. Open the shared project Flight_Stats and expands the nodes Data Dictionary / Tables / Airline / Methods. Right-click on the node Methods and select Override Method. Then click on the first override method aosValidateDelete.

Inside the method aosValidateDelete, I added the line info(‘aosValidateDelete’); in order to study when this particular method is triggered.

Similarly, I added the below mentioned 29 override methods to the table Airline. These override methods are available on any given AX table:

1. aosValidateDelete
2. aosValidateInsert
3. aosValidateRead
4. aosValidateUpdate
5. caption
6. clear
7. delete
8. equal
9. getPresenceFieldData
10. helpField
11. initValue
12. initValue
13. isFormDataSource
14. merge
15. modifiedField
16. new
17. postLoad
18. renamePrimaryKey
19. reread
20. toolTipField
21. toolTipRecord
22. toString
23. update
24. validateDelete
25. validateField
26. validateWrite
27. wait
28. write
29. xml

I added the info statement to all the above methods similar to how I added it to the method aosValidateDelete. Except the following methods required some additional changes:

new method required the lone semicolon to avoid the Syntax error.


toString method was throwing the error Super() not allowed here. Hence, that line had to be commented out. Also, added the lone semi colon to avoid the Syntax error.


wait method was throwing the error Super() not allowed here. Hence, that line had to be commented out. Also, added the lone semi colon to avoid the Syntax error.


Now that I have added these info statements, I am planning to study how these methods work using Table browser.

Creating a table in AX

Right-click on Tables under Data Dictionary node and select New / Table.

The new table object will be created with the name Table1. Right-click on the newly created table object Table1 and select the option Open or you can press Ctrl + O.

You should get the error message Cannot select a record in Table1 (Table1). The SQL database has issued an error.

The reason for the error is because the object has been created in AX but has not been created in the SQL Server database. Let’s use the SQL Server Management Studio (SSMS) to look into the list of tables in the AX database. I have shown here only the tables between alphabets S and T. You can notice that there is not object named Table1.

Let’s save the table in the AOT. To save the the object in AX, right-click on the object Table1 and select the option Save or press Ctrl + S.

Now, if we go back to SSMS you can see that the object Table1 has now been created in the AX database.

If we expand the newly created table Table1 in the SSMS, we can see the following nodes namely Columns, Keys, Constraints, Triggers, Indexes and Statistics.

• AX creates new tables with three fields namely DataAreaId, RecVersion and RecId.
• DataAreaId column will store the company code to which a record belongs to.
• RecVersion column contains the version of the record in the table.
•  RecId column contains the unique identification number of the record.
• The constraint that begins with CK is the check constraint to make sure that the RecId doesn’t contain the value zero.
• First default constraint is created to store the value dat in the field DataAreaId, which is then actually updated with the appropriate company account id value in which the record is created.
• Second default constraint is created to store the value 1 in the field RecVersion. The value 1 is always stored in the field RecVersion whenever a new record is created in the tables. If the record in the table is updated, the value in the field RecVersion is replaced with a version number. We will get to learn about that as we proceed with the Flight Stats application development.
• In addition to the constraints, a unique non-clustered index containing the fields DataAreaId and RecId is created to keep the values in these fields unique.

Let’s go back to the AX application and open the newly created table Table1 again. You should see the Table browser as shown below with the three automatically created columns that we just discussed earlier. Of course, the table is empty!

We need to rename this table because it should be called Airline and not Table1. To rename the table, right-click on it and select Rename option.

Rename the table to Airline.

Once the table is renamed and saved, the table in the SQL Server database will be synchronized.