AX SSRS : IF Condition with enter ( TRUE )

=First(Fields!VendAddress.Value, "InvoiceHeadPOTmpDS")
+IIF(IsNothing(First(Fields!VendPhone.Value, "InvoiceHeadPOTmpDS"))  OR First(Fields!VendPhone.Value, "InvoiceHeadPOTmpDS") = ""
 ,"", VBCRLF+First(Fields!VendPhone.Value, "InvoiceHeadPOTmpDS") )
 +IIF(IsNothing(First(Fields!VendFax.Value, "InvoiceHeadPOTmpDS"))  OR First(Fields!VendFax.Value, "InvoiceHeadPOTmpDS") = ""
 ,"",VBCRLF+First(Fields!VendFax.Value, "InvoiceHeadPOTmpDS") )

AX SSRS : Menggabungkan Field dan Menambahkan Enter di SSRS Report

contoh :

= First(Fields!Name.Value, "DataSet1") & VBCRLF &
First(Fields!Address1.Value, "DataSet1") & VBCRLF &
IIF(First(Fields!Address2.Value, "DataSet1") Is Nothing, "", First(Fields!Address2.Value, "DataSet1")  & VBCRLF) &
IIF(First(Fields!Address3.Value, "DataSet1") Is Nothing, "", First(Fields!Address3.Value, "DataSet1")  & VBCRLF) &
First(Fields!ZipCode.Value, "DataSet1")

Fungsi & adalah untuk menggabungkan field

VBCRLF fungsinya sebagai petunjuk enter 

Query : Query Update With Concat Substr

Contoh :
update karyawan set joindate=CONCAT(SUBSTR(SSN,1,4),"-",SUBSTR(SSN,5,2),"-", SUBSTR(SSN,7,2))

Query : Contoh Query Concat With Substr

Contoh :

SELEcT name,ssn, CONCAT(SUBSTR(SSN,1,4),"-",SUBSTR(SSN,5,2),"-", SUBSTR(SSN,7,2)) from karyawan

Excel : If di Excel (http://rumuslengkap.com)

Rumus IF merupakan salah satu fungsi paling umum yang digunakan dalam Microsoft Excel. Fungsi IF atau yang juga bisa diartikan “jika”, digunakan untuk mengecek suatu kondisi. Apabila kondisi terpenuhi maka fungsi akan mengambil suatu nilai, dan sebaliknya apabila kondisi tersebut tidak terpenuhi maka akan diambil nilai yang lain. dengan kata lain, fungsi IF disebut fungsi syarat. Jika suatu nilai mempunyai syarat dan dapat bernilai benar apabila syarat terpenuhi serta bernilai salah jika syarat tidak terpenuhi.

Excel : Formula If dengan Warna (copas http://kangmansyur.blogspot.co.id)

Bagaimana membuat sebuah grade atas nilai suatu mahasiswa tertentu, dan bagaimana memberikan pewarnaan di exel atas berbagai grade nilai mahasiswa tersebut. Untuk contohnya mari kita coba di excel sebagai berikut

Formula 1, untuk mengeset formula nilai akhir

AX ; Query Join AX

 qbdsCIT = query.addDataSource(tableNum(CustInvoiceTrans));
    qbdsAgreementLineReleasedLine = qbdsCIT.addDataSource(tableNum(AgreementLineReleasedLineUnion));
    qbdsAgreementLineReleasedLine.joinMode(JoinMode::ExistsJoin);
    qbdsAgreementLineReleasedLine.addLink(fieldNum(CustInvoiceTrans, RecId),
                                          fieldNum(AgreementLineReleasedLineUnion,CustInvoiceTrans));


    qbdsAgreementLine = qbdsAgreementLineReleasedLine.addDataSource(tableNum(AgreementLine));
    qbdsAgreementLine.joinMode(JoinMode::ExistsJoin);
    qbdsAgreementLine.addLink(fieldNum(AgreementLineReleasedLineUnion, AgreementLine),
                                      fieldNum(AgreementLine, RecId));

    qbdsAgreementHeader = qbdsAgreementLine.addDataSource(tablenum (SalesAgreementHeader));
    qbdsAgreementHeader.joinMode(JoinMode::ExistsJoin);
    qbdsAgreementHeader.addLink(fieldNum(AgreementLine, Agreement),
                                      fieldNum(SalesAgreementHeader, RecId));
    qbdsAgreementHeader.addRange(fieldNum(SalesAgreementHeader, Salesnumbersequence)).value(nomorsacari);


    qr = new QueryRun(query);

   while (qr.next())
    {
        CT = qr.Get(tableNum(CustInvoiceTrans));
        //ID = qr.Get(tableNum(InventDim));
      InvoiceAndFreeTaxTmp.clear();
      InvoiceAndFreeTaxTmp.InvoiceId = CT.InvoiceId;
        InvoiceAndFreeTaxTmp.tanggal = CT.InvoiceDate;
        InvoiceAndFreeTaxTmp.nmrSO = CT.OrigSalesId;
        InvoiceAndFreeTaxTmp.licenseplate=CT.LicensePlate();
        InvoiceAndFreeTaxTmp.ka=CT.KadarAir();
        InvoiceAndFreeTaxTmp.CIT_SDL=CT.SourceDocumentLine;
        //InvoiceAndFreeTaxTemp.licenseplate=CustInvoicePackingSlip::PackAndLicense(0, CT.RecId);
        InvoiceAndFreeTaxTmp.KG=CT.Qty;
        //InvoiceAndFreeTaxTmp.ka=CustInvoicePackingSlip::KadarAirs(0, CT.RecId);
        InvoiceAndFreeTaxTmp.refraksipercent=CT.markupAmountPercent();
        InvoiceAndFreeTaxTmp.refraksipcs=CT.markupAmountPcs();
        InvoiceAndFreeTaxTmp.jumlah=CT.LineInvoiceAmount();
        InvoiceAndFreeTaxTmp.harga=CT.PriceUnit;
        cittvalue=CT.SourceDocumentLine;

        //info(int642str(cittvalue));

        InvoiceAndFreeTaxTmp.komoditi=CT.Name;
        if(CT.B2BSupplier()!="")
        {
         InvoiceAndFreeTaxTmp.supplier=CT.B2BSupplier();
        }
        else
        {
         InvoiceAndFreeTaxTmp.supplier=CT.inventLocationId();
        }
        if(CT.OrigSalesId=="")
        {
            InvoiceAndFreeTaxTmp.FreeTax=1;
            InvoiceAndFreeTaxTmp.Jenis='FREETEXT';
        }

        InvoiceAndFreeTaxTmp.insert();
////////////////////////////////////////////////////////////////
///

    }