Wednesday, May 27, 2015

Error when validate settings in Report servers by any admin account which is not account used to install the AX reporting services extensions.

First thing you need to verify the Service account and Execution Account are properly configured under Reporting Services Configuration Manager.

After some troubleshooting, I found UAC was not turned off. I was running on MS Windows Server 2012.
UAC has to be turned off via registry by changing the DWORD "EnableLUA" from 1 to 0 in "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system". You will get a notification that a reboot is required. After the reboot, UAC is disabled.
After UAC is disabled, the issue is resolved.


How to create Info part and use it on List page

This post is about how to create info Part in AX and use it on list page.

For this example; I am using Projects List page and will add Revenue Info Part on it.

Right click on AOT\Parts\Info Parts and create New Info Part


















Assign Query to this info part I have assigned ProjTable_NoFilter query
















Add New Group under Layout
























Name it ProjectRevenue and set few properties as shown













Note I haven't assigned DataSource to this group, I will be using data methods to get data here.

Add new fields under ProjectRevenue group and name it RevenueAtSales and RevenueAtOrders












I assigned ForecastRevenueAtSalesSw data method to field RevenueAtSales, this data method is written on at \Data Dictionary\Tables\ProjTable\Methods\forecastRevenueAtSalesSw

display AmountCur forecastRevenueSw()
{
    ProjForecastOnAcc projForecastOnAcc;
    real total;

    // Sum all the prices with quantity of 1.
    select sum(SalesPrice) from projForecastOnAcc
        where projForecastOnAcc.ProjId == this.ProjId
           && projForecastOnAcc.Qty == 1;

    total = projForecastOnAcc.salesPrice;

    // Support non-unity quantities.
    while select SalesPrice, qty from projForecastOnAcc
        where projForecastOnAcc.ProjId == this.ProjId
           && projForecastOnAcc.Qty != 1
    {
        total += (projForecastOnAcc.salesPrice * projForecastOnAcc.qty);
    }

    return total;
}

Add this Info part to ProjProjectsListPage as I want to see this on Project List page.

Right click on Parts node in ProjProjectsListPage























Name it FF_ProjectRevenue



















Set IsLinked Property to YES, this property will link this part to the list page based on Datasource (ProjTable)















Create new display menu item and name it FF_ProjectRevenueMenuitem












Assign this menu item as Part property in ProjProjectListPage
















Time to see this info Part on List page, Open All Projects form and here it goes










Happy Daxture!ng

Thursday, May 21, 2015

Cannot open the datafile file axapden-us.alt

Faced following error after restoring Dynamics AX databases from one AOS server to other.


Cannot open the datafile "C:\Program Files\Microsoft DynamicsAX\60\Server\DAX2012R3\bin\Application\appl\Standard\axapden-us.alt"
Error code from the openrating system: errno = 13.
Look in file 'errno.h' or in the operating system documentation for an explanation. 


Resolution:
It was the account permssion issue. 
Make sure AOS service account must have added into Dynamics AX databases with full permissions (at least db_owner). This AOS service account manages the files located at above mentioned path in error.

In my Case;
Source AOS service was running with domain user account (DOMAIN\AOSService) and Destination AOS service was running under Network Service account. When I restored AX databases from source to destination it overwrite all user accounts in destination database, which means it deleted Network Service account.  
I had two options EITHER to add Network Service account which already has all the permissions as AX was installed from this account as mentioned above OR I have to provide full permissions to the domain user account (DOMAIN\AOSService) in destination Dynamics AX databases.


Export Import Model - Power Shell & Command line statements

I myself often require following commands and worth to have these at one place to get it more quickly. 

Export a Model File
Windows PowerShell
Export-AXModel -Model <name> -File <Filename.axmodel>
Example: daxture is a model name and it exist in C drive
Export-AXModel -Model daxture -File C:\daxture.axmodel

Export-AXModel -Server SERVERDB -Database DataBaseName –Model "Model Name" -File c:\temp\xxxxx.axmodel

AXUtil Command
axutil export /model:<modelname> /file:<filename> /verbose
Example: daxture is a model name and it exist in C drive
axutil export /model:"daxture" /file:"C:\daxture.axmodel" /verbose

Import a Model File
Windows PowerShell
Install-AXModel -File <Filename.axmodel> -Details
Example: daxture is a model name and it exist in C drive
Install-AXModel -File C:\daxture.axmodel -Details

Install-AXModel -Server SERVERDB -Database DatabaseName -File c:\temp\xxxxx.axmodel -DETAILS -Conflict Overwrite

In case of conflict we can use this command; Intall -AXModel -File <Filename.axmodel> -Details -Conflict Push

AXUtil Command
axutil import /file:<filename> /verbose
Example: file exist in C drive
axutil import /file:"C:\daxture.axmodel" /verbose


AxUtil commands
Creating a model in layer axutil create /model:"daxture" /Layer:CUS
Delete a model axutil delete /model:"daxture"
Delete a layer axutil delete /layer:ISV
Delete a layer from database and AXServer axutil delete /layer:ISV /db:<database> /s:<server>

Wednesday, May 20, 2015

Calculate timing in X++ operations [WinAPI::getTickCount()]

While reading some lengthy operations e.g. reading data from CSV files, fetching data in query and uploading data etc I wanted to know the time consuming in each opertion. This helped me to optimize my logic or query in making client-server calls.
We can use following function to know about time consumption;

int                 startTime, endTime;

startTime = WinAPI::getTickCount();
[Perform X++ operations]
endTime = WinAPI::getTickCount();

info(strFmt('It took %1 minutes', ((endTime - startTime)/1000)/60));

Note: WinAPI::getTickCount() tells time in milliseconds you need to change accordingly as per your required unit.

Building query AND using query object in X++

Create and add datasource with range in X++
// Code using X++ to build the query
Query                   query;
QueryRun                queryRun;
QueryBuildDataSource    qbds;
ProjTable               ProjTable;
;

query    = new Query();
// Add a datasource to the query
qbds     = query.addDataSource(tableNum(ProjTable));
// Add a range to the newly added datasource.
qbds.addRange(fieldNum(ProjTable, ProjId)).value("00403_1036..00412_1036");
   
queryRun = new QueryRun(query);
   
while(queryRun.next())
{
   projTable  =   queryRun.get(tableNum(ProjTable));
   info(projTable.ProjId + ", " + ProjTable.Name);
}



Use query object to retrieve AOT query
// Code using a query string
static void UseAOTQuery(Args _args)
{
    Query                           query;
    QueryRun                        queryRun;
    QueryBuildDataSource            qbds;
    QueryBuildRange                 qbr;      
    ProjTable                     projTable;   
    
    query = new query(queryStr(ProjTable));
    queryRun = new QueryRun(query);
    
    while (queryRun.next())
    {
        projTable= queryRun.get(tableNum(ProjTable));
        
        info (strFmt("%1 - %2", ProjTable.ProjId, ProjTable.Name));
    }   
}

Monday, May 18, 2015

Conditional IIF Sum in SSRS report at group level

I got a requirement where I want to show sum amount based on differnent conditions.

Simple Sum of amount can be shown with following expression;
=Sum(Fields!Amount.Value)

I tried following expression to show conditional sum on a group level; It did not work :(
=Sum(IIF(Fields!name.value = "Standard", Fields!Amount.value, 0))


I tried following expression after getting idea from this blog and it worked
=Sum(VAL(IIF(Fields!name.value = "Standard", Fields!Amount.value, 0)))

Use ReportItems in SSRS report

Sommetime we require to use values from textboxes in SSRS for some calculations or data hiding and some other manipulations. Textbox or any other object on report design is an item of the report and can be accessed through ReportItems!TextBox.value

This can be used as on textbox expression =ReportItems!Amount.Value - ReportItems!Amount1.Value


Count records in Query Vs Cound Loops in Query

Sometime we do need to know the number of records fetching through X++ query and this can be achieved by SysQuery::CountRecords(QueryRun) function. 

This function works similar to this X++ logic;

ProjTable   projTable;    
select count(RecId) from projTable;info(strFmt("%1 total records", projTable.RecId)); // total tecords 1130

Lets see with X++ query;

static void CountProjTableRecords(Args _args)
{      
    Query                           query = new Query();    
    QueryRun                     queryRun;    
    QueryBuildDataSource qbd;    
    qbd = query.addDataSource(tablenum(ProjTable));
   
    queryRun = new QueryRun(query);
   
    info(strfmt("Total Records in Query %1", SysQuery::countTotal(queryRun))); 
// total tecords 1130
}

However, this SysQuery::CountRecords(QueryRun) function count only the number of records of the first datasource. Let's assume if we need to count number of records of a query with more than one datasource, we need to use SysQuery::CountLoops(QueryRun) function.

static void CountProjTableRecords(Args _args)
{      
    Query                           query = new Query();    
    QueryRun                     queryRun;    
    QueryBuildDataSource qbds, qbds1;    
    qbds = query.addDataSource(tablenum(ProjTable));
    
    qbds1 = query.addDataSource(tablenum(ProjTable)).addDataSource(tablenum(ProjForecastTable));    
    queryRun = new QueryRun(query);
   
    info(strfmt("Total Records in Query %1", SysQuery::countLoops(queryRun))); 
}

Show last day of the month from date in SSRS report

Following expression can be used to show last day of the monthin SSRS report.


Format(DateSerial(Year(Parameters!Dataset1_AsPerDate.Value), Month(Parameters!Dataset1_AsPerDate.Value),"1").AddMonths(1).AddDays(-1),"dd/MM/yyyy")

Input:  Parameters!Dataset1_AsPerDate.Value = "02/01/2015"
Outout: 31/01/2015

Show AX full company in SSRS

Following expression can be used to show full AX company name from legal entities in SSRS report.


=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.GetFullCompanyNameForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value)

Show Print Date & Time and Page Number in SSRS report - Custom formatted

Following expression can be used to show print date and time in SSRS report.


=Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value,Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "d", Parameters!AX_RenderingCulture.Value) & " at " & Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, System.DateTime.UtcNow, "t", Parameters!AX_RenderingCulture.Value)

OutPut: 
        01/01/2015 at 8:14 AM


Following expression can be used to show page number in SSRS report.

=System.String.Format(Labels!@SYS182565, Globals!PageNumber & space(2) & Labels!@sys26401 & space(2) & Globals!TotalPages)
                  
OutPut: Page 1 of 2

Format a date in SSRS report

After adding a Date parameter in contract class of SSRS report it starts showing in report's parameters node with Data type DateTime.























There is no option to make it either short date or format a date at parameter level. You can use following expression in report design where this parameter has been used in design, mostly in textbox.

=format(Parameters!Dataset1_AsPerDate.Value,"dd/MM/yyyy")


Happy Daxur!ng

Thursday, May 14, 2015

The data source is not embedded within a (parent) data source.

This error comes when you try to add new datasource to the top of the query.

QueryBuildDataSource qbds;
qbds = query.addDataSource(tableNum(newTable)); // This is wrong

You should add new table to the Main datasource instead.

query.dataSourceTable(tableNum(newTable)).addDataSource(..) // This is correct

AOT and X++ queries and ranges in AX 2012

Let's say we have a query in AOT \Queries\ProjTable














And now we want to add more datasources into this query's parent datasource (ProjTable) and some ranges etc.

Let's assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on.

I created a SRS report data provider class for this example;

Class declaration
[
    SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)),
    SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw))
]
public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase
{
    Query                       query;
    TempTable               tempTable;
    RecordInsertList       recordInsertListTmpTable;
    ProjTable                  projTable;
    TransDate                 AsOfDate;

}

ProcessReport
[SysEntryPointAttribute]
public void processReport()
{
    // Query variable declaration
    QueryRun                            queryRun;

    // Contract class declaration
    MarginAnalysisReportContractV2Sw    dataContract;

    // Contract class parameters
    ProjDateCriteriaSw                  dateType;

    // Views declaration
    ProjTableDateViewSw                 projTableDateViewSw;

    // This will retrieve the above ProjTable query
    // Select * from ProjTable
    query = this.parmQuery();

    dataContract  = this.parmDataContract();
    dateType        = dataContract.parmDateType();
    asOfDate        = dataContract.parmAsOfDate();

    this.addParameterRanges();

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        projTable = queryRun.get(tablenum(ProjTable));
        this.insertTmpTable(ProjTable);
    }
}

addParameterRanges() Method
private void addParameterRanges()
{
    QueryBuildDataSource    qbds1;
    QueryBuildRange            qbr1;

// add ProjTableDate table into query's parent datasource projtable
qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate));
// defining relation based on ProjId
qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId));
// defining join mode
qbds1.joinMode(JoinMode::ExistsJoin);
// adding range on ActualEndDate field of ProjTableDate table
// setting value <= asOfDate which is considered as per date
qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate));
}

Resultant X++ Query
SELECT * FROM ProjTable(ProjTable) 
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1) 
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND 

      ((ActualEndDate<={ts '2015-05-14 00:00:00.000'}))


Adding OR and AND in query
QueryBuildRange                 qbr;
qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate));
qbr.value(strFmt('((%1 != %2) || (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));

qbr.value(strFmt('((%1 != %2) && (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));
Using enum value in query as a range
qbr.value(strFmt('(Status == %1)', any2int(ProjStatus::active)));

Check modifiedDate value of the table
qbr.value(strFmt('(ModifiedDate > ProjTable.ModifiedDate)'));

Using wildcards in query
You can use LIKE keyword for wildcards in ranges
qbr.value(strFmt('(ProjName LIKE "*Builder*")'));