Refresh Caller form From another form in dynamics ax 2009

--Class Declaration method contains

public class FormRun extends ObjectRun
{
        FormDataSource                              fds;
}

--Init Method contains

fds  = element.args().record().dataSource();

--for resfreshing the caller form execute the following code

fds.executeQuery();
fds.refresh();

Fetching Records Using Query Object

my previous post “Playing with data”, I have made you guys learn how to retrieve data using the select query which you can write anywhere. You just need to declare the table buffer for that. However, there are some cases where it is not recommended to use the select statement to retrieve data for example it is not recommended to use select statement at Form level methods. So, what could be used to retrieve data? The answer is Using Query Object. Normally queries are stored in AOT (see Queries Node in AOT), but they can also be created from code dynamically. The Query object is used when a user interaction is required, like user wants to add range or sort the data.
In order to create query from code, the objects that needs to be created are,

• Query:
  
  The query class object defines the structure of the query. Objects of this type are not used for fetching records from the database. Instead, use a QueryRun object that may be assigned a query object.
  
  
• QueryBuildDataSource :
  
  The QueryBuildDataSource class provides the building blocks that queries are made of. It holds the tables that needed to be added in the query. The hierarchy of the datasources of query are defined in this object. Like if query has a join with another table, so child table is added using the QueryBuildDatasource object of parent table.
• QueryRun:
  
  The QueryRun class traverses tables in the database while fetching records that satisfy constraints given by the user, and helps gather such constraints from user input.
  
  

The object to add the range to the query is,

• QueryBuildRange:
  
  The QueryBuildRange class represents the ranges that define which records should be fetched from the data source in which the QueryBuildRange is associated. The value property can be used to set the string that defines the range.
  
  

Now, lets have a code example how to use the objects of these classes. Create a new job and add the following code.

static void CustTableSales(Args _args)

{

Query query;

QueryBuildDataSource qbds1;

QueryBuildDataSource qbds2;

QueryBuildRange qbr1;

QueryBuildRange qbr2;

QueryRun queryRun;

CustTable custTable;

query = new Query();

qbds1 = query.addDataSource(tablenum(CustTable));

qbr1 = qbds1.addRange(fieldnum(CustTable, Blocked));

qbr1.value(queryvalue(CustVendorBlocked::No));

qbr2 = qbds1.addRange(fieldnum(CustTable, CustGroup));

qbr2.value(queryvalue(’10′));

qbds2 = qbds1.addDataSource(tablenum(SalesTable));

qbds2.joinMode(JoinMode::ExistsJoin);

qbds2.addLink(fieldnum(CustTable, AccountNum), fieldnum(SalesTable, CustAccount));

queryRun = new QueryRun(query);

while (queryRun.next())

{

custTable = queryRun.get(tablenum(CustTable));

info(strfmt(“%1 – %2″,custTable.Name,

custTable.AccountNum));

}

}

This job runs the query that selects all active customers who belong to group 10 and have at least one sales order. To set the range value a global function “queryValue(anyType _anyType)” is used which takes a value of any type. There is another static function in the SysQuery class, e.g SysQuery::value().
If the addRange method is called for same field then an OR is added to where clause and if the addRange is called for different field the AND is added to the where clause in the query.
To add OR in the where clause in different field, you can make the expression using the strfmt() global function as,

qbr2.value(strfmt(‘((%1 = “%2″) || (%3 = “%4″))’,

fieldstr(CustTable,CustGroup),

queryvalue(’10′),

fieldstr(CustTable,Currency),

queryvalue(‘EUR’)));

Yes, you can make the expression as you like using the strfmt(), you can use greater than, less than in the query as,

qbr3.value(strFmt(‘(ModifiedDate > %1)’, Date2StrXpp(01012000)));

One thing which should be noted here is the use of Date2StrXpp() global function. It is really a need for a range on date field to use this function. Without this function the query will not be executed correctly and wrong results will be displayed. So, whenever there is a need of applying range on Date field use Date2StrXpp() function.

There are a lot to discuss about query, which cannot be covered in a single post. And it will be boring if I mention everything about query in single post. So wait for new post to explore more about Query Object.
Thanks guys, comments and suggestion will be appreciated.

Insert_recordset, Update_recordset, and delete_from single transaction command.

In AX, you can manipulate a set of data by sending only one command to the database. This way of manipulating data improves performance a lot when trying to manipulate large sets of records. The commands for manipulations are insert_recordset, update_recordset, and delete_from. With these commands, we can manipulate many records within one database transaction, which is a lot more efficient than using the insert, update, or delete methods.
Lets discuss about these commands one by one.

• Insert_recordset
  
  A very efficient way of inserting a chunk of data is to use the insert_recordset operator, as compared to using the insert() method. The insert_recordset operator can be used in two different ways; to either copy data from one or more tables to another, or simply to add a chunk of data into a table in one database operation.
  The first example will show how to insert a chunk of data into a table in one database operation. To do this, we simply use two different table variables for the same table and set one of them to act as a temporary table. This means that its content is not stored in the database, but simply held in memory on the tier where the variable was instantiated.
  static void Insert_RecordsetInsert(Args _args)
  {
  CarTable carTable;
  CarTable carTableTmp;
  
  
  /* Set the carTableTmp variable to be a temporary table.
  This means that its contents are only store in memory
  not in the database.
  */
  carTableTmp.setTmp();
  // Insert 3 records into the temporary table.
  carTableTmp.CarId = “200″;
  carTableTmp.CarBrand = “MG”;
  carTableTmp.insert();
  carTableTmp.CarId = “300″;
  carTableTmp.CarBrand = “SAAB”;
  carTableTmp.insert();
  carTableTmp.CarId = “400″;
  carTableTmp.CarBrand = “Ferrari”;
  carTableTmp.insert();
  /* Copy the contents from the fields carId and carBrand
  in the temporary table to the corresponding fields in
  the table variable called carTable and insert the chunk
  in one database operation.
  */
  Insert_Recordset carTable (carId, carBrand)
  select carId, carBrand from carTableTmp;
  }
  The other, and perhaps more common way of using the insert_recordset operator, is to copy values from one or more tables into new records in another table. A very simple example on how to do this can be to create a record in the InventColor table for all records in the InventTable.
  static void Insert_RecordsetCopy(Args _args)
  {
  InventColor inventColor;
  InventTable inventTable;
  This material is copyright and is licensed for the sole use by ALESSANDRO CAROLLO on 18th December
  Chapter 6
  [ 169 ]
  InventColorId defaultColor = “B”;
  Name defaultColorName = “Blue”;
  ;
  insert_recordset inventColor (ItemId, InventColorId, Name)
  select itemId, defaultColor, defaultColorName
  from inventTable;
  }
  The field list inside the parentheses points to fields in the InventColor table.
  The fields in the selected or joined tables are used to fill values into the fields in
  the field list.
  
  
• Update_recordset
  
  The update_recordset operator can be used to update a chunk of records in a table in one database operation. As with the insert_recordset operator the update_recordset is very efficient because it only needs to call an update in the database once.
  The syntax for the update_recordset operator can be seen in the next example:
  static void Update_RecordsetExmple(Args _args)
  {
  CarTable carTable;
  ;
  info(“BEFORE UPDATE”);
  while select carTable
  where carTable.ModelYear == 2007
  {
  info(strfmt(“CarId %1 has run %2 miles”,
  carTable.CarId, carTable.Mileage));
  }
  update_recordset carTable setting Mileage = carTable.Mileage + 1000
  where carTable.ModelYear == 2007;
  info(“AFTER UPDATE”);
  while select carTable
  where carTable.ModelYear == 2007
  {
  info(strfmt(“CarId %1 has now run %2 miles”,
  carTable.CarId, carTable.Mileage));
  }
  }
  
  
  When this Job is executed it will print the following messages to the Infolog:
  
  Notice that no error was thrown even though the Job didn’t use selectforupdate, ttsbegin, and ttscommit statements in this example. The selectforupdate is implicit when using the update_recordset, and the ttsbegin and ttscommit are not necessary when all the updates are done in one database operation. However, if you were to write several update_recordset statements in a row, or do other checks that should make the update fail, you could use ttsbegin and ttscommit and force a ttsabort if the checks fail.
  
  
• Delete_from
  
  As with the insert_recordset and update_recordset operators, there is also an option for deleting a chunk of records. This operator is called delete_from and is used as the next example shows:
  
  
  static void Delete_FromExample(Args _args)
  {
  CarTable carTable;
  
  
  delete_from carTable
  where carTable.Mileage == 0;
  }
  
  
  Thanks for reading the post, any comments or questions are welcomed. Keep visiting the blog.

Example of insert recordset in to table using axaptaExample of insert recordset in to table using axapta

syntax of insert recordset is

insert_recordset DestinationTable ( ListOfFields )

select ListOfFields1 from SourceTable [ where WhereClause ]

[ join ListOfFields2 from JoinedSourceTable

[ where JoinedWhereClause ]]

example of insert recordset is

insert_recordset TableName (field1, field2)
    select field1, field2 
        from table2 
                where abc<= 1000;

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…