Power BI ile yapılan raporlarda Baz para birimi dönüşümü, bir önceki yılda içinde bulunduğumuz ayda ki toplamtutar yada adet bazlı hesplama

Günlük döviz kurları alındıktan sonra raporda baz tutar dönüşüm yapılarak hesaplanır 

BazTutarEur = IF(AxR3BankBalance[Para Birimi] ="EUR"; AxR3BankBalance[PTutar]; IF(AxR3BankBalance[Para Birimi] ="TRY";

AxR3BankBalance[PTutar]/ SUMX(FILTER(Kurlar;Kurlar[CurrencyKod]="EUR");Kurlar[Alis]);AxR3BankBalance[PTutar] / SUMX(FILTER(Kurlar;Kurlar[CurrencyKod]="EUR");Kurlar[EurParite])))

Bu yıl ve bu ay değerlerini raporlarda lıyoruz ancak bu ay daki değerleri geçen senenin aynı ayı ile karşılaştırmak istediğimizde

Geçen Yıl Bu Ay = CALCULATE(COUNT(AxR3SalesRprt[SALESID]);YEAR(TODAY())-1= AxR3SalesRprt[Yil];MONTH(TODAY())= AxR3SalesRprt[Ay])

Multi select controller, çoklu seçim kontrolü

SalesTable salestablelocal;
  int sayac;



MultiSelectionHelper helper = MultiSelectionHelper::construct();

helper.parmDatasource(SalesTable_ds);

salestablelocal = helper.getFirst();
  while (salestablelocal.RecId != 0)




{

info(salestablelocal.SalesId);

sayac++;

salestablelocal = helper.getNext();

}
  if (sayac>1)



{
  throw error("@SYS18447");



}

AX 2012 için ücretsix editör düzenleyici (Editor Extensions for AX 2012)

ilk Adım JAEE.AX.EditorExtensions-b0.2 isimli dll dosyalarını indirelim, axapta yı kapatalım

2. adım axaptanın kurulu olduğu bilgisayarda "C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\EditorComponents" klasörün altına kopyalayalım

3. adım test edelim.

ek kaynaklar:
http://yetanotherdynamicsaxblog.blogspot.com/2013/03/free-editor-extensions-for-ax2012.html
https://axatoz.wordpress.com/2014/08/31/free-x-editor-extensions-for-ax-2012-setup-under-5-minutes/

MS SQL, SQL, TSQL, BACKUP Sql kodu ile yedekleme

 

DECLARE @name varchar(50) -- database name

DECLARE @path varchar(256) -- yedek dosya/klasör yolu

DECLARE @filename varchar(256) -- yedek dosya adı

DECLARE @filedate varchar(20) -- tarih

SET @path = 'E:\yedek\'

SET @filedate = CONVERT(VARCHAR(20), GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR 

SELECT name FROM master.dbo.sysdatabases where name like 'CKR_A%'  -- not in ('master','model','msdb','tempdb')

-- AND state = 0 -- database is online

-- AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @filename = @path + @name +'_' + @filedate + '.bak'

BACKUP DATABASE @name TO DISK = @filename WITH STATS=10, COMPRESSION

  FETCH NEXT FROM db_cursor INTO @name

END

close db_cursor 

deallocate db_cursor

satır hücre renklendirme

public void displayOption(Common _record, FormRowDisplayOption _options)

{

   CPMTenderReplyCompare localTable = _record;

   Qty  minA,maxA;

   ;

 

    //indexe gore satırdaki ilgili hucreyi renkelendireme

    switch(pMin)

    {

       case 1:

           _options.textColor(WINAPI::rgbCon2int([255, 0, 0]));

           _options.affectedElementsByControl(Grid_ComparePrice1.id());

       break;

       case 2:

           _options.textColor(WINAPI::rgbCon2int([255, 0, 0]));

           _options.affectedElementsByControl(Grid_ComparePrice2.id());

       break;

 

    }

 

    //burdaki ifler istenilen koşula gore satirlari renklendiriyor

    minA = localTable.DispCostAmount-localTable.DispCostAmount*0.15;

    maxA = localTable.DispCostAmount+localTable.DispCostAmount*0.15;

 

    if(localTable.CompareAmount1)

    {

      if(localTable.CompareAmount1 < minA || localTable.CompareAmount1 > maxA )

      {

         _options.backColor(WinApi::RGB2int(50,255,50));//Green

         _options.affectedElementsByControl(Grid_CompareAmount1.id());

      }

    }

  

    if(localTable.CompareAmount2)

    {

      if(localTable.CompareAmount1 < minA || localTable.CompareAmount2 > maxA )

      {

         _options.backColor(WinApi::RGB2int(50,255,50));//Green

         _options.affectedElementsByControl(Grid_CompareAmount2.id());

      }

    }

 

    super(_record, _options);

 

}

Export all shared projects, Projelerin dışarı aktarımı xpo

Ax 2012 r3, x++ 

paylaşılan projelerin xpo olarak dışarı aktarımı.

Export all shared projects

Following code exports all projects in the Shared node. For each run it creates a new folder and then the projects are individually exported to xpo.

 

static void ExportAllProjects(Args _args)  
{  
 ProjectNode      sharedProject = Infolog.projectRootNode().AOTfindChild("Shared");  
 TreeNodeIterator   treeIterator;  
 TreeNode        childNode;  
 str           folderPath = @"C:\\AXProjects\\";  
 str           folderName;  
 System.DateTime    dateTime = DateTimeUtil::newDateTime(systemDateGet(), timeNow());  

 void exportProject(TreeNode _projectNode)  
 {  
  FileIoPermission   perm;  
  str           exportFileName;  

  if(_projectNode != null)  
  {  
   exportFileName = folderPath + folderName + "\\" + _projectNode.AOTname() + ".xpo";  
   perm = new FileIoPermission(exportFileName, "W");  
   perm.assert();  
   _projectNode.treeNodeExport(exportFileName);  
   CodeAccessPermission::revertAssert();  
  }  
 }  

 folderName = dateTime.ToString("yyyyMMdd_HHmmss");  

 if(sharedProject)  
 {  
  treeIterator = sharedProject.AOTiterator();  

  if(treeIterator)  
  {  
   if(!WinAPI::pathExists(folderPath + folderName))  
   {  
    info(strFmt('Creating the folder %1', folderPath + folderName));  
    WinAPI::createDirectoryPath(folderPath + folderName);  
   }  

   childNode = treeIterator.next();  
   while (childNode)  
   {  
    info(strFmt('Exporting %1', childNode.AOTname()));  
    exportProject(childNode);  

    childNode = treeIterator.next();  
   }  
  }  

  sharedProject.treeNodeRelease();  
 }  
}  

veya

static void WIK_ExportLayer(Args _args) {     #define.ModelToExport1("ISV Model")     #define.ModelToExport2("USR Model")     #define.ExportDestination(@"C:\Temp\")     #AOTExport     boolean     currentLayerOnly = true;       void exportModel(ModelName _modelName)     {         ProjectNode             project;         SysModelCreateProject   createProject   = SysModelCreateProject::construct();         ProjectName             projectName     = strReplace(_modelName, ' ', '_') + '_All';         SysModel                model;         SysModelManifest        modelManifest;         SysModelElement         modelElement;         SysModelElementData     modelElementData;         SysLabelFileExport      label           = SysLabelFileExport::construct();           select firstOnly Model, RecId from modelManifest             where modelManifest.Name == _modelName             join Layer from model                 where modelManifest.Model   == model.RecId                     && model.Layer          == currentAOLayer();           createProject.parmModelId(modelManifest.RecId);         createProject.parmProjectName(projectName);           if (createProject.parmModelId())         {             // Export model metadata and source             createProject.run();             project = SysTreeNode::getPrivateProject().AOTfindChild(projectName);             project.treeNodeExport(#ExportDestination + projectName + '.xpo', currentLayerOnly ? #export | #expLayer : #export);               // Export label files and languages             while select modelElement                 where modelElement.ElementType  == UtilElementType::LabelFileLanguage                 exists join modelElementData                     where modelElementData.ModelId  == modelManifest.Model                         && modelElement.RecId       == modelElementData.ModelElement             {                 label.parmTreenode(TreeNode::findNode(SysTreeNode::modelElement2Path(modelElement)));                 label.parmPath(#ExportDestination);                 label.run();             }               // Export project definitions             while select modelElement                 where modelElement.ElementType  == UtilElementType::SharedProject                 exists join modelElementData                     where modelElementData.ModelId  == modelManifest.Model                         && modelElement.RecId       == modelElementData.ModelElement             {                 project = TreeNode::findNode(SysTreeNode::modelElement2Path(modelElement));                 if (!WinAPI::folderExists(#ExportDestination + 'Projects'))                 {                     WinAPI::createDirectory(#ExportDestination + 'Projects');                 }                 project.treeNodeExport(#ExportDestination + @'Projects\' + enum2str(currentAOLayer()) + modelElement.Name + '.xpo', currentLayerOnly ? #export | #expProjectOnly | #expLayer : #export | #expProjectOnly);             }         }         else         {             error(strFmt('Layer %1 does not have model %2', strUpr(enum2str(currentAOLayer())), _modelName));         }     }       exportModel(#ModelToExport1);     exportModel(#ModelToExport2); }

  

query filtre örneği, lookup filter örneği

filteredDs.addRange(fieldNum(FilteredTable, Field1)).value(strFmt(
    "(%1.%2 like %3.%4)",
    filteredDs.name(),
    fieldStr(FilteredTable, Field1),
    definitionDs.name(),
    fieldStr(FilterDefiniton, Field1)));

lookup filter

forma eklenen bir alan ve bu alan lookup ise filtre verme

Query                   query = new Query();

    QueryBuildDataSource    qbds;

    QueryBuildRange qr;

    SysMultiTableLookup     sysTableLookup;

    qr = query.addDataSource(tableNum(CustTable)).addRange(FieldNum(CustTable,RecId));

    //qr.value(strFmt('(!(AccountNum LIKE "%1"))',SysQuery::valueLikeAfter('M')));

   // qr.value(strFmt('(!(AccountNum LIKE "%1"))',"M*"));

    qr.value(strFmt('(!(AccountNum LIKE "M*"))'));

//status(2); bu recid değilde accountnum da gösterilecekse enable yapar

    sysTableLookup = SysMultiTableLookup::newParameters(this, query);

    sysTableLookup.addLookupField(fieldNum(CustTable, AccountNum));

    sysTableLookup.addLookupField(fieldNum(CustTable, Party));

    sysTableLookup.performFormLookup();

örnek

 Query q;

    Queryrun qr;

    QueryBuildRange qbr;

    QueryBuildDataSource qbds;

    InventTrans iv;

    Real Total;

    str range;

    /* The following query produces the same results as:

    while select sum(qty) from inventTrans

        where (inventtrans.ItemId == "1016756") || inventtrans.ItemId == "1032958"

            join inventDim

                group by inventBatchId

                where inventDim.InventDimId == inventTrans.InventDimId */

    q = new query("Inventory_Transactions");

    qbds = q.addDataSource(tablenum(InventTrans));

    qbds.addSelectionField(fieldnum(InventTrans,Qty),selectionfield::Sum);

    qbr = qbds.addRange(fieldnum(InventTrans,ItemId));

    qbr.value(strfmt('((%1 == "%2") || (%1 == "%3"))',fieldstr(inventtrans,ItemId),'1016756','1032958'));

    range = strfmt('((ItemId == "%1")||(ItemID =="%2"))',queryvalue('1016756'),queryvalue('1032958'));

    qbr.value(range);

    qbds = qbds.addDataSource(tablenum(InventDim));

    qbds.relations(true);

    qbds.orderMode(ordermode::GroupBy);

    qbds.addSortField(fieldnum(InventDim,InventBatchId));

    qr = new QueryRun(q);  // If the user clicks OK, continue.

    if (qr.prompt())

    {

        while (qr.next())

        {

           iv = qr.get(tablenum(InventTrans));

           total =  iv.Qty;

        }

    }

    info(strfmt("Quantity: %1",total));

     info (qr.query().dataSourceNo(1).toString());